在SELECT查询结果中无需指定属性名展开嵌套JSONB数据的方法

结论 #

不存在原生静态SQL可以直接实现该需求，但可以通过动态SQL的方式实现无需手动枚举所有属性名的查询效果。

核心原因 #

SQL属于静态声明式语言，查询返回的列数量、列名、数据类型必须在查询执行前确定，而JSONB内的属性是动态可变的，数据库无法在执行静态查询时自动推断要返回的列结构，因此无法直接通过普通SELECT语句实现动态列展开。

实现方案 #

可以通过递归提取所有JSONB叶子节点的全路径，再拼接生成动态查询语句执行，步骤如下：

步骤1：提取所有JSONB全路径 #

先通过递归CTE提取data字段中所有叶子节点的全路径（支持嵌套对象、数组下标）：

WITH RECURSIVE extract_paths AS (
    -- 提取第一层属性
    SELECT 
        ARRAY[key] AS path,
        value,
        jsonb_typeof(value) AS type
    FROM x,
         jsonb_each(data)
    UNION ALL
    -- 递归展开嵌套对象
    SELECT 
        ep.path || key,
        ev.value,
        jsonb_typeof(ev.value) AS type
    FROM extract_paths ep,
         jsonb_each(ep.value) ev
    WHERE ep.type = 'object'
    UNION ALL
    -- 递归展开数组元素，下标从0开始
    SELECT 
        ep.path || (idx - 1)::text,
        arr_val.value,
        jsonb_typeof(arr_val.value) AS type
    FROM extract_paths ep,
         jsonb_array_elements(ep.value) WITH ORDINALITY arr_val(value, idx)
    WHERE ep.type = 'array'
)
-- 生成列名和对应的取值表达式
SELECT DISTINCT 
    'data.' || array_to_string(path, '.') AS col_name,
    format('data #>> %L', path) AS extract_expr
FROM extract_paths
WHERE type NOT IN ('object', 'array') -- 仅保留叶子节点
ORDER BY col_name;

步骤2：执行动态查询 #

方式1：psql客户端直接执行

如果使用psql客户端，可以通过\gexec命令直接执行生成的查询语句：

SELECT 'SELECT id, name, ' || string_agg(format('%s AS %I', extract_expr, col_name), ', ') || ' FROM x;'
FROM (
    WITH RECURSIVE extract_paths AS (
        SELECT ARRAY[key] AS path, value, jsonb_typeof(value) AS type
        FROM x, jsonb_each(data)
        UNION ALL
        SELECT ep.path || key, ev.value, jsonb_typeof(ev.value)
        FROM extract_paths ep, jsonb_each(ep.value) ev
        WHERE ep.type = 'object'
        UNION ALL
        SELECT ep.path || (idx - 1)::text, arr_val.value, jsonb_typeof(arr_val.value)
        FROM extract_paths ep, jsonb_array_elements(ep.value) WITH ORDINALITY arr_val(value, idx)
        WHERE ep.type = 'array'
    )
    SELECT DISTINCT 'data.' || array_to_string(path, '.') AS col_name,
           format('data #>> %L', path) AS extract_expr
    FROM extract_paths
    WHERE type NOT IN ('object', 'array')
) t \gexec

方式2：PL/pgSQL函数封装

如果需要在应用侧调用，可以封装为PL/pgSQL函数，返回JSON格式的展开结果：

CREATE OR REPLACE FUNCTION get_expanded_x()
RETURNS SETOF jsonb AS $$
DECLARE
    select_cols text;
    query_str text;
BEGIN
    -- 生成列查询表达式
    SELECT string_agg(format('%s AS %I', extract_expr, col_name), ', ')
    INTO select_cols
    FROM (
        WITH RECURSIVE extract_paths AS (
            SELECT ARRAY[key] AS path, value, jsonb_typeof(value) AS type
            FROM x, jsonb_each(data)
            UNION ALL
            SELECT ep.path || key, ev.value, jsonb_typeof(ev.value)
            FROM extract_paths ep, jsonb_each(ep.value) ev
            WHERE ep.type = 'object'
            UNION ALL
            SELECT ep.path || (idx - 1)::text, arr_val.value, jsonb_typeof(arr_val.value)
            FROM extract_paths ep, jsonb_array_elements(ep.value) WITH ORDINALITY arr_val(value, idx)
            WHERE ep.type = 'array'
        )
        SELECT DISTINCT 'data.' || array_to_string(path, '.') AS col_name,
               format('data #>> %L', path) AS extract_expr
        FROM extract_paths
        WHERE type NOT IN ('object', 'array')
    ) t;

    -- 拼接并执行动态SQL
    query_str := format('SELECT row_to_json(t) FROM (SELECT id, name, %s FROM x) t', select_cols);
    RETURN QUERY EXECUTE query_str;
END;
$$ LANGUAGE plpgsql;

-- 调用函数
SELECT * FROM get_expanded_x();

注意事项 #

• 该方法会取所有行出现过的所有JSON属性作为列，没有对应属性的行会返回NULL
• 如果数组长度较大，会生成大量列，可根据实际需求调整逻辑限制数组展开的最大长度

内容的提问来源于stack exchange，提问作者rap-2-h