Postgres jsonb 'NOT contains' operator

postgresql postgresql-9.4 jsonb
user101289 picture user101289 · Sep 28, 2016 · Viewed 9.9k times · Source

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

Answer

Ahmet Erkan ÇELİK picture Ahmet Erkan ÇELİK · Jan 5, 2019

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.

Related questions

How to perform update operations on columns of type JSONB in Postgres 9.4

Looking through the documentation for the Postgres 9.4 datatype JSONB, it is not immediately obvious to me how to do updates on JSONB columns. Documentation for JSONB types and functions: http://www.postgresql.org/docs/9.4/static/functions-json.html http://www.postgresql.…

Read More
Merging Concatenating JSON(B) columns in query

Using Postgres 9.4, I am looking for a way to merge two (or more) json or jsonb columns in a query. Consider the following table as an example: id | json1 | json2 ---------------------------------------- 1 | {'a':'b'} | {'c':'d'} 2 | {'a1':'b2'} | {'f':{…

Read More
How to convert PostgreSQL 9.4's jsonb type to float

I'm trying the following query: SELECT (json_data->'position'->'lat') + 1.0 AS lat FROM updates LIMIT 5; (The +1.0 is just there to force conversion to float. My actual queries are far more complex, this query is just a test case …

Read More