如何在Supabase中实现JSONB数组的OR逻辑筛选？

场景1：同一trait_type下多value用OR #

假设需求是：必须满足SmartSkin为Chrome，同时Clothes是Standard Issue Armor 1 (Purple)或Standard Issue Armor 1 (Red)，可以用exists子查询实现：

SELECT *
FROM your_table
WHERE 
  -- 固定AND条件：SmartSkin必须是Chrome
  attributes @> '[{"trait_type":"SmartSkin", "value":"Chrome"}]'
  -- OR条件：Clothes匹配指定值之一
  AND EXISTS (
    SELECT 1
    FROM jsonb_array_elements(attributes) AS attr
    WHERE attr->>'trait_type' = 'Clothes'
      AND attr->>'value' IN ('Standard Issue Armor 1 (Purple)', 'Standard Issue Armor 1 (Red)')
  );

也可以用jsonb_path_exists简化写法：

SELECT *
FROM your_table
WHERE 
  attributes @> '[{"trait_type":"SmartSkin", "value":"Chrome"}]'
  AND jsonb_path_exists(
    attributes, 
    '$[*] ? (@.trait_type == "Clothes" && @.value in ("Standard Issue Armor 1 (Purple)", "Standard Issue Armor 1 (Red)"))'
  );

场景2：不同trait_type之间用OR #

如果需求是：（Clothes为Purple 或者 Full Helmet为Red），同时SmartSkin为Chrome，直接用OR结合JSONB包含操作符@>即可：

SELECT *
FROM your_table
WHERE 
  attributes @> '[{"trait_type":"SmartSkin", "value":"Chrome"}]'
  AND (
    attributes @> '[{"trait_type":"Clothes", "value":"Standard Issue Armor 1 (Purple)"}]'
    OR attributes @> '[{"trait_type":"Full Helmet", "value":"Standard Issue Helmet 1 (Red)"}]'
  );

如果是更复杂的多条件组合，exists子查询会更灵活：

SELECT *
FROM your_table
WHERE 
  -- 必须满足的AND条件
  EXISTS (
    SELECT 1
    FROM jsonb_array_elements(attributes) AS attr
    WHERE attr->>'trait_type' = 'SmartSkin' AND attr->>'value' = 'Chrome'
  )
  -- OR组合条件
  AND EXISTS (
    SELECT 1
    FROM jsonb_array_elements(attributes) AS attr
    WHERE (attr->>'trait_type' = 'Clothes' AND attr->>'value' = 'Standard Issue Armor 1 (Purple)')
       OR (attr->>'trait_type' = 'Full Helmet' AND attr->>'value' = 'Standard Issue Helmet 1 (Red)')
  );

性能优化建议 #

如果表数据量较大，建议给attributes列创建GIN索引，提升查询效率：

CREATE INDEX idx_attributes_gin ON your_table USING GIN (attributes);

内容的提问来源于stack exchange，提问作者Богдан