PostgreSQL中jsonb类型文本值读取及嵌套JSON数组处理

一、解决返回结果带双引号的问题 #

你当前查询中，jsonb_path_query_first返回的是jsonb类型值，因此会保留JSON原生的双引号格式。要获取纯文本值，有两种直接的解决方式：

方式1：用->>''提取jsonb值的文本内容 #

在jsonb_path_query_first结果后追加->>''，直接提取其文本形式：

jsonb_path_query_first(doc, '$.empdet.selectedMgrs.id')->>'' as mgr_list_id

方式2：在JSON路径中指定返回文本类型 #

使用::text在路径表达式中直接返回文本，无需额外操作：

jsonb_path_query_first(doc, '$.empdet.selectedMgrs.minApp::text') as minApp

修正后的基础查询（无引号输出） #

select  id as table_id,
        doc->'empdet'->'selectedDept'->>'empName' as empName,
        doc->'empdet'->>'deptno' as deptno,
        jsonb_path_query_first(doc, '$.empdet.selectedMgrs.id')->>'' as mgr_list_id,
        jsonb_path_query(doc, '$.empdet.selectedMgrs.list[*]')->>'mgrRole' as mgrRole,
        jsonb_path_query(doc, '$.empdet.selectedMgrs.list[*]')->>'mgrName' as mgrName,
        jsonb_path_query_first(doc, '$.empdet.selectedMgrs.minApp::text') as minApp,
        jsonb_path_query_first(doc, '$.appMgrs.mgrId::text') as mgrId,
        jsonb_path_query_first(doc, '$.appMgrs.mgrType::text') as mgrType,
        doc->>'deptLoc' as deptLoc,
        jsonb_path_query_first(doc, '$.jobIds[*]::text') as jobIds
from jtest;

二、高效读取嵌套多数组元素的方法 #

针对多层嵌套数组（如selectedMgrs数组内部包含list数组），jsonb_array_elements结合LATERAL连接是比jsonb_path_query更高效的方案——它是PostgreSQL专为JSON数组设计的原生函数，执行开销更低，逻辑也更清晰。

分步展开嵌套数组的优化查询 #

select
    j.id as table_id,
    j.doc->'empdet'->'selectedDept'->>'empName' as empName,
    j.doc->'empdet'->>'deptno' as deptno,
    -- 展开selectedMgrs外层数组
    mgrs.obj->>'id' as mgr_list_id,
    mgrs.obj->>'minApp' as minApp,
    -- 展开selectedMgrs内部的list数组
    mgr_list.obj->>'mgrName' as mgrName,
    mgr_list.obj->>'mgrRole' as mgrRole,
    -- 展开appMgrs数组
    app_mgr.obj->>'mgrId' as mgrId,
    app_mgr.obj->>'mgrType' as mgrType,
    j.doc->>'deptLoc' as deptLoc,
    -- 展开jobIds数组
    job_ids.obj::text as jobIds
from jtest j
-- 关联展开selectedMgrs数组
left join lateral jsonb_array_elements(j.doc->'empdet'->'selectedMgrs') as mgrs(obj) on true
-- 关联展开selectedMgrs内部的list数组
left join lateral jsonb_array_elements(mgrs.obj->'list') as mgr_list(obj) on true
-- 关联展开appMgrs数组
left join lateral jsonb_array_elements(j.doc->'appMgrs') as app_mgr(obj) on true
-- 关联展开jobIds数组
left join lateral jsonb_array_elements(j.doc->'jobIds') as job_ids(obj) on true;

优势说明 #

• 性能更优：jsonb_array_elements无需解析JSON路径表达式，执行计划更高效，大数据量下差距明显。
• 逻辑清晰：通过LATERAL连接分步展开嵌套数组，便于维护和扩展复杂的JSON结构。
• 灵活性高：可根据需求切换left join/inner join，控制是否保留空数组的行。

三、额外技巧 #

• 如果数组仅含单个元素（如你的jobIds），可直接用doc->'jobIds'->>0获取第一个元素，比jsonb_path_query_first更高效。
• 对于固定结构的JSON，优先使用->/->>操作符+jsonb_array_elements组合，性能优于JSON路径函数。

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