PostgreSQL：查询JSONB字段包含指定数组所有元素的行

你的场景是：数据库表中tags字段为JSONB类型，存储的是包含value键的对象数组，需要筛选出包含指定数组所有value值的记录（比如指定London和Sydney时，仅返回同时包含这两个值的记录）。

当前的查询只能实现任意匹配（只要tags里有一个值在指定列表中就会返回），无法满足全元素匹配的需求。下面提供几种可行的解决方案：

方法1：统计匹配数量验证全包含 #

通过聚合函数统计tags中匹配指定值的唯一数量，当数量等于指定值的总数时，说明所有指定值都存在：

SELECT *
FROM mytable
WHERE (
    SELECT COUNT(DISTINCT tag->>'value')
    FROM jsonb_array_elements(tags) tag
    WHERE tag->>'value' IN ('London', 'Sydney')
) = 2 -- 此处数字需与指定的元素数量保持一致

方法2：利用JSONB的包含操作符@> #

如果tags的元素结构是固定的（每个元素都是带value键的对象），可以构造对应的目标JSONB数组，通过@>操作符验证tags是否包含所有目标元素：

SELECT *
FROM mytable
WHERE tags @> '[{"value": "London"}, {"value": "Sydney"}]'::jsonb

注：这种方式要求目标JSONB元素与tags中的元素完全匹配（如果元素有其他键，也需要在目标数组中完整写出），适合元素结构简单固定的场景。

方法3：双重NOT EXISTS验证无遗漏 #

通过嵌套的NOT EXISTS，确保指定的每个值都能在tags中找到，无需手动指定元素数量，灵活性更高：

SELECT *
FROM mytable
WHERE NOT EXISTS (
    SELECT *
    FROM unnest(array['London', 'Sydney']) AS target(value)
    WHERE NOT EXISTS (
        SELECT *
        FROM jsonb_array_elements(tags) AS tag
        WHERE tag->>'value' = target.value
    )
)

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