如何用MySQL提取JSON数组中所有层级ID并生成一维数组？

问题背景 #

我有一张表my_table，其中的data列存储着嵌套结构的JSON数组，示例数据如下：

[
  {
    "id": 4065,
    "pageTitle": "Lorem Ipsum",
    "children": [
      { "id": 4067, "pageTitle": "Foo" },
      { "id": 4072, "pageTitle": "Bar" }
    ]
  },
  {
    "id": 4070,
    "pageTitle": "Another Lorem Ipsum",
    "children": [
      { "id": 4068, "pageTitle": "Another Foo" },
      { "id": 4073, "pageTitle": "Another Bar" }
    ]
  }
]

目前我用这条查询语句只能获取父级的ID：

SELECT JSON_EXTRACT(data, "$[*].id") FROM `my_table`;

它返回[4065, 4070]，完全忽略了子级的ID。我想知道：

• 怎么才能提取所有层级（包括子级、孙级，甚至更深层级）的ID？
• 能不能直接通过SQL返回像[4065, 4067, 4072, 4070, 4068, 4073]这样的一维数组，还是必须通过PHP这类程序来处理？

解决方案 #

方法一：MySQL 8.0+ 用递归CTE + JSON_TABLE直接生成一维数组 #

如果你的MySQL版本是8.0及以上，完全可以通过SQL直接实现这个需求，不需要依赖应用层处理。核心思路是用递归CTE遍历所有层级的JSON节点，提取每个节点的id，最后再聚合为一维数组。

完整的SQL语句如下：

WITH RECURSIVE nested_ids AS (
    -- 初始步骤：提取所有父级节点的id和它们的children数组
    SELECT 
        j.id,
        j.children
    FROM `my_table`
    JOIN JSON_TABLE(
        data,
        "$[*]" COLUMNS (
            id INT PATH "$.id",
            children JSON PATH "$.children"
        )
    ) j
    UNION ALL
    -- 递归步骤：提取子级节点的id和它们的children数组（如果有的话）
    SELECT 
        j.id,
        j.children
    FROM nested_ids
    JOIN JSON_TABLE(
        children,
        "$[*]" COLUMNS (
            id INT PATH "$.id",
            children JSON PATH "$.children"
        )
    ) j
    WHERE nested_ids.children IS NOT NULL AND JSON_LENGTH(nested_ids.children) > 0
)
-- 聚合所有id为一维数组
SELECT JSON_ARRAYAGG(id) AS all_ids FROM nested_ids;

这条语句会直接返回你想要的一维数组：[4065,4067,4072,4070,4068,4073]。

方法二：低版本MySQL（<8.0）：应用层（PHP）处理 #

如果你的MySQL版本低于8.0，不支持递归CTE，那只能先把整个JSON数组查出来，再在PHP里递归遍历提取所有ID。

示例PHP代码：

// 假设从数据库查询到的JSON字符串存在$json_data变量中
$json_data = '[ {"id": 4065, "pageTitle": "Lorem Ipsum", "children": [ {"id": 4067, "pageTitle": "Foo" }, {"id": 4072, "pageTitle": "Bar" } ] }, {"id": 4070, "pageTitle": "Another Lorem Ipsum", "children": [ {"id": 4068, "pageTitle": "Another Foo" }, {"id": 4073, "pageTitle": "Another Bar" } ] } ]';

$items = json_decode($json_data, true);
$all_ids = [];

// 递归遍历函数
function extract_ids($items, &$all_ids) {
    foreach ($items as $item) {
        $all_ids[] = $item['id'];
        // 如果有children，继续递归
        if (!empty($item['children'])) {
            extract_ids($item['children'], $all_ids);
        }
    }
}

extract_ids($items, $all_ids);
// 输出一维数组
print_r($all_ids);
// 或者转成JSON字符串
echo json_encode($all_ids);

这段代码会输出：Array ( [0] => 4065 [1] => 4067 [2] => 4072 [3] => 4070 [4] => 4068 [5] => 4073 )，转成JSON就是你要的格式。

内容的提问来源于stack exchange，提问作者ltdev