CODEWITHSUNDEEP
sqlpostgresql
Alex
Alex

Reputation: 134

How to compare an array from a json object with a normal array?

I want to do something like this

SELECT t.*
FROM table t
WHERE json_array_elements(t.data->'other_field'->my_array) && ARRAY['some_values']

But I can't, due to this error ERROR: set-returning functions are not allowed in WHERE I searched a lot for a solution without using other joins or stuff like that. So how can I do something like this in query as less complex as it can be?

Upvotes: 0

Views: 35

Answers (1)

user330315
user330315

Reputation:

If the array elements are strings, you can use the ?| operator. But that only works with jsonb values. As your column seems to be a json you need to cast it:

select *
from the_table t
where (t.data::jsonb -> 'other_field' -> 'my_array') ?| array['..', '..'];

Upvotes: 1

Related Questions