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
– redneb, Commented Sep 28, 2016 at 0:06
@redneb The problem is that NOT simply doesn't work. Using the attributes->>'City' <> 'Mesa' formulation also doesn't work. — eykanal
– eykanal, Commented Mar 30, 2017 at 2:09

Ahmet Erkan ÇELİK · Accepted Answer · 2019-01-05 13:02:05Z

36

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 5, 2019 at 13:02

user330315

answered Jan 5, 2019 at 10:28

Ahmet Erkan ÇELİK

2,4521 gold badge27 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

nessur Over a year ago

The frustrating thing is that if you've added a nice USING GIN(attribute) index, negating the @> contains operator suddenly stops using that index.

Dmitry Chirkin Over a year ago

NOT doesn't covers situation when you have attribute but this attribute equals NULL

Ahmet Erkan ÇELİK Over a year ago

@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 Over a year ago

@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: postgresql.org/docs/current/gin-builtin-opclasses.html

Dmitry Chirkin · Accepted Answer · 2017-04-28 10:33:01Z

13

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, 2017 at 10:33

answered Apr 26, 2017 at 10:44

Dmitry Chirkin

1,08410 silver badges23 bronze badges

1 Comment

A-Diddy Over a year ago

This helped me a lot... I couldn't find anyting about on this use case on the PgSQL site. Thanks!

iconoclast · Accepted Answer · 2022-09-24 20:08:21Z

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, 2022 at 20:08

iconoclast

23k17 gold badges112 silver badges160 bronze badges

answered Sep 29, 2021 at 13:29

jian

5,0231 gold badge29 silver badges47 bronze badges

Comments

chesterbr · Accepted Answer · 2024-04-08 13:32:33Z

I had problem close to this one (NOT actually not reverting the JSON expression), but in my case I was looking for the key-value inside a hierarchy, therefore it was a composition of -> and @> (maybe OP's original problem got simplified when posting).

On such a case, we must consider that (per postgres docs):

The field/element/path extraction operators return NULL, rather than failing, if the JSON input does not have the right structure to match the request; for example if no such key or array element exists.

For example, if one is looking for the key/value pair nested into some hierarchy, but part of that hierarchy doesn't exist, the expression will be NULL instead of false, and NOT NULL is NULL, not true.

A catch-all solution is to wrap the original expression in NOT COALESCE(<original expression>, false).

Diego Llamas Velasco · Accepted Answer · 2018-01-12 17:07:12Z

-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, 2018 at 17:07

Diego Llamas Velasco

2671 gold badge4 silver badges9 bronze badges

1 Comment

drice304 Over a year ago

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.

Collectives™ on Stack Overflow

Postgres jsonb 'NOT contains' operator

5 Answers 5

4 Comments

1 Comment

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

4 Comments

1 Comment

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related