Postgres jsonb 'NOT contains' operator

Question

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"}';

No, there is no dedicated operator for that. What's wrong with `NOT`? — redneb, Sep 28 '16 at 00:06
@redneb The problem is that `NOT` simply doesn't work. Using the `attributes->>'City' <> 'Mesa'` formulation also doesn't work. — eykanal, Mar 30 '17 at 02:09
@eykanal `NOT` [works pretty well](http://rextester.com/TMGVQZ95259). — pozs, Apr 28 '17 at 11:50

score 30 · Accepted Answer · edited Jan 05 '19 at 13:02

30

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.

edited Jan 05 '19 at 13:02

answered Jan 05 '19 at 10:28

Ahmet Erkan ÇELİK

2,364
1
26
28

The frustrating thing is that if you've added a nice `USING GIN(attribute)` index, negating the @> contains operator suddenly stops using that index. – nessur Feb 06 '20 at 18:38
1

NOT doesn't covers situation when you have attribute but this attribute equals NULL – Dmitry Chirkin Mar 01 '21 at 08:25
@DmitryChirkin simpe test. null valued object: SELECT NOT('{"City":null}'::jsonb @> '{"City":"Mesa"}'::jsonb); returns as true alternatively test. not contain attribute: SELECT NOT('{"Another Attribute":"Mesa"}'::jsonb @> '{"City":"Mesa"}'::jsonb) returns true i think there is no problem – Ahmet Erkan ÇELİK Jan 27 '23 at 06:45
@nessur You can create the GIN index with jsonb_ops. e.g: CREATE INDEX idx_my_index_jsonb_data_with_ops ON my_table USING GIN (attribut jsonb_ops); You can use this link for options: https://www.postgresql.org/docs/current/gin-builtin-opclasses.html – Ahmet Erkan ÇELİK Jan 27 '23 at 06:50

Dmitry Chirkin · Answer 2 · 2017-04-28T10:33:01.980

12

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'))

edited Apr 28 '17 at 10:33

answered Apr 26 '17 at 10:44

Dmitry Chirkin

994
8
23

1

This helped me a lot... I couldn't find anyting about on this use case on the PgSQL site. Thanks! – A-Diddy Feb 26 '21 at 22:29

score 2 · Answer 3 · edited Sep 24 '22 at 20:08

2

---contains specific key (City), does not contain value ('"Mesa"').

SELECT count(*) FROM jsonbtest  WHERE NOT (attributes  -> 'City'  @> '"Mesa"');

---does not contain specific key value pair: {"City":"Mesa"}

SELECT count(*) FROM jsonbtest  WHERE  NOT (attributes  @> '{"City":"Mesa"}')

Since attributes key can be others except City. So these two queries are different!

edited Sep 24 '22 at 20:08

iconoclast

21,213
15
102
138

answered Sep 29 '21 at 13:29

jian

4,119
1
17
32

score -3 · Answer 4 · answered Jan 12 '18 at 17:07

-3

You can use the operator <@ this will search where 'City' is not 'Mesa'

select count(*) from jsonbtest WHERE attributes <@ '{"City":"Mesa"}';

answered Jan 12 '18 at 17:07

Diego Llamas Velasco

225
1
4
9

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. – drice304 Dec 17 '19 at 21:08

Postgres jsonb 'NOT contains' operator

4 Answers4