可以使用jsonpath类型、函数和语言：

SELECT DISTINCT
       child->>'SEQ' AS seq
     , child->>'DESC_D' AS desc_d
     , child->>'ID' AS id
     , parent->>'ID' AS parent_id
     , child->>'NODES' AS nodes
  FROM jsonb_path_query('{"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"}]}]}'
                       , '$.** ? (@.NODES.type() == "array")') AS parent
 CROSS JOIN LATERAL jsonb_array_elements(parent->'NODES') AS child
 ORDER BY seq

结果：
| 顺序|描述_d|身份证|父代标识|节点|
| - ------| - ------| - ------| - ------| - ------|
| 1个|法姆|二三零四五零零|零|[{"识别号"：" 5214 "，"序列号"："2.1"、"节点"：[{"识别号"：" 999 "，"序列号"："3.1"、"描述_D"："测试1.1 "}]，"描述_D "："测试1 "}，{" ID"："74542"，"序列号"："2.2"、"描述_D"："测试2 "}]|
| 2.1 |测试1|小行星5214|二三零四五零零|[{"识别号"：" 999 "，"序列号"："3.1"、"描述_D"："测试1.1 "}]|
| 2.2 |测试2|小行星7454|二三零四五零零|零|
| 3.1 |测试1.1|九九九|小行星5214|零|
参见dbfiddle

你朋友的答案是正确的（Edouard的方法也是如此）。有两件事可能会使递归查询更容易阅读。您可以使用->>操作符，它基本上执行json_extract_path_text函数。您也可以省略CROSS JOIN LATERAL，因为任何返回集合的函数在FROM后面加上逗号时都暗示了连接类型。在您的情况下，ID是唯一的，因此不需要DISTINCT ON (ID)，但如果JSON * 可能 * 有重复的ID，则需要将其放回。

WITH RECURSIVE CTE(SEQ, DESC_D, ID, PARENT_ID, NODES) AS (
  SELECT e.element ->> 'SEQ' SEQ,
         e.element ->> 'DESC_D' DESC_D,
         e.element ->> 'ID' ID,
         NULL PARENT_ID,
         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),
       json_array_elements(a.val) e(element)
  WHERE json_typeof(a.val) = 'array'
  UNION ALL
  SELECT e.element ->> 'SEQ' SEQ,
         e.element ->> 'DESC_D' DESC_D,
         e.element ->> 'ID' ID,
         r.ID PARENT_ID,
         e.element -> 'NODES' NODES
  FROM CTE r,
       json_array_elements(r.NODES) e(element)
)
SELECT *
FROM CTE;

为了让它更容易理解，只要记住关于UNION ALL的部分永远是你的出发点，后面的部分是深入挖掘，只要分别看每个部分就可以帮助你理解它。