PostgreSQL中jsonb[]类型列查询失败问题求助

你的attributes列是jsonb[]类型，存储着包含多个JSON对象的数组，示例数据如下：

1 - [{"trait_type": "color", "value": "blue"},{"trait_type": "hair", "value": "short"}]
2 - [{"trait_type": "color", "value": "red"},{"trait_type": "hair", "value": "short"}]
3 - [{"trait_type": "color", "value": "green"},{"trait_type": "hair", "value": "long"}]
4 - [{"trait_type": "color", "value": "blue"},{"trait_type": "hair", "value": "med"}]

你之前的两次尝试都因为类型不匹配或格式错误失败：

• 第一次用字符串直接匹配，PostgreSQL把它当成了数组字面量，格式不符合要求：

  SELECT * FROM public.items WHERE attributes @> '[{"trait_type": "color", "value": "blue"}]';
  

  错误提示：

  Failed to run sql query: malformed array literal: "[{"trait_type": "color", "value": "blue"}]"
  

• 第二次构造了text[]数组，但@>操作符要求两边都是jsonb[]类型，类型不兼容：

  SELECT * FROM public.items WHERE attributes @> ARRAY['{"trait_type": "color", "value": "blue"}'];
  

  错误提示：

  Failed to run sql query: operator does not exist: jsonb[] @> text[]
  

下面是几种可行的解决方案：

方案1：展开数组后检查元素 #

使用unnest展开jsonb[]数组，逐个检查元素是否包含目标JSON对象：

SELECT * 
FROM public.items 
WHERE EXISTS (
    SELECT 1 
    FROM unnest(attributes) AS attr
    WHERE attr @> '{"trait_type": "color", "value": "blue"}'::jsonb
);

这个方案适合需要匹配“数组中存在任意一个符合条件的元素”的场景，unnest会把数组拆分成单行的jsonb数据，再用@>判断元素是否包含目标结构。

方案2：构造同类型的查询数组 #

如果要检查原数组是否包含指定的jsonb元素（数组级别的包含），需要把查询条件转为jsonb[]类型：

SELECT * 
FROM public.items 
WHERE attributes @> ARRAY['{"trait_type": "color", "value": "blue"}'::jsonb];

这里先把字符串转为jsonb，再构造jsonb[]数组，和列类型一致后，@>就能正常工作，它会判断左边数组是否包含右边数组的所有元素。

方案3：直接匹配数组元素 #

如果只是要判断目标JSON对象是否是数组中的某一个元素，可以用ANY简化写法：

SELECT * 
FROM public.items 
WHERE '{"trait_type": "color", "value": "blue"}'::jsonb = ANY(attributes);

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