如何通过SQL查询父子项以获取特定的JSON格式?

I am expecting this JSON for my jQuery code:{ "projects": [ { "id": "1", "project_name": "Carmichael House", "parent_id": "0", "children": [ { "id": "2", "project_name": "Carmichael Kitchen", "parent_id": "1" }, { "id": "3", "project_name": "Carmichael Bathroom", "parent_id": "1" } ] }, { "id": "2", "project_name": "Dowd Apartment", "parent_id": "0", "children": [ { "id": "4", "project_name": "Dowd Kitchen", "parent_id": "2" } ] } ]}This data will come from MySql table tbl_projects:idproject_nameparent_idWhat should the SQL SELECT query be, so that it will output 1 flat table, that can easily be converted into JSON (in PHP or JavaScript/jQuery)?Am I even approaching this the right way? 解决方案 You can generate JSON content directly from MySQL. Here is a solution that works with MySQL 5.7 or higher.As a starter, coonsider function JSON_OBJECT(), that generates a JSON object for each record in the table:SELECT p.*, JSON_OBJECT('id', id, 'project_name', project_name, 'parent_id', parent_id) jsFROM tbl_projects p;Given your sample data, this returns:| id | project_name | parent_id | js || --- | ------------------- | --------- | ---------------------------------------------------------------- || 1 | Carmichael House | 0 | {"id": 1, "parent_id": 0, "project_name": "Carmichael House"} || 2 | Carmichael Kitchen | 1 | {"id": 2, "parent_id": 1, "project_name": "Carmichael Kitchen"} || 3 | Carmichael Bathroom | 1 | {"id": 3, "parent_id": 1, "project_name": "Carmichael Bathroom"} || 4 | Dowd Apartment | 0 | {"id": 4, "parent_id": 0, "project_name": "Dowd Apartment"} || 5 | Dowd Kitchen | 4 | {"id": 5, "parent_id": 4, "project_name": "Dowd Kitchen"} |To generate your expected output, we will self-JOIN the table to find children records, and use aggregate function JSON_ARRAYAGG() to generate the inner JSON array. An additional level of aggregation stuffs everything into a single object. As showned in your sample data, I assumed that root projects have parent_id = 0 and that there is only one level of hierarchy:SELECT JSON_OBJECT('projects', JSON_ARRAYAGG(js)) resultsFROM ( SELECT JSON_OBJECT( 'id', p.id, 'project_name', p.project_name, 'parent_id', p.parent_id, 'children', JSON_ARRAYAGG( JSON_OBJECT( 'id', p1.id, 'project_name', p1.project_name, 'parent_id', p1.parent_id ) ) ) js FROM tbl_projects p LEFT JOIN tbl_projects p1 ON p.id = p1.parent_id WHERE p.parent_id = 0 GROUP BY p.id, p.project_name, p.parent_id) xYields:| results || -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- || {"projects": [{"id": 1, "children": [{"id": 2, "parent_id": 1, "project_name": "Carmichael Kitchen"}, {"id": 3, "parent_id": 1, "project_name": "Carmichael Bathroom"}], "parent_id": 0, "project_name": "Carmichael House"}, {"id": 4, "children": [{"id": 5, "parent_id": 4, "project_name": "Dowd Kitchen"}], "parent_id": 0, "project_name": "Dowd Apartment"}]} |Demo on DB Fiddle 这篇关于如何通过SQL查询父子项以获取特定的JSON格式?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 1403页，肝出来的..