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

Question

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.

lemon · Accepted Answer · 2022-06-06 18:26:26Z

3

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.

answered Jun 6, 2022 at 18:26

lemon

15.6k6 gold badges23 silver badges42 bronze badges

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

3 Comments

Aԃιƚყα Gυɾαʋ Over a year ago

Man, that's simplest and elegant approach i have learned today. Big thanks again for giving me helping hand.

lemon Over a year ago

Additional small tip: as long as this is an aggregation function, you can use a GROUP BY clause if you need to group specific rows that belong together on other columns. Glad this answer was helpful for you. @THINKINGMACHINE

Aԃιƚყα Gυɾαʋ Over a year ago

Aye cap. Noted.

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related