PostgreSQL中查询仅含指定键且值匹配的jsonb行

要实现仅包含查询条件中所有键、且对应值存在于json数组的行查询，需要同时满足两个核心条件：值匹配 + 键集合完全一致（数量和键名均匹配），具体方案如下：

核心实现思路 #

1. 值匹配：保留你已实现的逻辑，用@>操作符验证目标键的json数组包含指定值。
2. 键集合校验：确保data列的键集合与查询条件的键集合完全一致（无多余键、无缺失键）。

具体SQL示例 #

假设当前查询条件为{"bar":"def", "foo":"abc"}，对应的查询语句：

SELECT *
FROM people
WHERE
  -- 验证每个键的数组包含目标值
  data->'bar' @> '["def"]'::jsonb
  AND data->'foo' @> '["abc"]'::jsonb
  -- 校验键集合完全匹配（排序后对比数组，避免键顺序影响）
  AND (SELECT array_agg(key ORDER BY key) FROM jsonb_each_text(data)) = ARRAY['bar', 'foo']::text[];

不同查询条件的适配 #

• 当查询条件为{"bar":"def", "foo":"hij"}时，仅修改值匹配部分：

  SELECT *
  FROM people
  WHERE
    data->'bar' @> '["def"]'::jsonb
    AND data->'foo' @> '["hij"]'::jsonb
    AND (SELECT array_agg(key ORDER BY key) FROM jsonb_each_text(data)) = ARRAY['bar', 'foo']::text[];
  

逻辑说明 #

• 对于{"bar":"def"}这类查询：data的键数组是['bar','foo']，与查询的['bar']不相等，因此不会返回结果。
• 对于{"bar":"def", "foo":"abc", "xyz":"123"}这类查询：查询的键数组长度为3，而data的键数组长度为2，对比不相等，也不会返回结果。

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