5

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.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
user345602
  • 578
  • 1
  • 12
  • 26

1 Answers1

6

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.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • I expected a simpler solution since MySQL officially supports JSON operations, but anyway, thanks for the answer. – user345602 Feb 22 '21 at 17:51
  • 1
    This is MySQL's solution for JSON operations. All use of JSON is done through a variety of [JSON functions](https://dev.mysql.com/doc/refman/8.0/en/json-function-reference.html), which are still evolving. This makes an old-school normalized database seem pretty good doesn't it? – Bill Karwin Feb 22 '21 at 17:54