Convert rows in MariaDB to json array

Question

I tried to run this SQL in MariaDB - 10.4.22-MariaDB:

SELECT CONCAT( '[', GROUP_CONCAT( JSON_ARRAY( name ) ), ']' ) FROM `mytable`;

But I'll get this:

[["ith1,"],["ith2"],..]

I need to get it without the brackets for each ithem, so I'll get just basic array:

["ith1,","ith2",..]

I also tried to use just GROUP_CONCAT, but if the separator is also in text, the whole array is then broken.

Example data:

Thanks!

@Barmar I'm using old version of MariaDB. JSON_ARRAYAGG() was unfortunately added in MariaDB 10.5.0. mariadb.com/kb/en/json_arrayagg — pepeD
– pepeD, Commented Mar 9, 2022 at 20:26
I edited to make it clear this is MariaDB, not MySQL. MariaDB forked from MySQL a long time ago, and it should not be considered as a compatible product anymore. — Bill Karwin
– Bill Karwin, Commented Mar 9, 2022 at 20:32

Barmar · Accepted Answer · 2022-03-09 20:36:43Z

2

Use JSON_QUOTE() in GROUP_CONCAT()

SELECT CONCAT('[', GROUP_CONCAT(JSON_QUOTE(name)), ']') FROM test3;

answered Mar 9, 2022 at 20:26

Barmar

789k57 gold badges554 silver badges669 bronze badges

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

1 Answer 1