create JSON_ARRAY() from GROUP_CONCAT()-CodePudding

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"]

CodePudding user response：

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.