How to convert JSON object rows into JSON array in MySQL?

June 6, 2022

Stuck in one exercise task where I want JSON objects to be in form of a JSON array. Actually I have reached up to 1st stage to convert things into JSON object first.

Second stage is where my output should be as same as expected output as below, but it is not coming.

Current output :

{"name": "Peter", "uniqueId": "1"}
{"name": "MaryChan", "uniqueId": "3"}

Expected output :

[{"name": "Peter", "uniqueId": "1"}, {"name": "MaryChan", "uniqueId": "3"}]

Query :

SELECT JSON_OBJECT('uniqueId', uniqueId, 'name', name) actors
FROM (
select stf.id as familyId, stl.skill_type_name  as name
from actor_family af, actor_layered al
where af.id = al.actor_family_id) AS actors
GROUP BY uniqueId;

Some have suggested to use GROUP_CONCAT to get it done but still I’m not able to achieve the expected format.

Any help or pointers are welcomed.

>Solution :

You can use MySQL JSON_ARRAYAGG aggregation function:

SELECT JSON_ARRAY_AGG(JSON_OBJECT('uniqueId', uniqueId, 'name', name)) actors
FROM (
select stf.id as familyId, stl.skill_type_name  as name
from actor_family af, actor_layered al
where af.id = al.actor_family_id) AS actors
GROUP BY uniqueId;

Try it here.