Use jsonb array inside where IN or ANY in PostgreSQL

Question

I want to select all products that have active shipments (those have one or more shipment_ids in shipments table).

My product table has a jsonb column called data like this:

{
    "name": "foo",
    "shipments": [1,2,5,7]
}

I thought it would be as simple as something like to this:

... where shipment.id IN (products.data->'shipments')

or

... where shipment.id = any(products.data->'shipments')

but it is not.

After some digging I have managed to get it working, but I do not know if that is the simplest and most efficient solution for this:

select "products".* from "products"
   cross join LATERAL (
  select array_agg(ps.shipment::int) as ids
  from jsonb_array_elements(products.data -> 'shipments') as ps(shipment)
  ) ps
  where exists(select from shipments as "sh" where sh.id = any (ps.ids))
order by id

So do I actually need that cross join? Or there is a way to simply cast the JSON array to a Postgres integer array?

Answer 1

You can do it like this:

SELECT *
FROM products
WHERE EXISTS (
         SELECT 1 FROM shipment
         WHERE products.data @>
               jsonb_build_object('shipments', ARRAY[shipment.id])
      );

This query is still complicated and not very efficient.

The reason for that is the terrible data model. JSON in the database can be useful, but just stuffing all your data into a JSON column is a certain recipe for complicated and slow queries.

You should implement this with a junction table that implements the m-to-n relationship between the tables.

My rule of thumb is that you should use JSON in the database only for columns that are not used a lot inside the database.