Thanks for the reply, Erik. Have appreciated collaborating with you on a few
different things lately!
> On Oct 13, 2023, at 22:50, Erik Wienhold <e...@ewie.name> wrote:
>> Hi, finally getting back to this, still fiddling to figure out the
>> differences. From the thread you reference [1], is the point that @@
>> and jsonb_path_match() can only be properly used with a JSON Path
>> expression that’s a predicate check?
>
> I think so. That's also supported by the existing docs which only
> mention "JSON path predicate" for @@ and jsonb_path_match().
Okay, good.
>> If so, as far as I can tell, only exists() around the entire path
>> query, or the deviation from the SQL standard that allows an
>> expression to be a predicate?
>
> Looks like that. But note that exists() is also a filter expression.
> So wrapping the entire jsonpath in exists() is also a deviation from the
> SQL standard which only allows predicates in filter expressions, i.e.
> '<path> ? (<predicate>)'.
Yeah. I’m starting to get the sense that the Postgres extension of the standard
to allow predicates without filters is almost a different thing, like there are
two Pg SQL/JSON Path languages:
1. SQL Standard path language for selecting values and includes predicates.
Returns the selected value(s). Supported by `@?` and jsonb_path_exists().
2. The Postgres predicate path language which returns a boolean, akin to a
WHERE expression. Supported by `@@` and jsonb_path_match()
Both are supported by jsonb_path_query(), but if you use a standard path you
get the values and if you use a predicate path you get a boolean. This feels a
big overloaded to me, TBH; I find myself wanting them to be separate types
since the behaviors vary quite a bit!
>> This suggest to me that the "Only the first item of the result is
>> taken into account” bit from the docs may not be quite right.
>
> Yes, this was also the issue in the referenced thread[1]. I think my
> suggesstion in [2] explains it (as far as I understand it).
Yeah, lax vs. strict mode stuff definitely creates some added complexity. I see
now I missed the rest of that thread; seeing the entire thread on one page[1]
really helps. I’d like to take a stab at the doc improvements Tom suggests[2].
>> jsonb_path_match(), it turns out, only wants a single result. But
>> furthermore perhaps the use of a filter predicate rather than a
>> predicate expression for the entire path query is an error?
>
> Yes, I think @@ and jsonb_path_match() should not be used with filter
> expressions because the jsonpath returns whatever the path expression
> yields (which may be an actual boolean value in the jsonb). The filter
> expression only filters (as the name suggests) what the path expression
> yields.
Agreed. It only gets worse with a filter expression that selects a single value:
david=# select jsonb_path_match('{"a":[false,true]}', '$.a ?(@[*] == false)');
jsonb_path_match
------------------
f
Presumably it returns false because the value selected is JSON `false`:
david=# select jsonb_path_query('{"a":[false,true]}', '$.a ?(@[*] == false)');
jsonb_path_query
------------------
false
Which seems misleading, frankly. Would it be possible to update
jsonb_path_match and @@ to raise an error when the path expression is not a
predicate?
>> Curiously, @@ seems okay with it:
>>
>> david=# select '{"a":[false,true,false]}'@@ '$.a ?(@[*] == false)';
>> ?column?
>> ----------
>> t
>>
>> Not a predicate query, and somehow returns true even though the first
>> item of the result is false? Is that how it should be?
>
> Your example does a text search equivalent to:
>
> select to_tsvector('{"a":[false,true,false]}') @@ plainto_tsquery('$.a ?
> (@[*] == true)')
>
> You forgot the cast to jsonb.
Oh good grief 🤦🏻♂️
> jsonb @@ jsonpath actually returns null:
>
> test=# select '{"a":[false,true,false]}'::jsonb @@ '$.a ? (@[*] == false)';
> ?column?
> ----------
> <null>
> (1 row)
Yes, much better, though see the result above that returns a single `false` and
confuses things.
> This matches the note right after the docs for @@:
Yeah, that makes sense. But here’s a bit about lax mode[3] that confuses me:
> The lax mode facilitates matching of a JSON document structure and path
> expression if the JSON data does not conform to the expected schema. If an
> operand does not match the requirements of a particular operation, it can be
> automatically wrapped as an SQL/JSON array or unwrapped by converting its
> elements into an SQL/JSON sequence before performing this operation. Besides,
> comparison operators automatically unwrap their operands in the lax mode, so
> you can compare SQL/JSON arrays out-of-the-box.
This automatic flattening in lax mode seems odd, because it means you get
different results in strict and lax mode where there are no errors. In lax
mode, you get a set:
david=# select jsonb_path_query('{"a":[1,2,3,4,5]}', '$.a ?(@[*] > 2)');
jsonb_path_query
------------------
3
4
5
(3 rows)
But in strict mode, you get the array selected by `$.a`, which is more what I
would expect:
david=# select jsonb_path_query('{"a":[1,2,3,4,5]}', 'strict $.a ?(@[*] > 2)');
jsonb_path_query
------------------
[1, 2, 3, 4, 5]
This seems like an odd inconsistency in return values, but perhaps the standard
calls for this? I don’t have access to it, but MSSQL docs[4], at least, say:
> * In **lax** mode, the function returns empty values if the path expression
> contains an error. For example, if you request the value **$.name**, and the
> JSON text doesn't contain a **name** key, the function returns null, but does
> not raise an error.
>
> * In **strict** mode, the function raises an error if the path expression
> contains an error.
No flattening, only error suppression. The Oracle docs[5] mention array
flattening, but I don’t have it up and running to see if that means query
*results* are flattened.
Best,
David
[1]
https://www.postgresql.org/message-id/flat/CACJufxE01sxgvtG4QEvRZPzs_roggsZeVvBSGpjM5tzE5hMCLA%40mail.gmail.com
[2] https://www.postgresql.org/message-id/1229727.1680535592%40sss.pgh.pa.us
[3]
https://www.postgresql.org/docs/current/functions-json.html#STRICT-AND-LAX-MODES
[4]
https://learn.microsoft.com/en-us/sql/relational-databases/json/json-path-expressions-sql-server?view=sql-server-ver16#PATHMODE
[5]
https://docs.oracle.com/en/database/oracle/oracle-database/21/adjsn/json-path-expressions.html#GUID-8656CAB9-C293-4A99-BB62-F38F3CFC4C13
