Framework: mysql json field cannot update nested array item through nested array's index

[eleven26]

• Laravel Version: 5.7.12
• PHP Version: 7.1.14
• Database Driver & Version: MySQL 5.7.17

Description:

Laravel framework cannot update array item through array index like: '$.test[0].name', the sql generate by \Illuminate\Database\Query\Grammars\Grammar::wrapJsonPath seem like this '$."test[0]".name', but with double quotation mark won't update MySQL json field, at the same time, the update statement return the wrong result without updating MySQL's record, database record not updated but get the true result.

source code

    /**
     * Wrap the given JSON path.
     *
     * @param  string  $value
     * @return string
     */
    protected function wrapJsonPath($value)
    {
        return '\'$."'.str_replace('->', '"."', $value).'"\'';
    }

Steps To Reproduce:

• MySQL test
  
  
  
  • set @json = '{"test": [{"name": "laravel"}, {"name": "symfony"}]}';
  
  
  • run select json_set(@json, '$.test[0].name', "lumen");, no double quotation mark
  
  
  • result: {"test": [{"name": "lumen"}, {"name": "symfony"}]}, update successfully
  
  
  • run select json_set(@json, '$."test[0]".name', "lumen");, with double quotation mark
  
  
  • result: {"test": [{"name": "laravel"}, {"name": "symfony"}]}, update failed
  
  
• Laravel test
  
  
  
  • The sql generate by MySQL grammer:
    
    ~~
    
    update xx set a = json_set(a, '$."test[0]"."name"', lumen)
    
    ~~
  
  
  • This sql has double quotes wrap up test[0], but it will not work for array in MySQL json field. Unless remove the double quotes.
  
  

Related commit #22118

[staudenmeir]

I don't think we can support this because of the ambiguity:

->update(['column->foo[0]' => 'bar']);

We don't know whether the user wants to update the first array item or they have an object with a key that is actually named foo[0].

The first scenario is probably more common, but it's more complicated to implement and would be a breaking change.

[eleven26]

I don't think we can support this because of the ambiguity:

->update(['column->foo[0]' => 'bar']);

We don't know whether the user wants to update the first array item or they have an object with a key that is actually named foo[0].

The first scenario is probably more likely, but it's more complicated to implement and would be a breaking change.

Yeah, that's right. I have neglected the scenario of object key named foo[0]. But I think that the scenario of updating array item maybe more common. It't would be very nice if user has another choice to update json without double quotes, we can replace off DB::raw and save us the trouble of string catenation with this choice.

[driesvints]

I'll flag this as a bug for now but I also don't really see how we could solve this soon. Feel free to send in a PR if someone can come up with a non-BC solution.

[nulele]

Hi,

I found a similar problem in the 5.8 version.

Consider this JSON string contained in the data field of the heroes table:

[
   {
      "name":"superman",
      "type":"dc",
      "alignment":"good",
   },
   {
      "name":"batman",
      "type":"dc",
      "alignment":"good",
   },
   {
      "name":"joker",
      "type":"dc",
      "alignment":"evil",
   }
]

Actually this query cannot be done through Query Builder:

SELECT * FROM `heroes` WHERE json_unquote(json_extract(`heroes`.`data`, '$[*]."alignment"')) = 'good';

Because the wrapJsonPath function prints out this (notice quotes around [*] and dot before):

SELECT * FROM `heroes` WHERE json_unquote(json_extract(`heroes`.`data`, '$."[*]"."alignment"')) = 'good';

I think [*] should be bypassed and not quoted.

[staudenmeir]

This would also be a breaking change. An object key could actually be named [*].

[staudenmeir]

Actually this query cannot be done through Query Builder:

SELECT * FROM heroes WHERE json_unquote(json_extract(heroes.data, '$[*]."alignment"')) = 'good';

This doesn't work for me as a raw query either.

You can use whereJsonContains() instead:

Hero::whereJsonContains('data', ['alignment' => 'good'])->get();

[iamgergo]

Hi,

I bumped into similar issues and I had the following experiences (on MariaDB 10.4.11):

I have a JSON array of objects, named files:

{
    "files": [
        {"name": "file_1", "url": "..."},
        {"name": "file_2", "url": "..."},
    ]
}

I called the following update on the model, and it was working as expected:

$model->update(['files->0->name' => 'other_name']);

I called an update with an index that was not present, the update converted the array to an object and then inserted the value with the new index – which is logical I think.

$model->update(['files->5->name' => 'new file']);

{
    "files": {
        "0": {"name": "file_1", "url": "..."},
        "1": {"name": "file_2", "url": "..."},
        "5": {"name": "new file", "url": "..."},
    }
}

If the array was empty and the key was 0 (files->0->name), the update kept it as an array and inserted the value to 0 index. If the key was other than 0, then it was working like the non-existing indexes/keys.

---

So, it turns out, the working syntax is not column->field[0] but column->field->0. Also, with this approach there is no ambiguity issue, because we could even use column->field[0]->0. Here it's clear, that we access the subsets of the JSON by using the -> operator and not the field[0] syntax.

Also, this syntax is the same as the one in #31448.

In my opinion, this works as it should. Or do I miss anything here?

[staudenmeir]

@iamgergo Your code works because you are updating a single model.

@eleven26 is referring to queries like Model::where(...)->update(...).

When you update a nested JSON value on a single model, Eloquent replaces the value _in PHP_ and the UPDATE query replaces the whole column value.

[taylorotwell]

Do we really have any idea what direction we want to go on this? I agree with other commenters that it is likely updating a numerically indexed array value is the vastly more common use case than a string key containing "[0]", etc.

However, either way, we are going to break some applications it seems unless someone has an idea that has not been discussed so far.

However, If we did change this to interpret the array offset pattern as being intended to represent the numerical offset of an array, we would introduce an inconsistency with way you can update single models using the update method (which calls fill which presumably calls Arr::set). So, we would have to consider if we want to reconcile that new inconsistency. That would introduce a second breaking change.

Based on what has been presented so far, if we were going to take any action at all, I would lean towards making the change to interpret [N] as an array offset instead of part of the object's key, but I have no idea how hard it would be.

[nikosv]

Searching for an issue i am facing in Laravel 6 with jsonb columns and eloquent where clauses in Postgres i found this issue and while my issue is not related to updates i think it might be relevant (if i need to open a new issue please let me know).

The problem is related to where clauses in numeric indexes.
And more specifically
->where('data->information->145->value', 'whatever');

Generates:
WHERE "data"->'information'->145->>'value' = 'whatever'

Intead of:
WHERE "data"->'information'->'145'->>'value' = 'whatever'

A workaround would be to whereJsonContains like so:
->whereJsonContains('data->information', ['145' => ['value' => 'whatever']])

[ollieread]

Could you not make use of a custom Illuminate\Database\Query\Expression class?

Examples:

$something->update([
    JsonPath::make('foo')->index(0)->key('name') => 'bar',
]);

Using ArrayAccess and magic methods you could even do something like:

$something->update([
    JsonPath::make()->foo[0]->name => 'bar',
]);

// Or

$something->update([
    JsonPath::make()->foo->all()->name => 'bar',
]);

It's not the nicest solution, though it does feel nicer than doing 'foo[0]->name', could help prevent mistakes in using a string.

[taylorotwell]

@ollieread that's a pretty good solution actually. Would you have time to PR it so we can take a look?

[ollieread]

@taylorotwell happily. Do you have a preference for which of the methods I suggested?

[taylorotwell]

For future reference, a PR was started here: https://github.com/laravel/framework/pull/34515

However, it still needs completion and support / testing for multiple database drivers if anyone wants to pick it up and solve this issue.