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": "hi@example.net" } }' @? '$ ?(@.email.main == "hi@example.net")';
 ?column? 
----------
 t

david=#  SELECT '{ "email": { "main": "hi@example.net" } }' @@ '$ ?(@.email.main == "hi@example.net")';
 ?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": "hi@example.net" } }', '$ ?(@.email.main == "hi@example.net")');
 jsonb_path_exists 
-------------------
 t

david=#  SELECT jsonb_path_match('{ "email": { "main": "hi@example.net" } }', '$ ?(@.email.main == "hi@example.net")');
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": "hi@example.net" } }', 'exists($ ?(@.email.main == "hi@example.net"))');
 jsonb_path_match 
------------------
 t

But the same is not true of @@:

david=#  SELECT '{ "email": { "main": "hi@example.net" } }' @@ 'exists($ ?(@.email.main == "hi@example.net"))';
 ?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: