PostgreSQL动态提取层级JSON属性列的实现需求

要实现无需硬编码列名，将层级JSON转换为带父ID的表，可以通过动态SQL拼接实现，核心思路是先提取JSON中所有节点的唯一键，再自动生成查询的SELECT字段列表。

步骤1：提取所有节点的唯一键 #

先从层级JSON中提取所有节点（包含各级子节点），收集所有不重复的键名：

WITH json_data AS (
    SELECT '{"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"}]}]}'::jsonb AS data
),
all_nodes AS (
    -- 提取所有带ID的节点（过滤非目标节点）
    SELECT jsonb_path_query(data, '$.** ? (@.ID != null)') AS node
    FROM json_data
),
unique_keys AS (
    -- 获取所有节点的唯一键
    SELECT DISTINCT key
    FROM all_nodes, jsonb_object_keys(node) AS key
)
-- 生成SELECT字段列表
SELECT string_agg(
    format('child->>''%s'' AS %I', key, key),
    ', '
) || ', parent->>''ID'' AS parent_id' AS select_list
FROM unique_keys;

执行后会得到自动生成的字段列表，示例输出：

child->>'DESC_D' AS "DESC_D", child->>'SEQ' AS "SEQ", child->>'ID' AS "ID", child->>'NODES' AS "NODES", parent->>'ID' AS parent_id

步骤2：封装为函数自动执行 #

将逻辑封装成PL/pgSQL函数，一键完成键提取、SQL拼接和执行：

CREATE OR REPLACE FUNCTION dynamic_hierarchy_json_to_table(p_json jsonb)
RETURNS SETOF record AS $$
DECLARE
    v_select_list text;
    v_sql text;
BEGIN
    -- 生成动态SELECT字段列表
    SELECT string_agg(
        format('child->>''%s'' AS %I', key, key),
        ', '
    ) || ', parent->>''ID'' AS parent_id'
    INTO v_select_list
    FROM (
        SELECT DISTINCT key
        FROM jsonb_path_query(p_json, '$.** ? (@.ID != null)') AS node,
             jsonb_object_keys(node) AS key
    ) AS keys;

    -- 拼接完整查询语句
    v_sql := format('
        SELECT DISTINCT %s
        FROM jsonb_path_query($1, ''$.** ? (@.NODES.type() == "array")'') AS parent
        CROSS JOIN LATERAL jsonb_array_elements(parent->''NODES'') AS child
        ORDER BY seq
    ', v_select_list);

    -- 执行动态SQL并返回结果
    RETURN QUERY EXECUTE v_sql USING p_json;
END;
$$ LANGUAGE plpgsql;

调用函数获取结果 #

调用时只需指定返回列的结构（与JSON键对应即可）：

SELECT * 
FROM dynamic_hierarchy_json_to_table(
    '{"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"}]}]}'::jsonb
) AS (seq text, desc_d text, id text, nodes text, parent_id text);

注意事项 #

• 若JSON中不同节点键不一致，所有键都会被列出来，不存在的键对应值为null。
• 如需保留NODES的JSON原生类型而非字符串，可将child->>''NODES''改为child->''NODES''，对应列类型改为jsonb。

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