PostgreSQL 11.8如何查询jsonb数组字段满足OR条件的行

Got it, let's adjust your query to handle the updated extras field where SIZE is now a jsonb array instead of a single value. Here's how to do it effectively:

Core Query for Size Array Match #

PostgreSQL has a handy jsonb operator ?| that checks if a jsonb array contains any of the values in a given text array. Perfect for your use case of finding rows where SIZE includes "38" or "4".

Full Refactored Query #

If you want to keep the original color filtering logic alongside the new size check, here's the complete query:

WHERE 
  -- Check if SIZE array contains "38" OR "4"
  products_alias.extras -> 'SIZE' ?| array['38', '4']
  AND 
  -- Original color matching logic (still valid since COLOUR is a single value)
  (products_alias.extras @> '{"COLOUR":"Flerfärgat"}' OR products_alias.extras @> '{"COLOUR":"Grå"}')

Breakdown of Key Changes #

• extras -> 'SIZE': Extracts the SIZE value from the extras jsonb column (now an array type).
• ?| array['38', '4']: This operator checks if the extracted array contains either "38" or "4". Note that we use string values here because your example shows SIZE elements are quoted strings (e.g., "38").
• The color filtering logic stays the same because COLOUR is still a single key-value pair, so the @> (contains) operator works as before.

Performance Tip #

If your products table is large, add a GIN index on the SIZE array to speed up the query:

CREATE INDEX idx_products_extras_size ON products USING GIN ((extras -> 'SIZE'));

GIN indexes are optimized for jsonb array and containment operations, so this will make your ?| checks much faster.

Alternative (Less Efficient) Approach #

If you prefer to unnest the array explicitly (not recommended for large datasets), you could use jsonb_array_elements, but you'll need to add DISTINCT to avoid duplicate rows:

SELECT DISTINCT products_alias.*
FROM products_alias
JOIN jsonb_array_elements(products_alias.extras -> 'SIZE') AS size_element
  ON size_element::text IN ('"38"', '"4"')
WHERE 
  (products_alias.extras @> '{"COLOUR":"Flerfärgat"}' OR products_alias.extras @> '{"COLOUR":"Grå"}')

This works, but the ?| operator approach is cleaner and more performant.

内容的提问来源于stack exchange，提问作者shuba.ivan