Postgres jsonb 'NOT contains' operator
15,060
Solution 1
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.
Solution 2
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'))
Related videos on Youtube
Author by
user101289
Updated on September 28, 2022Comments
-
user101289 over 1 year
I'm experimenting with postgres
jsonb
column types, and so far so good. One common query I'm using is like this:select count(*) from jsonbtest WHERE attributes @> '{"City":"Mesa"}';
How do I reverse that? Is there a different operator or is it simply used as
select count(*) from jsonbtest WHERE NOT attributes @> '{"City":"Mesa"}';
-
redneb over 7 yearsNo, there is no dedicated operator for that. What's wrong with
NOT
? -
pozs almost 7 years@eykanal
NOT
works pretty well.
-
-
drice304 over 4 yearsThat would be the same as
select count(*) from jsonbtest WHERE '{"City":"Mesa"}' @> attributes;
, checking if{"City": "Mesa"}
containsattributes
which is not the same. -
A-Diddy about 3 yearsThis helped me a lot... I couldn't find anyting about on this use case on the PgSQL site. Thanks!