Better/shorter way to check inexistence of JSONB key

Question

Where data is JSONB column, I want to check if the key exists, normally I use:

SELECT id FROM users WHERE length(data->>'fingerprint_id') IS NULL;

Is there any better/shorter way to do this? since other alternative give incorrect result:

> SELECT id FROM users WHERE data->>'fingerprint_id' IS NULL;
ERROR:  operator does not exist: jsonb ->> boolean
LINE 1: SELECT id FROM users WHERE data->>'fingerprint_id' IS NULL;
                                       ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

> SELECT id FROM users WHERE data->>'fingerprint_id' = '';
 id 
----
(0 rows)

score 1 · Answer 1 · answered Jan 22 '15 at 06:17

1

Apparently, I just need to enclose the query with () before IS NULL

SELECT id FROM users WHERE (data->>'fingerprint_id') IS NULL;

answered Jan 22 '15 at 06:17

Kokizzu

24,974
37
137
233

1

Correct, it's because the binding strength of `IS NULL` is stronger than `->>`. – Craig Ringer Jan 22 '15 at 09:51

score 1 · Accepted Answer · edited May 23 '17 at 10:26

There is an explicit operator (?) for this purpose:

where data_jsonb ? 'key'

But be aware, that this might cause some DB abstraction layers (f.ex. JDBC) to falsely recognize ? as an input parameter's placeholder.

As a workaround, you could use the jsonb_exists(json, text) function directly (but your code will then depend on an undocumented function), or define a similar operator for this, like in this answer.

More details about the (data ->> 'key') IS NULL syntax can be found here.

Better/shorter way to check inexistence of JSONB key

2 Answers2