Re: JSON Path and GIN Questions

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 <ewie(at)ewie(dot)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 `(at)?` 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