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