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

Question

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)

klin · Accepted Answer · 2017-05-24 18:25:50Z

4

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)

answered May 24, 2017 at 18:25

klin

123k15 gold badges240 silver badges262 bronze badges

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related