如何在PostgreSQL中更简洁地将层级JSON转为表格？

我需要在PostgreSQL中将指定的层级JSON数据转换为表格形式。目前已有递归CTE的解决方案，但写法比较繁琐，希望找到更简洁的实现方式。

目标JSON数据 #

{"NODES":[{"DESC_D":"fam","SEQ":"1","ID":"2304500","NODES":[{"DESC_D":"test 1","SEQ":"2.1","ID":"5214","NODES":[{"DESC_D":"test 1.1","SEQ":"3.1","ID":"999"}]},{"DESC_D":"test 2","SEQ":"2.2","ID":"74542"}]}]}

现有递归CTE代码 #

WITH RECURSIVE CTE(SEQ, DESC_D, ID, PARENT_ID, NODES) AS (
  SELECT json_extract_path_text(e.element, 'SEQ') SEQ,
         json_extract_path_text(e.element, 'DESC_D') DESC_D,
         json_extract_path_text(e.element, 'ID') ID,
         NULL PARENT_ID,
         json_extract_path(e.element, 'NODES') NODES
  FROM json_each('{"NODES":[{"DESC_D":"fam","SEQ":"1","ID":"2304500","NODES":[{"DESC_D":"test 1","SEQ":"2.1","ID":"5214","NODES":[{"DESC_D":"test 1.1","SEQ":"3.1","ID":"999"}]},{"DESC_D":"test 2","SEQ":"2.2","ID":"74542"}]}]}'::JSON) a(KEY, val)
  CROSS JOIN LATERAL json_array_elements(a.val) e(element)
  WHERE json_typeof(a.val) = 'array'
  UNION ALL
  SELECT json_extract_path_text(e.element, 'SEQ') SEQ,
         json_extract_path_text(e.element, 'DESC_D') DESC_D,
         json_extract_path_text(e.element, 'ID') ID,
         r.ID PARENT_ID,
         json_extract_path(e.element, 'NODES') NODES
  FROM CTE r
  CROSS JOIN LATERAL json_array_elements(r.NODES) e(element)
)
SELECT DISTINCT ON (ID) *
FROM CTE;

更简洁的实现方案 #

可以用JSONB类型（比原生JSON性能更优）配合简化的递归逻辑来实现，代码更紧凑易读：

WITH RECURSIVE node_tree AS (
  -- 提取根层级的NODES数组作为初始节点
  SELECT 
    j->>'SEQ' AS seq,
    j->>'DESC_D' AS desc_d,
    j->>'ID' AS id,
    NULL::TEXT AS parent_id,
    j->'NODES' AS child_nodes
  FROM jsonb_array_elements('{"NODES":[{"DESC_D":"fam","SEQ":"1","ID":"2304500","NODES":[{"DESC_D":"test 1","SEQ":"2.1","ID":"5214","NODES":[{"DESC_D":"test 1.1","SEQ":"3.1","ID":"999"}]},{"DESC_D":"test 2","SEQ":"2.2","ID":"74542"}]}]}'::JSONB->'NODES') j

  UNION ALL

  -- 递归遍历子节点
  SELECT 
    c->>'SEQ' AS seq,
    c->>'DESC_D' AS desc_d,
    c->>'ID' AS id,
    p.id AS parent_id,
    c->'NODES' AS child_nodes
  FROM node_tree p
  CROSS JOIN LATERAL jsonb_array_elements(p.child_nodes) c
)
-- 只返回需要的字段，去掉冗余的child_nodes列
SELECT seq, desc_d, id, parent_id
FROM node_tree;

优化点说明 #

• 用JSONB替代JSON：支持更高效的操作符，性能更好
• 用->>直接提取字段值，替代冗长的json_extract_path_text函数
• 初始查询直接定位根节点的NODES数组，省去多余的json_each和类型判断步骤
• 去掉了不必要的DISTINCT ON (ID)：递归遍历每个节点只会被处理一次，无需去重

输出结果 #

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