CODEWITHSUNDEEP
sqlarraysjsonpostgresqlpostgresql-11
Tack
Tack

Reputation: 121

Query JSON object within a list (Postgres 11)

I have a "contact_info" column that stores jsonb, which look like this:

[ {"phoneNumber":"123-4567", "emailAddress":"[email protected]"} ]

I'm trying to retrieve all the rows where phone number equals "123-4567".

How can I do this in a one-line query? I can return the whole column with SELECT contact_info FROM db_name.db_table; but haven't had success pulling the individual objects and querying the phone numbers inside.

I've been looking at [this question][1] but it is slightly different than my situation (list of json objects vs json object containing a list), and I can't figure out how to apply the solution to my version of the issue.

Upvotes: 0

Views: 113

Answers (2)

Barbaros Özhan
Barbaros Özhan

Reputation: 65353

You can use JSONB_ARRAY_ELEMENTS() function as

SELECT t.*
  FROM t
 CROSS JOIN JSONB_ARRAY_ELEMENTS(contact_info) AS js(elm)
 WHERE js.elm ->> 'phoneNumber' = '123-4567'

Demo

Upvotes: 1

GMB
GMB

Reputation: 222572

If all of your arrays have just one element, this is straight-forward:

select t.*, t.contact_info -> 0 ->> 'emailAddress' as email_address
from db_table t
where t.contact_info -> 0 ->> 'phoneNumber' = '123-4567'

Else, can use jsonb_array_elements() and a lateral join to unnest the array, then filter:

select t.*, x.obj ->> 'emailAddress' as email_address
from db_table t
cross join lateral jsonb_array_elements(t.contact_info) x(obj)
where x.obj ->> 'phoneNumber' = '123-4567'

Note that this returns multiple rows if more than one object in a given array matches on the phone number. If you want to avoid that, you can use a subquery:

select t.*, x.obj ->> 'emailAddress' as email_address
from db_table t
where exists (
    select 1
    from jsonb_array_elements(t.contact_info) x(obj)
    where x.obj ->> 'phoneNumber' = '123-4567'
)

Upvotes: 1

Related Questions