how to insert json into a json array in mysql?

Question

I've been trying to append a JSON object to an already-populated array in a JSON column. I tried to use the JSON_ARRAY_INSERT() function but it didn't work as expected.

I have the following table:

id (`BIGINT`)	json_data (`JSON`)
`3`	`[{"a":"0","b":"1"}]`

And I want to append another object {"c":"2","d":"3"} to the existing array. Here is what I tried:

$conn = new PDO("mysql:host=localhost;dbname=exampledb", "root", "");
$query="UPDATE example SET json_data = JSON_ARRAY_INSERT(json_data, '$[1]', :new_json_data) WHERE id = 3";
$result = $conn->prepare($query);
$result->execute([
    "new_json_data" => json_encode(["c" => "2", "d" => "3"])
]);

I want the json_data column value to be

[{"a":"0","b":"1"},{"c":"2","d":"3"}]

But the result is

[{"a":"0","b":"1"}, "{\"c\":\"2\",\"d\":\"3\"}"]

By default, PHP uses PDO::PARAM_STR for variables to bind, so that could be the answer. Make sure your query is working as expected when you execute it right on MySQL to exclude that query is not working after that try removing json_encode which return string as result. — Oleg Andreyev
– Oleg Andreyev, Commented May 5, 2021 at 22:26
I've attempted to make your code and question more readable. Using consistent indentation and whitespace in your code is common courtesy for other users here, and will also help you when you're writing and debugging your own code. I removed a leading backslashed quote from your JSON output which looked like a mistake; it was not matched up with any other backslashed quote mark. — miken32
– miken32, Commented May 6, 2021 at 0:00
Also as a side note, JSON_ARRAY_INSERT() puts the inserted object at a specific place in the array. If you just want it at the very end, you are probably looking for JSON_ARRAY_APPEND(json_data, '$', CAST(:new_json_data AS JSON)) — miken32
– miken32, Commented May 6, 2021 at 0:06
Ok, fixed that. Using preformatted code blocks will make things easier for you in the future. — miken32
– miken32, Commented May 6, 2021 at 0:08
i see thanks for the side note and for the editting too , its my first time asking a question so my editting sucks — prewky
– prewky, Commented May 6, 2021 at 0:09

Bill Karwin · Accepted Answer · 2021-05-05 22:29:13Z

2

Use this:

$query="update example set json_data=JSON_ARRAY_INSERT(json_data,'$[1]',
    CAST(:new_json_data AS JSON)) where id=3";

This will force the string input to be converted to a JSON document before appending it to the array.

answered May 5, 2021 at 22:29

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

prewky Over a year ago

that gave me the same result

prewky Over a year ago

well guess what , that works , i had two identical blocs of code and i was only edditing the one that i wasnt executing thank you very much for the answer Bill

Collectives™ on Stack Overflow

how to insert json into a json array in mysql?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related