如何用动态SQL查询提取PostgreSQL中嵌套JSONB的指定键值？

PostgreSQL提供两种通用方法，能轻松提取JSONB列中任意层级（哪怕4-5层）的特定键值，无需手动写多层嵌套的提取逻辑，还能动态适配不同目标键名。

方法1：用jsonb_path_query（推荐，最简洁） #

PostgreSQL 12+支持的JSON路径查询功能，用**通配符可匹配任意层级节点，不管是嵌套对象还是数组元素里的键都能命中。

假设你的表名为your_table，JSONB列是metadata，要提取所有key3的值，直接执行：

-- 提取所有匹配的key3值（每个值返回一行）
SELECT jsonb_path_query(metadata, '$.**."key3"') AS key3_value
FROM your_table;

如果想把同一行内的多个key3值合并为数组再拆成单行，可搭配jsonb_path_query_array和unnest：

SELECT unnest(jsonb_path_query_array(metadata, '$.**."key3"')) AS key3_value
FROM your_table;

动态适配不同键名 #

若要动态指定目标键（比如从变量输入），用format函数生成安全的路径表达式，避免SQL注入：

-- 预定义参数化查询
PREPARE get_nested_key(text) AS
SELECT unnest(jsonb_path_query_array(metadata, format('$.**."%s"', $1))) AS key_value
FROM your_table;

-- 调用时传入目标键名，例如此处提取key3
EXECUTE get_nested_key('key3');

方法2：递归CTE（更灵活，适合定制遍历逻辑） #

如果需要精细控制（比如过滤特定层级、记录键的完整路径），可以用递归CTE遍历整个JSON结构：

WITH RECURSIVE json_walk AS (
    -- 初始步骤：拆分顶层JSON对象的键值对
    SELECT
        id, -- 假设表有主键id，关联原数据行
        kv.key,
        kv.value,
        jsonb_typeof(kv.value) AS value_type
    FROM your_table
    CROSS JOIN jsonb_each(metadata) AS kv
    
    UNION ALL
    
    -- 递归处理嵌套对象：继续拆分键值对
    SELECT
        jw.id,
        sub_kv.key,
        sub_kv.value,
        jsonb_typeof(sub_kv.value) AS value_type
    FROM json_walk jw
    CROSS JOIN jsonb_each(jw.value) AS sub_kv
    WHERE jw.value_type = 'object'
    
    UNION ALL
    
    -- 递归处理数组：遍历每个元素
    SELECT
        jw.id,
        NULL, -- 数组元素无键名，暂设为NULL
        arr_elem,
        jsonb_typeof(arr_elem) AS value_type
    FROM json_walk jw
    CROSS JOIN jsonb_array_elements(jw.value) AS arr_elem
    WHERE jw.value_type = 'array'
)
-- 筛选出键名为key3的值
SELECT id, value AS key3_value
FROM json_walk
WHERE key = 'key3';

这种方法可扩展更多逻辑，比如记录每个键的完整路径、过滤特定类型的值等。

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