PostgreSQL: get value of object inside jsonb array for full text search

1
votes

How do I get a certain value based on a key inside an object that is part of my array? I store my json data as jsonb inside my Postgres 9.6 DB

addresses (JSONB)
---------
[{"address":"[email protected]", "type": "home"}, {"address":"[email protected]", "type": "work"}]

What I'd love to do is something like:

SELECT addresses ->> 'address' FROM foo

and then use the result in a full text search, where I search for a specific email-address like:

SELECT * FROM foo WHERE 
to_tsvector('simple', CAST(addresses ->>'address' as text)) @@ to_tsquery('abc:*');

All I get when I run the first query is: (NULL)

1
sqlpostgresqlfull-text-searchjsonbpostgresql-9.6

1 Answers

3
votes

You should unnest the json array using jsonb_array_elements():

with foo(addresses) as (
values
    ('[{"address":"[email protected]", "type": "home"}, {"address":"[email protected]", "type": "work"}]'::jsonb)
)

select value->>'address' as address
from foo,
jsonb_array_elements(addresses)
where to_tsvector('simple', value->>'address') @@ to_tsquery('abc:*');

   address   
-------------
 [email protected]
(1 row)