Laravel 5.3 update JSON Mysql field - #3143 - Invalid JSON path Expression

Asked

Viewed 81 times

2

Query generated by Laravel:

update `informations` set `parameters` = json_set(`parameters`, "$.'Campo teste'.type", 'string'), `parameters` = json_set(`parameters`, "$.'Campo teste'.value", 'Novo valor') where `parameters`->'$."Identificação do campo"."type"' = 'string' and `parameters`->'$."Identificação do campo"."value"' = 'D25L'

Syntax:

DB::table("informations")->where($data['where'])->update($data['update']);

Mysql returns me the following error:

#3143 - Invalid JSON path expression. The error is around character position 7.

Array code $data:

{
   "update": {
      "parameters->'Campo teste'->type": "string",
      "parameters->'Campo teste'->value": "Novo valor"
   },
   "where": {
      "parameters->Identificação do campo->type": "string",
      "parameters->Identificação do campo->value": "D25L"
   }
}

Obs:

If I make a simple query using only the excerpt from where it finds the records, I believe the problem is in JSON_SET even

php mysql json laravel-5.3

  • This might help: https://mattstauffer.co/blog/new-json-column-where-and-update-syntax-in-laravel-5-3

  • put the array code $data

  • @13dev Postei ;)

  • @Miguel yes, I set up the array in the same structure as him, the only difference is that in my use of space instead of underline where elsewhere has behaved very well including in where

  • ta cause this error because the variable(s) parameter(s) "$.'Campo teste'.type" should be '$."Campo teste".type' single quotes at the end

  • @13dev yes, I thought the same thing, the inverted quote types, can it be considered a bug of them? The strange thing is that happens only in the update, Where works normally

  • possible, the best you have to do is use method DB::raw()

  • 1

    @13dev managed to correct by making a basic change in Laravel

Show 3 more comments

1 answer

3


I made a change to the Laravel file MySqlGrammar.php located in:

vendor\laravel\framework\src\Illuminate\Database\Query\Grammars

Row 155 Method compileJsonUpdateColumn

of: 

$accessor = '"$.'.implode('.', $path).'"';

for

$accessor = "'$.".implode('.', $path)."'";

And in my update array I added double quotes:

    "update": {
      "parameters->\"Campo teste\"->type": "string",
      "parameters->\"Campo teste\"->value": "Novo valor"
   }

Browser other questions tagged php mysql json laravel-5.3

You are not signed in. Login or sign up in order to post.