如何筛选JSONB数组中包含指定值的行

问题背景 #

表c的topics列是JSONB类型，存储的是包含id和name字段的对象数组，需要筛选出包含指定主题名称的行，支持单个主题或多个主题的筛选需求。

单个主题筛选（例如"Finance"） #

方法1：使用@>包含运算符 #

@>运算符要求左侧JSONB包含右侧的结构，由于topics是对象数组，需要构造一个包含目标主题对象的数组作为匹配条件：

SELECT id, topics
FROM c
WHERE topics @> '[{"name": "Finance"}]'::jsonb;

该语句会匹配所有topics数组中至少存在一个name为"Finance"的对象的行。

方法2：展开数组后筛选 #

通过jsonb_array_elements将数组展开为行，再通过EXISTS子查询判断是否存在目标主题：

SELECT id, topics
FROM c
WHERE EXISTS (
    SELECT 1
    FROM jsonb_array_elements(c.topics) AS t
    WHERE t->>'name' = 'Finance'
);

此方法更适合需要添加复杂筛选条件的场景。

多个主题筛选 #

场景1：包含所有指定主题（例如同时包含"Finance"和"Politics"） #

方法1：多条件@>组合

通过AND连接多个@>条件，确保数组中同时存在所有指定主题：

SELECT id, topics
FROM c
WHERE topics @> '[{"name": "Finance"}]'::jsonb
  AND topics @> '[{"name": "Politics"}]'::jsonb;

方法2：展开数组后分组计数

展开数组后筛选目标主题，通过分组计数确认所有主题都存在：

SELECT id, topics
FROM c
CROSS JOIN jsonb_array_elements(c.topics) AS t
WHERE t->>'name' IN ('Finance', 'Politics')
GROUP BY id, topics
HAVING COUNT(DISTINCT t->>'name') = 2; -- 数字对应指定主题的数量

场景2：包含任意一个指定主题（例如包含"Finance"或"Politics"） #

方法1：多条件@>+OR组合

通过OR连接多个@>条件，匹配存在任意一个目标主题的行：

SELECT id, topics
FROM c
WHERE topics @> '[{"name": "Finance"}]'::jsonb
   OR topics @> '[{"name": "Politics"}]'::jsonb;

方法2：展开数组后IN筛选

通过EXISTS子查询结合IN语句，匹配存在任意目标主题的行：

SELECT id, topics
FROM c
WHERE EXISTS (
    SELECT 1
    FROM jsonb_array_elements(c.topics) AS t
    WHERE t->>'name' IN ('Finance', 'Politics')
);

错误写法说明 #

你之前尝试的topics @> '{"name": ["Finance"]}'无效，原因是右侧JSON结构与左侧不匹配：左侧topics是对象数组，而右侧是一个单键对象（键为name，值为数组），两者结构完全不同，因此无法匹配。

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