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!

@BarbarosÖzhan Sorry I forgot to do it when I published the question but it's done now. — Derek Wildstar
– Derek Wildstar, Commented Mar 9, 2022 at 8:43

Bill Karwin · Accepted Answer · 2022-03-08 17:22:00Z

2

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.

edited Mar 8, 2022 at 17:22

answered Mar 8, 2022 at 16:51

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

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

4 Comments

Derek Wildstar Over a year ago

Unfortunately I cannot apply this to my case due to version constraint but anyhow your point is very interesting. JSON storage was a requirement but I get your point. Thank you for sharing this!

Bill Karwin Over a year ago

Nearly every case I've seen of storing JSON in MySQL has led to tears.

hejdav Over a year ago

This is useful, however I still wonder how to join with actual table to read json from existing column 🤔

Bill Karwin Over a year ago

@hejdav, Yes, the documentation is pretty unclear on this. I filed a bug report with MySQL to improve the documentation, but they refused to change it. See the suggested example I posted here: bugs.mysql.com/bug.php?id=102089

Barbaros Özhan · Accepted Answer · 2022-03-08 18:42:35Z

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

Collectives™ on Stack Overflow

How to update all string into JSON array

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related