Give me all visitors named Jack and age > 25
Give me all visitors who are users, but where name is unspecified (key not in json)
I assume you've read the docs. The answer is you can not do this. There are two types of index classes that can work on JSONB using
jsonb_ops (the default).
CREATE INDEX idxginp ON api USING GIN (jdoc jsonb_path_ops);
CREATE INDEX idxgin ON api USING GIN (jdoc);
jsonb_ops I believe you can test for the non-existence of a value. However, in neither of these can you test for anything but arbitrary containment. From the docs on JSONB indexing
The default GIN operator class for jsonb supports queries with top-level key-exists operators ?, ?& and ?| operators and path/value-exists operator @>.
Find out what those operators do for the type in the JSON-function docs
If you need to know if Jack is older than 25. You'll have to create a btree on