I have a jsonb column which have following rows
ROW1:
[ { "cpe23Uri": "cpe:2.3:a:sgi:irix:3.55:*:*:*:*:*:*:*", "active": true }, { "cpe23Uri": "cpe:2.3:a:university_of_washington:imap:10.234:*:*:*:*:*:*:*", "active": true } ]
ROW 2:
[]
ROW 3:
[ { "cpe23Uri": "cpe:2.3:o:sgi:irix:*:*:*:*:*:*:*:*", "active": true } ]
I want to find the rows which contain sgi:irix in the key cpe23Uri
Which query should i use for best performance?
Advertisement
Answer
You could use an exists
condition with a correlated subquery that uses jsonb_array_element()
to unnest and search the array:
select * from mytable t where exists ( select 1 from jsonb_array_elements(t.js) x where x->>'cpe23Uri' like '%sgi:irix%' );