CODEWITHSUNDEEP
mysqljson
Alireza Bashiri
Alireza Bashiri

Reputation: 11

Wrap/Convert json object into array of objects MySQL

I have a column named data and I have to update its content from something like {} to [{}] for each record in table A, I tried to use JSON_ARRAY() but it gives me a quoted

["{\"something\": \"true\"}"]

but I'd like to have something like

[{ "something": "true" }]

How I do it now?

SELECT JSON_ARRAY(data) FROM A;

How should I update it either using JSON_SET() or UPDATE?

Upvotes: 1

Views: 532

Answers (2)

Barmar
Barmar

Reputation: 782693

You need to use a path to get the data as JSON, rather than referring to the column by itself. The path $ means the top-level object.

update A 
SET data = CASE
    WHEN data IS NULL THEN '[]' -- NULL becomes empty array
    WHEN LEFT(data, 1) = '[' THEN data -- leave existing array alone
    ELSE JSON_ARRAY(data->"$") -- put object inside array
END

DEMO

Upvotes: 1

EWW
EWW

Reputation: 62

Try using

SELECT JSON_ARRAY_AGG(JSON_OBJECT(data)) from A;

Upvotes: 2

Related Questions