create JSON_ARRAY() from GROUP_CONCAT()

let’s say my_database has tbl1 tbl2 tbl3 like tables

I want to make an JSON_ARRAY with table names from my_database

I tried:

SET @bd = 'my_database';

SELECT GROUP_CONCAT(DISTINCT TABLE_NAME) INTO @my_tables
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = @bd;

SELECT JSON_ARRAY(@my_tables);

But I got a single element array

+-------------------+
| @my_tables         |
+-------------------+
| ["tbl1,tbl2,tbl3"] |
+-------------------+

I’m looking for ["tbl1","tbl2","tbl3"]

>Solution :

SELECT JSON_ARRAYAGG(TABLE_NAME) INTO @my_tables
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = @bd;

There’s no need to use DISTINCT in this query, because TABLE_NAME is guaranteed to be unique within a specific schema.

JSON_ARRAYAGG() requires MySQL 5.7.22 or later. If you have an older version of MySQL, it’s time to upgrade.