What are the jsonb ? operators and jsonpath queries used for?
The ? operator tests whether a key or string element exists at the top level; ?| and ?& test any or all of several keys. jsonpath (@? and @@ operators, jsonb_path_query) lets you filter inside nested structures with an expression.
Explain it simply
Sometimes you do not know the value, only that a key should exist ("does this profile have a phone?"). The ? family answers that. For deeper questions like "any item with qty above 3?", jsonpath works like a small query language written as a string, similar to XPath for XML.
Example
-- does the key exist?
SELECT '{"name":"Asha","phone":"98xxxx"}'::jsonb ? 'phone'; -- true
SELECT '{"a":1}'::jsonb ?| array['a','b']; -- true (any)
SELECT '{"a":1}'::jsonb ?& array['a','b']; -- false (all)
-- jsonpath: any item with qty greater than 3?
SELECT doc @? '$.items[*] ? (@.qty > 3)' FROM orders;
-- boolean predicate form
SELECT doc @@ '$.items[*].qty > 3' FROM orders;
-- extract the matching elements
SELECT jsonb_path_query(doc, '$.items[*] ? (@.qty > 3)') FROM orders;The ? operator is a problem inside some drivers because ? is also used as a parameter placeholder; there you use the function jsonb_exists or escape it. GIN indexes support ?, ?|, ?&, and @?/@@ (jsonpath operators work with the default jsonb_ops class).
What to say in the interview
"? checks key existence at the top level, ?| and ?& check any or all keys. For nested conditions I use jsonpath with @? or jsonb_path_query. A GIN index supports these operators."
Common mistakes
- Using ? in a driver that treats it as a placeholder.