Aj A Aj A - 1 month ago 5
SQL Question

Not able to add a where condition after jsonb_array_elements

I have a table MessagesList with jsonb datatype column Tags. Data in the column is like ["abc","xyz"]. I need to search in the column for a value 'abc'

My query

select value from messageslist , jsonb_array_elements(messageslist.Tags) as elem


This returns

"abc"
"xyz"


I need to write a condition in this resultset, ie like

where value ='abc'

Answer

You need to use jsonb_array_elements_text() to return each tag as a text value, not a JSON value.

I also prefer to define an alias for the column as well, not only the derived table that jsonb_array_elements_text() returns:

select e.tag
from messageslist as m, 
     jsonb_array_elements_text(m.tags) as e(tag) 
where e.tag = 'abc'