CODEWITHSUNDEEP
mysqlsql
Albaraa
Albaraa

Reputation: 83

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

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)

Upvotes: 5

Views: 7837

Answers (1)

madflow
madflow

Reputation: 8480

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

Upvotes: 9

Related Questions