MySQL merge arrays from multiple JSON rows

Question

I have a table with a json column that looks like this :

+----+------------+
| id | myfield    |
+----+------------+
|  1 | ["1", "2"] |
|  2 | ["3", "2"] |
|  3 | ["2", "4"] |
+----+------------+

How can I merge all values from myfield in one array?

I need a result that will look like this: ["1", "2", "3", "2", "2", "4"], or even better with removed duplicates.

I tried using this query: SELECT JSON_ARRAYAGG(myfield) FROM json_test but as a result I'm getting:

[["1", "2"], ["3", "2"], ["2", "4"]]

I assume I need a query in combination with the function JSON_MERGE.

Answer 1

Here's a solution but it requires MySQL 8.0 for the JSON_TABLE() function:

SELECT GROUP_CONCAT(j.myvalue) AS flattened_values
FROM mytable, JSON_TABLE(
  mytable.myfield, '$[*]' COLUMNS(myvalue INT PATH '$')
) AS j;

Output:

+------------------+
| flattened_values |
+------------------+
| 1,2,3,2,2,4      |
+------------------+

I would actually recommend avoiding storing JSON arrays. Instead, store multi-valued data in a normalized manner, in a second table. Then you could just use GROUP_CONCAT() on the joined table.

I have still yet to hear of a use of JSON in MySQL that isn't better accomplished by using database normalization.