Postgresql – How to select by a JSONB array containing at least one element within a range

postgresql

I need to model products with properties. Properties need to be dynamic, in the sense that application users can add / delete properties to products. For this reason, I am using a JSONB column storing a JSONB object to model those dynamic properties, with each field of the object a dynamic property, for instance:

{
  product_number: "snsug1",
  color: "white",
  weight: [100, 200, 300]
  ...
}

Furthermore, as can be seen in the example above, some properties are allowed to have multiple values, as some products can come in different configurations (different values for the weight property for instance, for packets of sugar of different sizes). I am modeling those as JSONB arrays.

I need to be able to select products based on some filter values for the properties.

Filtering based on comparisons on single value properties are straightforward (for instance where properties->'color' = '"white"'::jsonb) and overlap queries for multi-value properties are straightforward too when the overlap target contains discreet values, like where properties->'weight' @> any(array[to_jsonb(200), to_jsonb(300)]).

The problem comes with filtering on a multi-value property based on overlap with a range of values (instead of overlap with discreet values as above). So my question is: how can I filter on a multi-value property like weight, with criteria that any of the values in the array should fall with a range?

Something like (pseudo code):

select id from products where properties->'weight' @> range(50, 150);

should return the entry in my example above, but 

select id from products where properties->'weight' @> range(350, 500);

should not.

The weight @> range(50, 150) bit in the pseudo code query above is what I am asking about. What should that look like?

If I am barking up the wrong tree altogether and this is not doable with my current model, then what would be a good alternative for solving my problem? Is there a more appropriate way of modeling the data that can lead to elegant and fast queries of the type above? The solution needs to perform well on large data sets, it needs to be able to make use of indexes for the above types of queries.

Best Answer

You need to use a sub-query that unnests all array elements together with an EXISTS predicate:

select p.*
from products p
where exists (select *
              from jsonb_array_elements(p.properties->'weight') as t(weight)
              where t.weight::int <@ range(50, 150));
Related Question