join json array as a string in MySQL

Question

I have these rows in mysql table.
tags is a json type column and json array is stored.

id  tags
1   ["a", "b"]
2   ["a", "b", "c"]
3   []

I want to return join value of the array elements.
i.e.)

id  tags
1   "a, b"
2   "a, b, c"
3   ""

Is there any mysql function to do this?

※ this is just an example table, so why tags is json array doesn't matter, sorry.

"why `tags` is a json array doesn't matter", but it definitely does. If you state "I put my hands in my shoes, why is walking so hard?", the response is "Why wouldn't you just use your feet?". In other words, a normalized DB schema would store these values as multiple records, rather than using one record to store multiple values. Then it's a matter of simply using `GROUP_CONCAT` and `GROUP_BY` — Rogue, Jul 27 '22 at 15:22
Thank you for your comment! Yes, normalization is needed in this case...but it just means that my example is bad. I would like to know how to do that theoretically when these kind of no good values are stored in database. — user16012143, Jul 27 '22 at 16:55
To clarify, you're wondering how you would normalize the data you've shown in your example? It involves a schema change if so. There are methods which would work for this using the json input, but imo they're something which are pretty non-standard and tend to have "gotchas" (e.g. older rdbms) as opposed to standard sql. I did, however, miss you mentioning it was a `JSON` column type, meaning you're on mysql 5.7.8+ — Rogue, Jul 27 '22 at 16:57
Thanks! Normalization is not a scope of my question here. I see your point, this kind of way to store data is not a standard way. but I have to work with this schema without any change... That's why I would like to how to handle json array in this way. Sorry for missing my MySQL version, it's 8.0.23. — user16012143, Jul 27 '22 at 17:05

score 0 · Answer 1 · answered Jul 27 '22 at 16:55

This requires the JSON_TABLE() function which is supported in MySQL 8.0:

mysql> select id, j.tag from mytable 
  left join json_table(tags, '$[*]' columns( tag varchar(10) path '$' )) j 
  on true; 
+------+------+
| id   | tag  |
+------+------+
|    1 | a    |
|    1 | b    |
|    2 | a    |
|    2 | b    |
|    2 | c    |
|    3 | NULL |
+------+------+

mysql> select id, group_concat(j.tag) as tags from mytable 
  left join json_table(tags, '$[*]' columns( tag varchar(10)
path '$' )) j 
  on true 
  group by id;
+------+-------+
| id   | tags  |
+------+-------+
|    1 | a,b   |
|    2 | a,b,c |
|    3 | NULL  |
+------+-------+

score 0 · Answer 2 · answered Mar 25 '23 at 17:23

You can do it this way:

SELECT
  `id`,
  /* tags_joined */ (
    SELECT
      COALESCE(
        GROUP_CONCAT(`jsontable`.`item` SEPARATOR ', '), -- join array items
        '' -- fallback value for nonexistent or empty array
      )
    FROM
      `my_table` AS `jsonsource`, -- alias for WHERE
      -- implicit lateral join
      JSON_TABLE(
        `jsonsource`.`tags`,
        '$[*]' -- path to array items
        COLUMNS(`item` VARCHAR(10) PATH '$') -- array item to table column
      ) AS `jsontable`
    WHERE `jsonsource`.`id` = `my_table`.`id` -- current row only
  ) AS `tags_joined`
FROM `my_table`

join json array as a string in MySQL

2 Answers2