SQL query to return nested array of objects in JSON for SQLite

Question

I have 2 simple tables in a SQLite db and a nodejs, express api endpoint that should get results by student and have the subjects as a nested array of objects.

Tables: Student(id, name) and Subject(id, name, studentId)

This is what I need to result to look like:

{
    "id": 1,
    "name": "Student name",
    "subjects": 
    [{
        "id": 1,
        "name": "Subject 1"
    },
    {
        "id": 2,
        "name": "Subject 2"
    }]
}

How can I write a query to get this result?

Shawn · Accepted Answer · 2019-12-05 16:02:12Z

3

If your version of sqlite was built with support for the JSON1 extension, it's easy to generate the JSON from the query itself:

SELECT json_object('id', id, 'name', name
                 , 'subjects'
                 , (SELECT json_group_array(json_object('id', subj.id, 'name', subj.name))
                    FROM subject AS subj
                    WHERE subj.studentid = stu.id)) AS record
FROM student AS stu
WHERE id = 1;

record
---------------------------------------------------------------------------------------------------
{"id":1,"name":"Student Name","subjects":[{"id":1,"name":"Subject 1"},{"id":2,"name":"Subject 2"}]}

answered Dec 5, 2019 at 16:02

Shawn

53.9k3 gold badges29 silver badges74 bronze badges

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

2 Comments

Drashti Kheni Over a year ago

Is there any way to get record as pure json object not json string? I am getting response in json string

Shawn Over a year ago

@DrashtiKheni You'd have to use your language's facilities for parsing a string holding JSON data and converting it to a native data structure.

ingenuity · Accepted Answer · 2019-12-05 14:34:58Z

-2

It seems that all you need is a LEFT JOIN statement:

SELECT subject.id, subject.name, student.id, student.name 
FROM subject 
LEFT JOIN student ON subject.studentId = student.id
ORDER BY student.id;

Then just parse the rows of the response into the object structure you require.

answered Dec 5, 2019 at 14:34

ingenuity

224 bronze badges

Collectives™ on Stack Overflow

SQL query to return nested array of objects in JSON for SQLite

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related