This my jonb data
contact:{
"name": "Jonh",
"country": ["USA", "UK"],
}
And my query:
SELECT * FROM public.product where contact -> 'country' = ARRAY['USA','UK'];
Executed the query and got this ERROR: operator does not exist: jsonb = text[]
So how do I fix this error?
You need to compare it with a JSONB array:
select *
from product
where contact -> 'country' = '["USA","UK"]'::jsonb;
But this depends on the order of the elements in the array. If you want to test all keys regardless of the order, the ?& operator might be better:
where contact -> 'country' ?& array['UK','USA']
That would however also return rows that contain additional elements in the array. If you need to match all elements exactly regardless of the order you could use the @> operator twice:
where contact -> 'country' @> '["USA","UK"]'::jsonb
and contact -> 'country' <@ '["USA","UK"]'::jsonb
Use to_jsonb():
SELECT (('{"a": ["x","y","z"]}'::jsonb)->'a') = to_jsonb(array['x','y','z']);
^^ Returns true.
