Replacing a JSON array in MySQL in PHP

I am trying to replace data in a JSON array inside a JSON array in my MySQL Database.

The array looks like this:

{"slug": "SLUG","price": "{"44":12,"_default":12}", "test": "TEST"}

Now I would like to update the two fields inside the price array.

I am using the following code to be able to replace the price array with a string or number, but I am not able to replace it with another array:

$sql = "UPDATE products SET productDynamicFields = JSON_REPLACE(productDynamicFields,'$.price', '$test')
        Where productSlug = '$productSlug'";
$result = mysqli_query($link,$sql);

As you can see I am using a variable called $test.

I tried giving the variable the following value: $test = ['44' => 13, '_default' => 13]; and then encoding it like this: $test = json_encode($test);

But now in my database it looks like this:

"price": "{"44":13,"_default":13}

I now tried using JSON_UNESCAPED_SLASHES with no success.

What can I do to achieve the array inside the array?

Answer

Rather than generating json from php and attempting to pass it to MySQL, you would better use MySQL function json_object() to generate a valid json object:

update t
set js = json_replace(js, '$.price', json_object('44', 13, '_default', 13))

Demo on DB Fiddlde:

create table t (js json);
insert into t values('{"slug": "SLUG","price": {"44":12,"_default":12}, "test": "TEST"}');

update t
set js = json_replace(js, '$.price', json_object('44', 13, '_default', 13));

select * from t

| js                                                                    |
| :-------------------------------------------------------------------- |
| {"slug": "SLUG", "test": "TEST", "price": {"44": 13, "_default": 13}} |

Note:

The array looks like this:

{"slug": "SLUG","price": "{"44":12,"_default":12}", "test": "TEST"}

• this is a json object (with an embedded json object under key "price"), not a json array

• you had additional double quotes around the embedded object that made the json invalid, I assumed that was a typo and removed them