I'm looking for a way to update or better in my case concatenate each value into a JSON array. All the value are string. I know that in simpler case I could do, to replace, something like:
SELECT JSON_REPLACE('[1, 2, 3]', '$[0]', 9) AS 'Result';
that would replace the first field with 9; but there's a way to concatenate each value with a fixed string? I know that this is not correct but something like:
SELECT JSON_REPLACE('[1, 2, 3]', '$[*]', concat($[*], 'fixed')) AS 'Result';
to get
'["1fixed", "2fixed", "3fixed"]
Thank you!
CodePudding user response:
mysql> select json_arrayagg(concat(val, 'fixed')) as result
from json_table('[1, 2, 3]', '$[*]' columns (val int path '$')) as j;
--------------------------------
| result |
--------------------------------
| ["1fixed", "2fixed", "3fixed"] |
--------------------------------
MySQL 8.0 is required for the JSON_TABLE() function. MySQL 5.7 or later is required for the JSON_ARRAYAGG() function.
If this seems complicated, sorry, but it's a consequence of storing data as a JSON string, and then trying to use SQL expressions on the values within the string. It's bound to be awkward, because you're implementing an antipattern called the Inner-Platform Effect.
This would be far easier if you did not store data as a JSON array, but stored data in a normal form, with one value per row.
CodePudding user response:
You can use the following query which includes some JSON functions while extracting the elements of the array through using a kind of row generating technique such as
SELECT JSON_ARRAYAGG(
JSON_EXTRACT(
JSON_REPLACE(json,
j,
CONCAT(JSON_EXTRACT(json,j),'fixed')
),
j
)
) AS Result
FROM
(SELECT @i := @i 1 AS i, json, CONCAT('$[',@i-1,']') AS j
FROM t
JOIN (SELECT @i := 0 FROM t) AS k
JOIN information_schema.tables ) AS jj
WHERE i <= JSON_LENGTH(json)
provided the version of the DB is at least 5.7