如何从PostgreSQL的JSONB字段中仅返回元素子集

云上老周

2026-7-30

PostgreSQL 筛选JSONB数组中的指定对象

我在PostgreSQL中有一张表，结构及数据如下：

id	name	objects(jsonb)
1	first	`[{"name":"a", "value":"1", "param":{}}, {"name":"b", "value":"55", "param":{}}, {"name":"c", "value":"234", "param":{}}]`
2	second	`[{"name":"b", "value":"22", "param":{}}, {"name":"c", "value":"24", "param":{}}]`
3	third	`[{"name":"c", "value":"345", "param":{}}]`
4	forth	`[{"name":"a", "value":"123", "param":{}}, {"name":"b", "value":"456", "param":{}}]`

需要编写查询语句返回表中所有行，但仅保留objects列里符合指定条件的对象子集。

场景1：仅筛选`name="b"`的元素

预期输出：

id	name	objects(jsonb)
1	first	`[{"name":"b", "value":"55", "param":{}}]`
2	second	`[{"name":"b", "value":"22", "param":{}}]`
3	third	`[]`
4	forth	`[{"name":"b", "value":"456", "param":{}}]`

场景2：筛选`name="b"`或`name="c"`的元素

预期输出：

id	name	objects(jsonb)
1	first	`[{"name":"b", "value":"55", "param":{}}, {"name":"c", "value":"234", "param":{}}]`
2	second	`[{"name":"b", "value":"22", "param":{}}, {"name":"c", "value":"24", "param":{}}]`
3	third	`[{"name":"c", "value":"345", "param":{}}]`
4	forth	`[{"name":"b", "value":"456", "param":{}}]`

解决方案

场景1查询语句

SELECT 
    id,
    name,
    jsonb_agg(obj) FILTER (WHERE obj->>'name' = 'b') AS objects
FROM 
    your_table,
    jsonb_array_elements(objects) AS obj
GROUP BY 
    id, name;

场景2查询语句

SELECT 
    id,
    name,
    jsonb_agg(obj) FILTER (WHERE obj->>'name' IN ('b', 'c')) AS objects
FROM 
    your_table,
    jsonb_array_elements(objects) AS obj
GROUP BY 
    id, name;