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

Question

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 ?

worker_ids array contains 1, so why are you expecting a result? — 404
– 404, Commented Apr 20, 2018 at 11:23
comparison against null gives null, not true or false, so not null ? value is not false and thus not returned — Vao Tsun
– Vao Tsun, Commented Apr 20, 2018 at 11:38
@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
– Vao Tsun, Commented Apr 20, 2018 at 11:40

Vao Tsun · Accepted Answer · 2018-04-20 11:45:37Z

3

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

answered Apr 20, 2018 at 11:45

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related