CODEWITHSUNDEEP
phpmysqljson
prewky
prewky

Reputation: 25

how to insert json into a json array in mysql?

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\"}"]

Upvotes: 1

Views: 2164

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562230

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.

Upvotes: 2

Related Questions