MYSQL JSON_SET adds backslash

Question

I want to update JSON array in the database

I'v got this array:

Array
(
    [id] => 34
    [var1] => val1
    [var2] => val2
    [var3] => val3
)

and I need to update json by the id

query sample:

JOSN_SET(`json_arr`,'$."34"','{"id":"34","var1":"val1","var2":"val2","var3":"val3"}')

expected result :

{"34":{"id":"34","var1":"val1","var2":"val2","var3":"val3"}}

result :

{"34":"{\"id\":\"34\",\"var1\":\"val1\",\"var2\":\"val2\",\"var3\":\"val3\"}"}

Thanks for the help.

Are you using the ->> operator or json_unquote(json_extract()) to get the result? — Dave Stokes, Feb 10 '19 at 12:23

score 7 · Answer 1 · answered Jul 12 '19 at 04:34

7

you should CAST it into JSON, like:

JOSN_SET(`json_arr`,'$."34"',CAST('{"id":"34","var1":"val1","var2":"val2","var3":"val3"}' AS JSON))

answered Jul 12 '19 at 04:34

zh2096

1 Answers1