如何通过 SQL 查询特定 JSON 格式的父子关系？

我希望我的 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查询be，这样就会输出1张平桌，可以轻松转换为 JSON（在 PHP 或 JavaScript/jQuery 中）？

我是否以正确的方式处理这个问题？

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

作为初学者，请考虑功能JSON_OBJECT() https://dev.mysql.com/doc/refman/8.0/en/json-creation-functions.html#function_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() https://dev.mysql.com/doc/refman/8.0/en/group-by-functions.html#function_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

Yields:

| 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 上的演示 https://www.db-fiddle.com/f/mr81oyYKLHCdoRB88TErS3/0