如何用JSON_TABLE提取多层嵌套JSON中所有item的id和name？

要实现无需指定完整路径提取所有层级的item节点的id和name，核心是利用JSON路径的递归下降运算符（不同数据库语法略有差异，但核心逻辑一致），下面以主流数据库为例说明：

Oracle 实现 #

假设你有存储JSON数据的表your_table，其中json_column是存放嵌套JSON的字段，直接使用$..item递归匹配所有层级的item节点：

示例JSON #

{
  "id": "root",
  "item": {
    "id": "level1",
    "name": "一级项目",
    "children": [
      {
        "item": {
          "id": "level2-1",
          "name": "二级项目1"
        }
      },
      {
        "item": {
          "id": "level2-2",
          "name": "二级项目2",
          "subitems": [
            {
              "item": {
                "id": "level3-1",
                "name": "三级项目1"
              }
            }
          ]
        }
      }
    ]
  }
}

对应SQL #

SELECT jt.id, jt.name
FROM your_table t,
     JSON_TABLE(
       t.json_column,
       '$..item' COLUMNS (
         id VARCHAR2(100) PATH '$.id',
         name VARCHAR2(200) PATH '$.name'
       )
     ) jt;

• $..item 会遍历JSON结构中所有层级的item对象，不管它在数组、子对象还是更深的嵌套里
• JSON_TABLE会将每个匹配到的item拆分为一行，提取对应的id和name字段

PostgreSQL 实现 #

PostgreSQL使用jsonb_path_query配合递归路径$.**."item"，再通过jsonb_to_record解析字段：

SELECT jt.id, jt.name
FROM your_table t,
     jsonb_path_query(t.json_column, '$.**."item"') AS item_obj,
     jsonb_to_record(item_obj) AS jt(id text, name text);

注意事项 #

• 确保你的数据库版本支持JSON路径递归：Oracle 12c及以上、PostgreSQL 10及以上都支持该特性
• 如果部分item节点缺少id或name，查询结果中对应字段会返回NULL，可通过COALESCE设置默认值
• 若item本身是数组（比如"item": [{"id": "a"}, {"id": "b"}]），递归路径依然能匹配数组内的每个元素，不会遗漏

内容的提问来源于stack exchange，提问作者marciel.deg