How to fetch data while where condition in jsonb in Postgresql

Question

I have a table data_table like this

| id         | reciever                                     
| (bigint)   |(jsonb)                                      

----------------------------------------------------------------------
|    1       | [{"name":"ABC","email":"[email protected]"},{"name":"ABDFC","email":"[email protected]"},...]
|    2       | [{"name":"DEF","email":"[email protected]"},{"name":"AFDBC","email":"[email protected]"},...]
|    3       | [{"name":"GHI","email":"[email protected]"},{"name":"AEEBC","email":"[email protected]"},...]
|    4       | [{"name":"LMN","email":"[email protected]"},{"name":"EEABC","email":"[email protected]"},...]
|    5       | [{"name":"PKL","email":"[email protected]"},{"name":"ABREC","email":"[email protected]"},...]
|    6       | [{"name":"ANI","email":"[email protected]"},{"name":"ABWC","email":"[email protected]"},...]

when i run on pg admin it works fine

I want to fetch row by putting email in where condition like select * from data_table where receiver = '[email protected]'. there can be more data in array so i have shown "...".

I have tried like where receiver-->>'email'='[email protected]' but it is working in the case {"name":"ABC","email":"[email protected]"} only not in array where i have to chaeck every email in array

Help will be appreciated.

GMB · Accepted Answer · 2020-05-10 19:35:03Z

1

One option is to use exists and jsonb_array_elements():

select t.*
from mytable t
where exists (
    select 1
    from jsonb_array_elements(t.receiver) x(elt)
    where x.elt ->> 'email' = '[email protected]'
)

This gives you all rows where at least one element in the array has the given email.

If you want to actually exhibit the matching elements, then you can use a lateral join instead (if more than one element in the array has the given email, this duplicates the row):

select t.*, x.elt
from mytable t
cross join lateral jsonb_array_elements(t.receiver) x(elt)
where x.elt ->> email = '[email protected]'

answered May 10, 2020 at 19:35

GMB

224k25 gold badges103 silver badges151 bronze badges

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

4 Comments

bala Over a year ago

ERROR can't extract an element from object @GMB

jjanes Over a year ago

@bala if you get that error, then you example you have shown us is not accurate.

bala Over a year ago

May be i am missing one thing is that in some row when data is single then store d this {"email":"[email protected]"} i mean its not in an array @jjanes

jjanes Over a year ago

@bala, the simple answer is "don't store things that way". You could work around it, but it will be an never ending nightmare.

Collectives™ on Stack Overflow

How to fetch data while where condition in jsonb in Postgresql

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related