Storing data in a JSON column in Postgres can be very handy but a bit more difficult to query than normal tables. In particular, querying arrays of objects had me stumped for a while.
The trick is to use
jsonb_array_elements to expand the array into a row for every object. Then each object can be queried individually by using the
->> operator extract a key and use it in a where clause.
Conceptually this isn’t much different than a one to many inner join where, because of the join, you can have a row mostly duplicated in the query output after it is joined.
So let’s say you have a user address and have an
addresses which is a JSONB column, containing multiple address objects.
The following query expands the addresses into multiple rows, then uses the
->> operator to extract the
zip field and then finds any that are equal to 78701.
from "user" u, jsonb_array_elements(u.addresses) as obj
where obj->>'zip' = '78701';
Just like with an inner join there might be a need to do a
group by to get the result you want but overall it is pretty straightforward!
Read more about the the available JSON operators and functions here.