Postgres Jsonb按属性值筛选数组元素并获取指定字段

直接使用Postgres的jsonb_array_elements函数展开嵌套数组，结合WHERE条件筛选目标元素，再提取指定字段即可，无需依赖元素在数组中的位置。

最终SQL语句（隐式横向连接写法） #

SELECT 
    app->>'name' AS external_app_name
FROM 
    act,
    jsonb_array_elements(act."MessageBody"->'results') AS res,
    jsonb_array_elements(res->'externalApps') AS app
WHERE 
    app->>'modifiedByUser' = 'User-2';

更清晰的横向连接写法（推荐） #

SELECT 
    app->>'name' AS external_app_name
FROM 
    act
LATERAL JOIN jsonb_array_elements(act."MessageBody"->'results') AS res
LATERAL JOIN jsonb_array_elements(res->'externalApps') AS app
WHERE 
    app->>'modifiedByUser' = 'User-2';

语句说明 #

1. jsonb_array_elements(act."MessageBody"->'results') AS res：将results数组展开为多行记录，每个数组元素对应一行res
2. jsonb_array_elements(res->'externalApps') AS app：将每个res中的externalApps数组展开为多行记录，每个数组元素对应一行app
3. WHERE app->>'modifiedByUser' = 'User-2'：筛选出modifiedByUser为User-2的元素
4. app->>'name'：提取符合条件元素的name字段值

补充提示 #

• 由于你的列是jsonb类型，直接使用->（返回jsonb对象）和->>（返回文本）操作符比强制转换为json更高效
• 该写法兼容results数组包含多个元素的情况，无需修改语句即可处理更复杂的场景

内容的提问来源于stack exchange，提问作者A. Hasemeyer