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

Question

我期望我的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

SQLSELECT查询应该是什么，以便输出 1个平面表 ，可以很容易地将其转换为JSON（在PHP或JavaScript / jQuery中）？

我什至以正确的方式走这条路吗？

Answer 1

您可以直接从MySQL生成JSON内容。这是可与MySQL 5.7或更高版本一起使用的解决方案。

首先，coonsider函数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"}        |

为了生成您的预期输出，我们将对JOIN表进行自我查找以找到子记录，并使用聚合函数JSON_ARRAYAGG()生成内部JSON数组。聚合的附加级别将所有内容填充到单个对象中。如您的样本数据所示，我假设根项目具有parent_id = 0并且只有一个层次结构：

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

DB Fiddle上的演示