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

0
votes

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 ?

1
sqlarrayspostgresqljsonb
worker_ids array contains 1, so why are you expecting a result? - 404
Are the other worker_ids fields NULL? - 404
comparison against null gives null, not true or false, so not null ? value is not false and thus not returned - Vao Tsun
@eurotrash true :) I thought it is obvious because asking why no rows returned for false would be strange when you know they all are true. but yes, you are right - it's logical, not obvious - Vao Tsun

1 Answers

2
votes

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