What's the difference between the PostgreSQL @? and @@ JSONB Operators?

Question

I've been trying to understand the difference between the PostgreSQL @? and @@ JSONB operators. Why do these return different results?

david=#  SELECT '{ "email": { "main": "[email protected]" } }' @? '$ ?(@.email.main == "[email protected]")';
 ?column? 
----------
 t

david=#  SELECT '{ "email": { "main": "[email protected]" } }' @@ '$ ?(@.email.main == "[email protected]")';
 ?column? 
----------
 f

I thought maybe they're equivalent to the jsonb_path_exists() and jsonb_path_match() functions:

david=#  SELECT jsonb_path_exists('{ "email": { "main": "[email protected]" } }', '$ ?(@.email.main == "[email protected]")');
 jsonb_path_exists 
-------------------
 t

david=#  SELECT jsonb_path_match('{ "email": { "main": "[email protected]" } }', '$ ?(@.email.main == "[email protected]")');
ERROR:  single boolean result is expected

That's a bit more informative, and jsonb_path_match() example in the docs show the use of exists(), and that does seem to work:

david=#  SELECT jsonb_path_match('{ "email": { "main": "[email protected]" } }', 'exists($ ?(@.email.main == "[email protected]"))');
 jsonb_path_match 
------------------
 t

But the same is not true of @@:

david=#  SELECT '{ "email": { "main": "[email protected]" } }' @@ 'exists($ ?(@.email.main == "[email protected]"))';
 ?column? 
----------
 f

So color me confused. I do not understand the differences here.

Answer 1

With thanks to @jjanes for pointing me in the right direction, and to the denizens of the pgsql-hackers mail list, I believe I got the differences worked out. This blog post details figuring it out, but the conclusion is this:

• Postgres supports a nonstandard syntax for SQL/JSON path queries where the entire query can be a boolean predicate, and the value returned by the query is the value returned by the predicate: true, false, or unknown (translated to SQL null).
• @? (and jsonb_path_exists()) returns true if the path query returns any values — even false or null — and false if it returns no values. This operator should be used only with SQL-standard JSON path queries that select data from the JSONB. Do not use predicate-only JSON path expressions with @?.
• @@ (and jsonb_path_match()) returns true if the path query returns the single boolean value true and false otherwise. This operator should be used only with Postgres-specific boolean predicate JSON path queries, that return data from the predicate expression. Do not use SQL-standard JSON path expressions with @@.

Answer 2

Try visualizing with this:

SELECT jsonb_path_query('{ "email": { "main": "[email protected]" } }', '$ ?(@.email.main == "[email protected]")');

The above does return something, which why is why @? is true. But what it returns is not the boolean value 'true', which is why @@ is false. And is not a boolean at all, which is why jsonb_path_match gives an error. That is my casual interpretation of it, anyway.

This is all very subtle, and I find the docs are hard to understand unless you already know what they are saying.