How to select multiple rows and return them as JSON in one column using MySQL?

Question

I have this data in MySQL that looks like this (the actual data is different but I just to make it easer)

Note: children column is JSON

    SELECT id, name, children FROM people;

id	name	children
2	Jack	["5", "3"]
3	John	Null
5	Sara	Null

children can NOT have other children, children can NOT be in a different table because the data is similar and I need all the people at once, and I don't want to access the database for every child

The data should look like this after SELECT:

id	name	children
2	Jack	[{'id': '5', 'name': 'Sara'},{'id': '3', 'name': 'John'}]
3	John	Null
5	Sara	Null

is it possible, how can I do it ?

is there a better way to do this (not another table)

madflow · Accepted Answer · 2021-01-14 16:54:20Z

9

I would suggest to change your table layout to a parent->child relationship with a parent_id column.

The current layout mixes json string values with integer column values. When the table grows larger you will probably run into perfomance problems, too (just a guess currently).

The other approach would be:

https://www.db-fiddle.com/f/jbVqtZjZbmPAZ42S8A8fbT/1

CREATE TABLE people (id INT, name TEXT, parent_id INT);
INSERT INTO people (id, name, parent_id)
VALUES 
(2,'Jack', Null),
(3,'John', 2),
(5,'Sara', 2)
;


SELECT p.id, p.name, 

(SELECT JSON_ARRAYAGG(JSON_OBJECT('id', pc.id, 'name', pc.name)) FROM people pc
WHERE pc.parent_id=p.id) AS children

FROM people p

answered Jan 14, 2021 at 16:54

madflow

8,7013 gold badges45 silver badges61 bronze badges

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

1 Comment

Damaged Organic Over a year ago

Thank you, the answer is really helpful. From the official MySQL docs I didn't right away realize that JSON_ARRAYAGG function can actually accept JSON_OBJECT expression as an argument.

Collectives™ on Stack Overflow

How to select multiple rows and return them as JSON in one column using MySQL?

is it possible, how can I do it ?

is there a better way to do this (not another table)

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

is it possible, how can I do it ?

is there a better way to do this (not another table)

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related