如何通过SQL查询父子项以获取特定的JSON格式? [英] How to SQL query parent-child for specific JSON format?
问题描述
我期望我的jQuery代码使用此JSON:
{
"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"
}
]
}
]
}
此数据将来自MySql表 tbl_projects
:
id
project_name
parent_id
SQL SELECT
查询应该是什么,以便输出 1个平面表,可以轻松地将其转换为JSON(在PHP或JavaScript/jQuery中)?
我什至以正确的方式走这条路吗?
您可以直接从MySQL生成JSON内容.这是可与MySQL 5.7或更高版本配合使用的解决方案.
首先,请考虑函数JSON_OBJECT()
,它为表中的每个记录生成一个JSON对象:
SELECT
p.*,
JSON_OBJECT('id', id, 'project_name', project_name, 'parent_id', parent_id) js
FROM tbl_projects p;
给出示例数据,将返回:
| 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"} |
要生成您的预期输出,我们将自行 I am expecting this JSON for my jQuery code: This data will come from MySql table What should the SQL 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 Given your sample data, this returns: To generate your expected output, we will self- Yields: 这篇关于如何通过SQL查询父子项以获取特定的JSON格式?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!JOIN
该表以查找子记录,并使用{
"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"
}
]
}
]
}
tbl_projects
:id
project_name
parent_id
SELECT
query be, so that it will output 1 flat table, that can easily be converted into JSON (in PHP or JavaScript/jQuery)?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) js
FROM tbl_projects p;
| 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"} |
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)) results
FROM (
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
) x
| 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"}]} |