Search code examples
postgresqljsonbjsonpath

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


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.


Solution

  • 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 @@.