Query Array of Objects in Postgres

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.

For example:

User Table Address Table
Full Name Phone Zip
John Doe 555-555-5555 78701
John Doe 555-555-5555 78613

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.

select *
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.