CODEWITHSUNDEEP
sqlarrayspostgresqljsonb
Aliaksei Stadnik
Aliaksei Stadnik

Reputation: 1968

Postgres check if jsonb array not contains value returns empty result set

Need to check if jsonb column contains and not contains value. Jsonb field is a simple array:

ALTER TABLE task ADD COLUMN worker_ids jsonb;

UPDATE task SET worker_ids = '["1", "2"]' WHERE ...

Then trying to run queries to check either it contains or not contains concrete value:

Query which checks if jsonb contains value - is working fine and returns correct set of rows 

 SELECT * FROM task WHERE worker_ids ? '1'

However when I add NOT to the where condition query returns simply nothing:

 SELECT * FROM task WHERE NOT worker_ids ? '1'

Am I getting something wrong ?

Upvotes: 0

Views: 2803

Answers (1)

Vao Tsun
Vao Tsun

Reputation: 51659

https://www.postgresql.org/docs/current/static/functions-comparison.html

Ordinary comparison operators yield null (signifying “unknown”), not true or false, when either input is null. For example, 7 = NULL yields null, as does 7 <> NULL. When this behavior is not suitable, use the IS [ NOT ] DISTINCT FROM predicates:

something like:

SELECT * FROM task WHERE (worker_ids ? '1') is distinct from true

should work

Upvotes: 3

Related Questions