Query JSON object within a list (Postgres 11)

Question

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.

Barbaros Özhan · Accepted Answer · 2020-11-30 00:13:17Z

1

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

answered Nov 30, 2020 at 0:13

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tack Over a year ago

Thanks a lot! I wasn't initially sure how this worked and having the demo helped me figure it out.

GMB · Accepted Answer · 2020-11-30 00:07:39Z

1

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'
)

answered Nov 30, 2020 at 0:07

GMB

224k25 gold badges103 silver badges151 bronze badges

1 Comment

Tack Over a year ago

Thanks for the quick response, I ended up implementing the answer below but I really appreciated your speed and breadth of answers!

Collectives™ on Stack Overflow

Query JSON object within a list (Postgres 11)

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related