PostgreSQL jsonb：对象转单元素数组与嵌套JSON扁平化问题

针对node4可能是单个对象或对象数组的场景，无需判断类型做UNION，可以利用PostgreSQL JSONPath的||合并操作符统一处理两种情况：

SELECT jsonb_path_query(data, '$.node4.attr1 || $.node4[*].attr1')#>> '{}' AS attr1
FROM your_table;

逻辑说明 #

• 当node4是单个对象时，$.node4[*].attr1返回空数组，||合并后仅保留$.node4.attr1的结果；
• 当node4是数组时，$.node4.attr1返回null，||合并后仅保留数组元素的attr1值；
• #>> '{}'用于将JSONPath查询返回的jsonb类型转换为文本（你已理解此作用，此处仅作补充说明）。

完全可行，通过递归CTE（Common Table Expression）动态判断节点类型，展开数组的同时累积父级信息：

WITH RECURSIVE flatten_json AS (
    -- 初始节点：根数据、空父路径、空父信息
    SELECT
        data AS current_node,
        '{}'::text[] AS parent_path,
        jsonb_build_object() AS parent_info
    FROM your_table
    UNION ALL
    SELECT
        arr.elem AS current_node,
        fj.parent_path || j.key,
        fj.parent_info || CASE
            WHEN jsonb_typeof(j.value) != 'array' THEN jsonb_build_object(j.key, j.value)
            ELSE '{}'::jsonb
        END AS parent_info
    FROM flatten_json fj,
         jsonb_each(fj.current_node) j(key, value),
         -- 动态展开数组：数组则拆分为元素，非数组则保留单个值
         LATERAL (
             SELECT unnest(
                 CASE WHEN jsonb_typeof(j.value) = 'array' 
                      THEN jsonb_array_elements(j.value) 
                      ELSE ARRAY[j.value] END
             ) AS elem
         ) arr
    WHERE jsonb_typeof(fj.current_node) != 'scalar' -- 跳过标量值，避免无限递归
)
-- 输出合并了父级信息的扁平化结果
SELECT fj.parent_info || fj.current_node AS full_flattened_info
FROM flatten_json fj
WHERE jsonb_typeof(fj.current_node) IN ('object', 'scalar'); -- 根据需求筛选最终节点类型

逻辑说明 #

• 递归CTE从根节点开始，逐层遍历JSON的键值对；
• 遇到数组类型的节点时自动展开为单个元素，非数组节点直接保留；
• 每次递归都会将非数组类型的父节点信息存入parent_info，最终合并父信息与当前节点，得到完整的层级数据；
• 无需预先知道父节点类型，全程动态判断处理。

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