Columns into JSON array-CodePudding

I have the following table:

Name	Pets
John	Bird
John	Cow
John	Dog
Nina	Cow
Nina	Fish
Nina	Cat

I would like to output it like so:

Name	Pets
John	["Bird","Cow","Dog"]
Nina	["Cow","Fish","Cat"]

I have this starting point, that converts a single column to JSON.

SELECT JSON_ARRAY(GROUP_CONCAT(column_name SEPARATOR ',')) AS names 
FROM table_name;

I'm new to working with arrays and JSON in SQL. Is this possible? What is the best solution?

CodePudding user response：

This approach is already a proper solution for this current case, just need to add GROUP BY expression, and exchange the aliases such as

SELECT name, JSON_ARRAY(GROUP_CONCAT(pets)) AS pets 
  FROM t
 GROUP BY name

where , is the default seperator, then adding that is redundant

Demo

P.S. seems your DB is MySQL (version at least 5.7 ) or its extension which's so called MariaDB or SQLite. It's expected to tag the DBMS, and its version, which you're using.