mySQL json array reformatting-CodePudding

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.

CodePudding user response：

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.

CodePudding user response：

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