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.

What does SELECT @@version; return? The best answer could depend on which exact version of MySQL you are using. — Bill Karwin
– Bill Karwin, Commented Feb 15, 2021 at 21:41
@BillKarwin 5.7.22, but I'm open to upgrading to any needed version as I'm at the project's beginning. — user345602
– user345602, Commented Feb 15, 2021 at 22:15

Bill Karwin · Accepted Answer · 2021-02-15 22:28:26Z

8

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.

answered Feb 15, 2021 at 22:28

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user345602 Over a year ago

I expected a simpler solution since MySQL officially supports JSON operations, but anyway, thanks for the answer.

Bill Karwin Over a year ago

This is MySQL's solution for JSON operations. All use of JSON is done through a variety of JSON functions, which are still evolving. This makes an old-school normalized database seem pretty good doesn't it?

Collectives™ on Stack Overflow

MySQL merge arrays from multiple JSON rows

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related