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

From: Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
To: Erik Wienhold <ewie(at)ewie(dot)name>, pgsql-general list <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Re: ​jsonb @@ jsonpath operator doc: ​Only the first item of the result is taken into account
Date: 2023-04-03 16:37:27
Message-ID: d2bf1d05-fc29-82c6-5b7b-56f8e8459639@aklaver.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On 4/3/23 09:21, Erik Wienhold wrote:
>> On 03/04/2023 17:36 CEST Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> wrote:
>>
>> On 4/3/23 08:11, Erik Wienhold wrote:
>>>> On 02/04/2023 17:40 CEST Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)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

select '{"a":[1,2,3,4,5]}'::jsonb @@ '$.a[*] == "test"' ;
?column?
----------
NULL

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

>
> --
> Erik

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Erik Wienhold 2023-04-03 19:00:31 Re: ​jsonb @@ jsonpath operator doc: ​Only the first item of the result is taken into account
Previous Message Erik Wienhold 2023-04-03 16:21:48 Re: ​jsonb @@ jsonpath operator doc: ​Only the first item of the result is taken into account