Framework: Postgresql 9.6 + UPDATE JSON value in jsonb field - Grammar creates invalid sql syntax

[pongraczi]

• Laravel Version: Laravel Framework 5.6.3
• PHP Version: 7.2
• Database Driver & Version: Postgresql 9.6

Brief description:

Updating json field in a table produce invalid sql statement.
The generated invalid sql formula is:
update "preferences" set "value"->'global'->>'lang' = hu, "updated_at" = 2018-02-22 21:54:50 where "preferences"."user_id" = 1 and "preferences"."user_id" is not null

The working formula would be, after I changed the field type from json to jsonb:
update "preferences" set "value" = jsonb_set("value", '{"global", "lang"}', '"hu"'::jsonb) , "updated_at" = '2018-02-22 21:54:50' where "preferences"."user_id" = 1 and "preferences"."user_id" is not null
Maybe json->jsonb is not necessary, I just found the jsonb_set function and that is why I changed it.

Long description:

I installed the latest laravel-enso framework into Postgresql 9.6 and I found that, when I try to change the Language in the user's preference, it did not happen, but a syntax error appeared instead.
After I dig into the code, I found that, the generated sql syntax is not working when it try to update the field, specifically one value (global->lang) in the json from gb to hu.
As I checked the syntax, it seems it should work only when one want to get a value from the json using select and not for updating custom value of a key in the json.

First I reported this issue to the laravel-enso repository, but it turned out, the related code is in the laravel framework.

Steps To Reproduce:

It is easy,

• just install laravel-enso with Postgresql 9.6
• login as default user
• and try to change the language preferences at the upper-right corner
• check the debug log, you will see this:
  

[pongraczi]

Ok, I try to explain the situation in other way.

Click to open PostgreSQL JSON Reference

Operators for GET a JSON value (cannot be used to set a value!):

-> ->>  #> #>>

INSERT or UPDATE JSON values:

• Table 9-44. JSON Creation Functions
• Table 9-45. JSON Processing Functions

So, due to that, it is not possible to use the same SQL syntax to GET and SET json values using the operators shown above (-> ->>).

Example

Schema

Schema::create('preferences', function (Blueprint $table) {
            $table->increments('id');
            $table->integer('user_id')->unsigned()->index()->unique();
            $table->foreign('user_id')->references('id')->on('users')
                ->onUpdate('cascade')->onDelete('cascade');
            $table->json('value');
            $table->timestamps();
        });

Value of the JSON field, called value

{
    "global": {
        "lang": "en",
        "collapsedSidebar": false,
        "fixedHeader": true,
        "dtStateSave": true,
        "theme": "clean"
    }
}

Use case: UPDATE global->lang to hu

Illuminate/Database/Query/Grammars/PostgresGrammar.php will produce the following syntax which is not working (BAD syntax):

update "preferences" set "value"->'global'->>'lang' = hu, "updated_at" = 2018-02-22 21:54:50 where "preferences"."user_id" = 1;

Instead, in case of UPDATE, the following sql syntax should be compiled by the grammar (GOOD syntax):

update "preferences" set "value" = jsonb_set("value", '{"global", "lang"}', '"hu"'::jsonb) , "updated_at" = '2018-02-22 21:54:50' where "preferences"."user_id" = 1

Please note, jsonb_set function must be used to SET a new value, instead of the -> and ->> operators.

Please let me know if you need more information.

[fonini]

@pongraczi This worked for me.

[staudenmeir]

At the moment, updating JSON columns with the query builder is only implemented for MySQL.