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:

ID	Name
1	Football, boys "first group"
2	Football, girls "first group"
3	Streetball

Thanks!

Answer 1

Use JSON_QUOTE() in GROUP_CONCAT()

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

DEMO