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.