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