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

Sandra Emily

Can you answer this question?

1 Answers

Liked By