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?

CodePudding user response：

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

CodePudding user response：

Try using

SELECT JSON_ARRAY_AGG(JSON_OBJECT(data)) from A;