PostgreSQL中处理嵌套数组型JSON字段的SQL查询需求求助

问题背景 #

test表的attribute字段为非固定结构JSON类型，示例数据：

id | attribute
---|---------------------------------------
1  | {"a":["b",["b","c"],["e","f","g"]]}
2  | {"b":true}

需要将嵌套数组元素转为点分隔的字符串数组，非数组类型（如布尔值）保持原样，期望输出：

id | attribute
---|-------------------------
1  | ["b","b.c","e.f.g"]
2  | true

现有SQL问题 #

你提供的SQL存在硬编码键名、逻辑冗余（如不必要的replace操作）、可读性差等问题，且无法适配其他顶级键的情况。

优化后的解决方案 #

以下SQL可通用处理单顶级键的JSON结构，自动识别数组/非数组类型并完成转换：

WITH top_key AS (
    SELECT
        id,
        attribute,
        jsonb_object_keys(attribute) AS key
    FROM test
)
SELECT
    id,
    CASE jsonb_typeof(attribute -> key)
        WHEN 'array' THEN
            (SELECT array_agg(
                CASE jsonb_typeof(arr_elem)
                    WHEN 'string' THEN arr_elem::text
                    WHEN 'array' THEN string_agg(sub_elem, '.')
                END
            )
            FROM jsonb_array_elements(attribute -> key) AS arr_elem
            LEFT JOIN LATERAL jsonb_array_elements_text(arr_elem) AS sub_elem 
                ON jsonb_typeof(arr_elem) = 'array')::jsonb
        ELSE attribute -> key
    END AS attribute
FROM top_key;

逻辑说明 #

1. 获取顶级键：通过jsonb_object_keys提取每行JSON的唯一顶级键（适配任意单键结构）。
2. 类型判断与处理：
   • 若顶级值为数组：遍历数组元素，字符串元素直接保留，嵌套数组通过string_agg用点连接成字符串，最终将所有结果聚合为JSON数组。
   • 若顶级值为非数组类型（如布尔、数字等）：直接返回原JSON值，保持类型不变。

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