CODEWITHSUNDEEP
mysql
MTK
MTK

Reputation: 3570

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

Upvotes: 1

Views: 97

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562260

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.

Upvotes: 5

Related Questions