How to concatenate multiple JSON arrays into a single array in MariaDB?

Question

I'm working with a MariaDB database where I have a table with two columns: id and values, where values contains JSON arrays. Here is a simplified example of the data:

id	values
1	"[1, 2, 3]"
1	"[5]"
2	"[4]"

I want to group the rows by id and concatenate the JSON arrays in the values column into a single array, like this:

id	values
1	"[1, 2, 3, 5]"
2	"[4]"

I tried using JSON_ARRAYAGG but it creates an array of arrays, and I can't figure out how to merge the arrays into one. How can I achieve this in MariaDB?

SELECT id, JSON_ARRAYAGG(values)
  FROM REC
GROUP BY id

Bonus question: How to output only unique values?

score 0 · Answer 1 · answered Dec 16 '24 at 17:12

With Mariadb 10.6 you could use following approach with json_table, all approaches would mimic the behaviour and would need a function.

still you should avoid column names that have reserved words like values see a list https://mariadb.com/kb/en/reserved-words/

CREATE Table Table1 (id int, `values` text)

INSERT INTO Table1 (id  ,`values`) VALUES
(1,     "[1, 2, 3]"),
(1,     "[5]"),
(2,     "[4]")

Records: 3  Duplicates: 0  Warnings: 0

select id, json_arrayagg(`values`) as `values`
from ( 
  select distinct id, j.value_val as `values`
  from Table1
  cross join json_table(`values`
  , '$[*]' columns (value_val int path '$')) as j
) as t
group by id;

id	values
1	[1,2,3,5]
2	[4]

fiddle

score 0 · Answer 2 · answered Dec 17 '24 at 08:35

The solution which is applicable to MariaDB 10.5:

WITH RECURSIVE
cte1 (id, json, rn) AS ( -- enumerate rows for each separate ID 
  SELECT id, json, ROW_NUMBER() OVER (PARTITION BY id)
  FROM test
  ),
cte2 (id, json, rn) AS ( -- concatenate arrays one-by-one
  SELECT id, json, rn
  FROM cte1
  WHERE rn = 1
  UNION ALL
  SELECT id, JSON_MERGE(cte2.json, cte1.json), cte1.rn
  FROM cte2
  JOIN cte1 USING (id)
  WHERE cte2.rn + 1 = cte1.rn
  ),
cte3 (id, json, rn) AS ( -- enumerate intermediate and final results
  SELECT id, json, ROW_NUMBER() OVER (PARTITION BY id ORDER BY rn DESC)
  FROM cte2
  )
-- select only final result
SELECT id, json
FROM cte3
WHERE rn = 1;

fiddle

PS. The duplicated values are preserved, the values are not reordered.

How to concatenate multiple JSON arrays into a single array in MariaDB?

2 Answers2