PostgreSQL中筛选jsonb数组含指定键的行？查询无结果求助

你的查询语句无法返回结果，是因为?操作符的用法不符合需求：当操作数是jsonb数组时，?是检查数组是否直接包含指定的jsonb值作为元素，而你传入的'[{"c"}]'会被解析为一个包含单个对象的数组，显然原数据里的数组元素是带有a、b、c键的完整对象，和这个值不匹配，所以没有结果。

下面是几种正确的实现方式：

方法1：展开数组后筛选（适合需查看匹配元素的场景） #

SELECT DISTINCT t.*
FROM public.test t
JOIN jsonb_array_elements(t.lines) elem ON elem ? 'c';

• jsonb_array_elements(t.lines)会把lines列的jsonb数组拆分成单个jsonb对象行
• elem ? 'c'检查每个对象是否包含c键
• 用DISTINCT避免原表中同一行被多次返回（如果数组里有多个含c键的对象）

方法2：使用JSON路径函数（简洁高效） #

SELECT *
FROM public.test
WHERE jsonb_path_exists(lines, '$[*] ? (@.c)');

• $[*]表示遍历数组中的所有元素
• ? (@.c)表示筛选出存在c键的元素，只要数组里有至少一个这样的元素，该行就会被选中

方法3：用EXISTS子查询（性能更优） #

SELECT *
FROM public.test t
WHERE EXISTS (
    SELECT 1
    FROM jsonb_array_elements(t.lines) elem
    WHERE elem ? 'c'
);

• 这种方式会在找到第一个符合条件的元素后就停止检查当前行的数组，比JOIN后去重的效率更高，适合只需要筛选行不需要查看元素的场景

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