求助：PostgreSQL中支持数组与对象的递归CTE实现问题

我懂你现在的困扰——你的递归CTE能搞定嵌套JSON对象，但一碰到数组就直接停住了对吧？核心问题在于没同时兼容对象和数组两种JSON结构的递归逻辑，而且得用LATERAL来动态适配不同类型对应的处理函数（jsonb_each处理对象，jsonb_array_elements处理数组）。下面是修正后的完整代码，能完美遍历嵌套的对象和数组，还会给数组节点标记索引路径：

WITH RECURSIVE jsonRecurse AS (
    -- 初始节点：处理顶层JSON对象
    SELECT 
        j.key::text AS path,
        j.key::text AS node_name,
        j.value
    FROM jsonb_each(to_jsonb('{ 
        "key1": { 
            "key2": [ 
                { "key3": "test3", "key4": "test4" } 
            ] 
        }, 
        "key5": [ 
            { "key6": [ 
                { "key7": "test7" } 
            ] } 
        ] 
    }'::jsonb)) j

    UNION ALL

    -- 递归分支：同时处理对象和数组
    SELECT 
        -- 数组节点用[index]标记路径，对象用.key拼接
        CASE 
            WHEN jsonb_typeof(jr.value) = 'array' THEN jr.path || '[' || (row_number() OVER (PARTITION BY jr.path) - 1) || ']'
            ELSE jr.path || '.' || jr2.key
        END AS path,
        -- 数组节点的名称用索引值，对象用原key
        CASE 
            WHEN jsonb_typeof(jr.value) = 'array' THEN (row_number() OVER (PARTITION BY jr.path) - 1)::text
            ELSE jr2.key
        END AS node_name,
        jr2.value
    FROM jsonRecurse jr
    -- 用LATERAL UNION ALL拆分对象/数组的处理逻辑
    LEFT JOIN LATERAL (
        -- 分支1：处理对象类型的节点
        SELECT key, value FROM jsonb_each(jr.value) WHERE jsonb_typeof(jr.value) = 'object'
        UNION ALL
        -- 分支2：处理数组类型的节点，用空字符串占位key，后续补索引
        SELECT '' AS key, value FROM jsonb_array_elements(jr.value) WHERE jsonb_typeof(jr.value) = 'array'
    ) jr2 ON true
    -- 只对非叶子节点（对象/数组）继续递归
    WHERE jsonb_typeof(jr.value) IN ('object', 'array')
)
-- 可以选择只输出最终的叶子节点（非对象/数组类型）
SELECT path, node_name, value 
FROM jsonRecurse
WHERE jsonb_typeof(value) NOT IN ('object', 'array');

关键改动说明： #

• LATERAL UNION ALL拆分逻辑：把对象和数组的处理分成两个独立分支，通过WHERE条件过滤当前节点类型，避免了直接用CASE调用不同函数导致的类型不匹配报错。
• 数组路径可视化：用row_number()生成数组的0起始索引，路径格式变成key2[0]这种直观形式，方便定位数组元素。
• 精准递归终止：只对object或array类型的节点继续递归，遇到字符串、数字等叶子节点就自动停止。

运行这段代码后，会输出所有叶子节点的完整路径和对应值，比如：

• key1.key2[0].key3 → test3
• key1.key2[0].key4 → test4
• key5[0].key6[0].key7 → test7

这样就完全覆盖了对象和数组的嵌套递归需求啦！

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