create JSON_ARRAY() from GROUP_CONCAT()

Question

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

your variable stores a string. use json_arrayagg instead of group_concat to get a json array instead. mysql does not support json_arrayagg distinct, though mariadb does. if you truly need distinct, select from a distinct subquery. — ysth
– ysth, Commented Oct 14, 2022 at 20:27

Bill Karwin · Accepted Answer · 2022-10-14 20:52:00Z

5

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.

edited Oct 14, 2022 at 20:52

answered Oct 14, 2022 at 20:26

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

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

Collectives™ on Stack Overflow

create JSON_ARRAY() from GROUP_CONCAT()

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related