Question 22092 in Mysql Question posted in
The official MySQL documentation can be found here.

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?

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    Try using

    SELECT JSON_ARRAY_AGG(JSON_OBJECT(data)) from A;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search