PostgreSQL如何根据多组属性键值对匹配查询目标item记录

这是典型的实体-属性-值（EAV）表结构查询场景，核心要解决的问题是：单个item的多个属性分散存储在item_attribute表的不同行，需要筛选出同时满足所有指定属性键值对的item记录，以下是几种可直接使用的实现方式：

方案1：分组聚合过滤（通用性最强，推荐优先使用） #

实现逻辑：先筛选出命中任意一个目标属性条件的属性记录，按item_id分组后，统计该item命中的符合要求的属性个数，个数与传入的属性对总数相等，即说明该item满足所有匹配条件，最后关联item主表返回结果。
查询country=DE 且 size=M的示例SQL：

SELECT i.id, i.name
FROM item i
INNER JOIN item_attribute ia
  ON i.id = ia.item_id
WHERE (ia.name, ia.value) IN (
  ('country', 'DE'),
  ('size', 'M')
)
GROUP BY i.id, i.name
HAVING COUNT(DISTINCT ia.name) = 2; -- 数值与传入的属性对总数量保持一致

如果需要切换查询条件为country=EN 且 size=M，只需要修改IN子句里的元组为('country', 'EN'), ('size', 'M')即可，HAVING后的计数不需要改动。

优化提示：如果业务上保证同一个item不会出现重名属性（即item_id + name为联合唯一键），可以把COUNT(DISTINCT ia.name)替换为COUNT(*)，查询性能会进一步提升。

方案2：JSONB包含匹配（PostgreSQL特性，灵活度高） #

利用PostgreSQL原生的JSONB类型和包含运算符，可以把单个item的所有属性聚合成JSON对象后直接做包含判断，写法更简洁，尤其适合查询属性数量不固定的场景：

SELECT i.id, i.name
FROM item i
INNER JOIN (
  SELECT 
    item_id,
    jsonb_object_agg(name, value) AS attr_map
  FROM item_attribute
  GROUP BY item_id
) attr ON i.id = attr.item_id
WHERE attr.attr_map @> '{"country":"DE", "size":"M"}'::jsonb;

这里的@>是JSONB类型的包含运算符，只要属性聚合后的对象包含右侧JSON中所有的键值对，就会判定为匹配。切换查询条件时只需要修改右侧JSON里的键值对即可，不需要调整其他查询逻辑。如果属性表数据量较大，可以给聚合生成的attr_map字段建立GIN索引，查询速度会有明显提升。

方案3：多表关联匹配（适合属性数量固定的场景） #

如果每次查询的属性数量固定，也可以通过多次关联属性表的方式实现，逻辑直观，在属性字段建有索引时查询效率很高：

SELECT i.id, i.name
FROM item i
-- 关联匹配country属性
INNER JOIN item_attribute ia_country
  ON i.id = ia_country.item_id
  AND ia_country.name = 'country'
  AND ia_country.value = 'DE'
-- 关联匹配size属性
INNER JOIN item_attribute ia_size
  ON i.id = ia_size.item_id
  AND ia_size.name = 'size'
  AND ia_size.value = 'M';

这个方案的缺点是灵活度低，查询的属性数量变化时需要同步调整JOIN的次数，不适合属性条件动态变化的业务场景。

常见错误写法避坑 #

不要尝试写如下单表WHERE条件的写法：

-- 错误写法，永远返回空结果
SELECT * FROM item_attribute 
WHERE name = 'country' AND value = 'DE' 
  AND name = 'size' AND value = 'M'

单行属性记录的name字段不可能同时等于两个不同的值，这类写法完全无法命中数据。

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