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

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

About this issue

Original URL
State: closed
Created 6 years ago
Comments: 21 (19 by maintainers)

Most upvoted comments

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.

ollieread on Aug 4, 2020

Whohoo, glad we got this one closed. Thanks @derekmd! 🥳

driesvints on Aug 19, 2021

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.

taylorotwell on Oct 22, 2020

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

taylorotwell on Sep 23, 2020

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’]])

nikosv on May 8, 2020

Yeah, I planned to eventually submit solution 2 since the code change is simple and it covers most JSON path queries. Since that must be submitted to 9.x (Jan 2022) I was waiting to see if CI test coverage for MS SQL + Postgres was going to be added so I can get JSON queries running for each driver in the test suite. Or I can submit it now and tick off this issue checkbox since I know that MS SQL dev branch is test suite refactor hell.

derekmd on Aug 15, 2021

@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.

staudenmeir on May 4, 2020