sql - SQL 查询以 JSON 格式为 SQLite 返回嵌套的对象数组

Question

我无法弄清楚这一点，我在 SQLite db 和 nodejs 中有 2 个简单的表，表达 api 端点应该由学生获得结果，并将主题作为对象的嵌套数组。表：Student(id, name) 和 Subject(id, name, studentId)

谁能帮我弄清楚这个查询？

这就是我需要的结果：

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

标签： sqlnode.jsjsonsqliteexpress

Answer 1

如果您的 sqlite 版本是在支持JSON1扩展的情况下构建的，则很容易从查询本身生成 JSON：

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"}]}