selecting row if value of attribute in array of objects one of multiple values-CodePudding

I have data shaped like this: arrays of objects in a jsonb column in postgres

id	data
1	[{"a":3, "b":"green"} ,{"a":5, "b":"blue"}]
2	[{"a":3, "b":"red"} ,{"a":5, "b":"yellow"}]
3	[{"a":3, "b":"orange"} ,{"a":5, "b":"blue"}]

I am trying to select the rows where b is either "green" or "yellow"

I know I can unroll the data using jsonb_array_elements to get all the b values

select jsonb_array_elements(data) ->> 'b' from table

but I am failing to use that in a where query like this

 select * from table where jsonb_array_elements(data) ->> 'b' && ARRAY["green","yellow"]::varchar[]

(not working "set-returning functions are not allowed in WHERE")

CodePudding user response：

You can use the @> operator

select *
from the_table
where data @> '[{"b": "green"}]'
   or data @> '[{"b": "yellow"}]'

Or a JSON path expression:

select *
from the_table
where data @@ '$[*].b == "green" || $[*].b == "yellow"';

Or by unnesting the array with an EXISTS condition:

select t.*
from the_table t
where exists (select *
              from jsonb_array_elements(t.data) as x(item)
              where x.item ->> 'b' in ('green', 'yellow'))

CodePudding user response：

You can try to use subquery with a column alias name and ANY like below

SELECT *
FROM (
  select *,jsonb_array_elements(data) ->> 'b' val 
  from t
) t1
WHERE t1.val = ANY (ARRAY['green','yellow'])

sqlfiddle

NOTE

ARRAY filter value need to use single quote instead of double quote