Search code examples
sqlarraysjsonpostgresqlpostgresql-11

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.

Solution

  • 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