Postgres jsonb 'NOT contains' operator


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


Updated on September 28, 2022


  • user101289
    user101289 2 months

    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
      redneb about 6 years
      No, there is no dedicated operator for that. What's wrong with NOT?
    • pozs
      pozs over 5 years
      @eykanal NOT works pretty well.
  • drice304
    drice304 almost 3 years
    That would be the same as select count(*) from jsonbtest WHERE '{"City":"Mesa"}' @> attributes;, checking if {"City": "Mesa"} contains attributes which is not the same.
  • A-Diddy
    A-Diddy almost 2 years
    This helped me a lot... I couldn't find anyting about on this use case on the PgSQL site. Thanks!