如何用PostgreSQL JSON函数从JSON中获取指定数组对象

假设你的表结构包含一个JSONB类型字段（比如名为data），其中嵌套了gallery数组，以下是两种可靠的解决方案：

方法1：展开数组后过滤 #

通过jsonb_array_elements将数组元素展开为行，再根据ID过滤匹配项，适合需要单独提取对象的场景：

-- 提取单个匹配的JSON对象
SELECT elem
FROM your_table_name,
     jsonb_array_elements(data->'gallery') AS elem
WHERE elem->>'id' = '2'; -- 注意：如果ID是数字类型，用 (elem->'id')::int = 2

如果需要将匹配结果重新组合为JSON数组：

SELECT jsonb_agg(elem) AS matched_gallery_items
FROM your_table_name,
     jsonb_array_elements(data->'gallery') AS elem
WHERE elem->>'id' = '2';

方法2：使用JSON路径查询 #

利用jsonb_path_query直接通过路径表达式定位匹配元素，语法更简洁：

-- 提取匹配的JSON对象（支持批量匹配）
SELECT jsonb_path_query(data, '$.gallery[*] ? (@.id == 2)') AS matched_item
FROM your_table_name;

注意：路径表达式中ID的类型要与数据一致——如果ID是字符串，写成@.id == "2"；如果是数字，写成@.id == 2。

常见问题排查 #

• 之前查询无结果可能是类型不匹配：比如ID是数字，但你用了字符串格式的比较（如elem->>'id' = 2），此时需转换类型或调整比较值的格式。
• 若仅返回第一个对象，大概率是没遍历整个数组（比如用了data->'gallery'->0这种固定索引的写法），必须用[*]遍历所有数组元素。

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