Wrap/Convert json object into array of objects MySQL

Question

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?

EWW · Accepted Answer · 2022-11-24 06:39:21Z

2

Try using

SELECT JSON_ARRAY_AGG(JSON_OBJECT(data)) from A;

answered Nov 24, 2022 at 6:39

EWW

726 bronze badges

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

2 Comments

Barmar Over a year ago

This will combine all the rows into a single array.

Alireza Bashiri Over a year ago

Yes it does. This isn't what I was looking for but thanks.

Barmar · Accepted Answer · 2022-11-24 07:16:54Z

1

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

edited Nov 24, 2022 at 7:16

answered Nov 24, 2022 at 6:26

Barmar

789k57 gold badges554 silver badges669 bronze badges

9 Comments

Alireza Bashiri Over a year ago

Great, how can I apply it using a UPDATE? UPDATE A SET data=JSON_ARRAY(data->"$") WHERE data IS NOT NULL

Barmar Over a year ago

That should work. See db-fiddle.com/f/pKSS9vgSmsSLn5yiZFkiDc/1

Alireza Bashiri Over a year ago

Great answer. How can I add another condition to prevent running it twice? Like if data is alreay an array?

Barmar Over a year ago

You can use a string function like and LEFT(data, 1) != '['

Alireza Bashiri Over a year ago

I missed to ask if the column is null is it possible to create an empty array for that? I used UPDATE cpdc SET cpdcFormData = JSON_ARRAY() WHERE cpdcFormData IS NULL But didn't work.

|

Collectives™ on Stack Overflow

Wrap/Convert json object into array of objects MySQL

2 Answers 2

Try using

2 Comments

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Try using

2 Comments

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related