Re: ​jsonb @@ jsonpath operator doc: ​Only the first item of the result is taken into account

On 4/3/23 12:00, Erik Wienhold wrote:
>> On 03/04/2023 18:37 CEST Adrian Klaver <adrian.klaver@aklaver.com> wrote:
>>
>> On 4/3/23 09:21, Erik Wienhold wrote:
>>>> On 03/04/2023 17:36 CEST Adrian Klaver <adrian.klaver@aklaver.com> wrote:
>>>>
>>>> On 4/3/23 08:11, Erik Wienhold wrote:
>>>>>> On 02/04/2023 17:40 CEST Adrian Klaver <adrian.klaver@aklaver.com> wrote:
>>>>>>
>>>>>> That is a long way from:
>>>>>>
>>>>>> jsonb @@ jsonpath → boolean
>>>>>>
>>>>>> Returns the result of a JSON path predicate check for the specified JSON
>>>>>> value. Only the first item of the result is taken into account. If the
>>>>>> result is not Boolean, then NULL is returned.
>>>>>
>>>>> What do you mean?  I responded to the OP's question.  It's not a suggestion
>>>>> to update the docs.  Obviously it's quite a mouthful and needs to be boiled
>>>>> down for the docs.  Any suggestions?
>>>>
>>>> For me I don't see how:
>>>>
>>>> Predicates have existence semantics, because their operands are item
>>>> sequences.  Pairs of items from the left and right operand's sequences
>>>> are checked.  TRUE returned only if any pair satisfying the condition is
>>>> found. In strict mode, even if the desired pair has already been found,
>>>> all pairs still need to be examined to check the absence of errors.  If
>>>> any error occurs, UNKNOWN (analogous to SQL NULL) is returned.
>>>>
>>>> resolves to :
>>>>
>>>> Only the first item of the result is taken into account.
>>>>
>>>> In other words reconciling "TRUE returned only if any pair satisfying
>>>> the condition is found."  and "...first item of the result..."
>>>
>>> I see.
>>>
>>> Thinking about it now, I believe that "first item of the result" is redundant
>>> (and causing the OP's confusion) because the path predicate produces only a
>>> single item: true, false, or null.  That's what I wanted to show with the first
>>> two jsonb_path_query examples in my initial response, where the second example
>>> returns multiple items.
>>>
>>> I think the gist of @@ and json_path_match is:
>>>
>>> "Returns true if any JSON value at the given path matches the predicate.
>>>    Returns NULL when not a path predicate or comparing different types."
>>
>> So basically a variation of jsonb @? jsonpath that returns NULL instead
>> of false when confused:
>>
>> select '{"a":[1,2,3,4,5]}'::jsonb @? '$.a[*] ? (@ == "test")' ;
>> ?column?
>> ----------
>>    f
> 
> The filter expression does not match any values because predicate '@ == "test"'
> returns unknown.  This follows SQL's three-valued logic.
> 
> "    ? (condition)
> 
>   [...] The result of that step is filtered to include only those items that
>   satisfy the provided condition. SQL/JSON defines three-valued logic, so the
>   condition can be true, false, or unknown. The unknown value plays the same role
>   as SQL NULL and can be tested for with the is unknown predicate. Further path
>   evaluation steps use only those items for which the filter expression returned
>   true."    https://www.postgresql.org/docs/current/functions-json.html
> 
>> select '{"a":[1,2,3,4,5]}'::jsonb @@ '$.a[*] == "test"' ;
>>    ?column?
>> ----------
>>    NULL
> 
> In this case @@ returns null because the predicate returns unknown for all array
> elements.  It gets interesting in strict mode.
> 
> Lax mode (default) with an array element of matching type found by the predicate:
> 
>     select '{"a":[1,2,3,4,5,"test"]}'::jsonb @@ '$.a[*] == "test"';
>      ?column?
>     ----------
>      t
>     (1 row)
> 
> In strict mode the unknown result for the first array element causes the
> predicate evaluation to short-circuit and return unknown right away instead
> of testing the remaining elements:
> 
>     select '{"a":[1,2,3,4,5,"test"]}'::jsonb @@ 'strict $.a[*] == "test"';
>      ?column?
>     ----------
>      NULL
>     (1 row)

So from here:

https://www.postgresql.org/docs/current/functions-json.html

"
Note

The jsonpath operators @? and @@ suppress the following errors: missing 
object field or array element, unexpected JSON item type, datetime and 
numeric errors. The jsonpath-related functions described below can also 
be told to suppress these types of errors. This behavior might be 
helpful when searching JSON document collections of varying structure.
"

Needs to be updated to indicate that the above is true when in the 
default lax mode, but changes if strict mode is specified.


> 
>> Otherwise it does the same thing:
>>
>> select '{"a":[1,2,3,4,5]}'::jsonb @? '$.a[*] ? (@ > 2)' ;
>>    ?column?
>> ----------
>>    t
>>
>>    select '{"a":[1,2,3,4,5]}'::jsonb @@ '$.a[*] > 2' ;
>>    ?column?
>> ----------
>>    t
> 
> Yes, if the LHS and RHS types match.  The equivalence is also supported by
> documentation in src/backend/utils/adt/jsonb_gin.c:
> 
>     The operators support, among the others, "jsonb @? jsonpath" and
>     "jsonb @@ jsonpath".  Expressions containing these operators are easily
>     expressed through each other.
> 
>         jb @? 'path' <=> jb @@ 'EXISTS(path)'
>         jb @@ 'expr' <=> jb @? '$ ? (expr)'
> 
>
https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/utils/adt/jsonb_gin.c;h=e941439d7493365f8954c791f0e2368c080189b8;hb=HEAD#l15
> 
> --
> Erik

-- 
Adrian Klaver
adrian.klaver@aklaver.com