Postgres jsonb 'NOT contains' operator
Two way, you can test any json(b) value
- the
->>operator extract value as text. But this operation slow, if you will use the value only test - the
@>operator test any json(b) contain any json(b). This is a quick but you are not tested NOT option.
Simply and quick way:
NOT (attribute @> '{"City":"Mesa"}'::jsonb)I've change attribute->>'City' <> 'Mesa' to NOT (attribute @> '{"City":"Mesa"}'::jsonb) and my ~2.000.000 rows query result time changed 45secs to 25secs.
This can be achieved with several conditions. It's not elegant but I didn't find another way to do so.
So, first, get every row which simple don't have 'City' attribute and then add 'OR' condition to check for correct field value.
select count(*) from jsonbtest where NOT(attributes ? 'City') OR (attributes ? 'City') is NULL -- this is required if attributes can be null OR (attributes->>'City' != 'Mesa'))
You can use the operator <@ this will search where 'City' is not 'Mesa'
select count(*) from jsonbtest WHERE attributes <@ '{"City":"Mesa"}';