mySQL json array reformatting

Question

I have a table which contains a column "owners", which has json data in it like this:

[
   {
      "first":"bob",
      "last":"boblast"
   },
   {
      "first":"mary",
      "last": "marylast"
   }
]

I would like to write a query that would return for each row that contains data like this, a column that has all of the first names concatenated with a comma.

i.e.

id                owners
----------------------------
1                 bob,mary
2                 frank,tom

Not on mysql 8.0 yet.

score 0 · Answer 1 · answered Nov 03 '21 at 21:28

0

You can get the values as a JSON array:

SELECT JSON_EXTRACT(owners, '$[*].first') AS owners ...

But that returns in JSON array format:

+-----------------+
| owners          |
+-----------------+
| ["bob", "mary"] |
+-----------------+

JSON_UNQUOTE() won't take the brackets and double-quotes out of that. You'd have to use REPLACE() as I show in a recent answer here:

MYSQL JSON search returns results in square brackets

You should think about not storing data in JSON format if it doesn't support the way you need to query them.

answered Nov 03 '21 at 21:28

Bill Karwin

538,548
86
673
828

We dont necessarily need to query the data. We considered adding additional tables, but when we switch to mysql 8, all these issues go away with JSON_TABLE, which will allow us to use GROUP_CONCAT. – bpeikes Nov 04 '21 at 03:17
Is JSON_UNQUOTE only MariaDB? – bpeikes Nov 05 '21 at 19:23
@bpeikes I will leave it to you to look it up in the JSON function reference in MySQL. – Bill Karwin Nov 05 '21 at 19:24
Odd, when I first searched for JSON_UNQUOTE, I only saw refrences to MariaDB, I see it now in the mysql docs. – bpeikes Nov 05 '21 at 21:13

score 0 · Answer 2 · answered Nov 03 '21 at 23:00

Here is another option, get a helper table with running numbers up to the max json array length, and extract values by individual index, after that group_concat the values, something like this:

SELECT g.id, GROUP_CONCAT(g.name)
FROM (
    SELECT a.id, JSON_UNQUOTE(JSON_EXTRACT(a.owners, CONCAT('$[', n.idx, '].first'))) name
    FROM running_numbers n
    JOIN mytable a
) g
GROUP BY g.id

https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=d7453c9edf89f79ca4ab2f63578b320c

mySQL json array reformatting

2 Answers2