How to update all string into JSON array

Question

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!

Answer 1

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)

Demo

provided the version of the DB is at least 5.7