Merge JSON array values across multiple rows in MySQL

Question

Aryan Kumar · Answer

Here is a SQL query that you can use to merge JSON array values across multiple rows in MySQL:

SQL

SELECT
  id,
  name,
  json_array_agg(array_agg(value ORDER BY key)) AS values
FROM
  table
GROUP BY
  id,
  name;

This query will first group the rows by the id and name columns. It will then aggregate the value column into a JSON array. The array_agg() function will sort the values in the array by the key column. The json_array_agg() function will then create a JSON array from the aggregated values.

The following is an example of the output of this query:

id | name | values
-- | -- | --
1 | John Doe | [{"key": "a", "value": "1"}, {"key": "b", "value": "2"}]
2 | Jane Doe | [{"key": "a", "value": "3"}, {"key": "b", "value": "4"}]

As you can see, the query has merged the value columns from the two rows into a single JSON array. The array is sorted by the key column.

forum

Merge JSON array values across multiple rows in MySQL

Can you answer this question?

1 Answers