pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Erik Wienhold <ewie@ewie.name>
To: Adrian Klaver <adrian.klaver@aklaver.com>
To: pgsql-general list <pgsql-general@lists.postgresql.org>
Subject:  Re: ​jsonb @@ jsonpath operator doc: ​Only the first item of the result is taken into account
Date: Mon, 3 Apr 2023 21:00:31 +0200 (CEST)
Message-ID: <314710945.570712.1680548431450@office.mailbox.org> (raw)
In-Reply-To: <d2bf1d05-fc29-82c6-5b7b-56f8e8459639@aklaver.com>
References: <CACJufxE01sxgvtG4QEvRZPzs_roggsZeVvBSGpjM5tzE5hMCLA@mail.gmail.com>
	<202761461.525027.1680441711777@office.mailbox.org>
	<48ed779b-c405-ca4f-c2d1-5ecc0312d9a3@aklaver.com>
	<550248534.555107.1680534677029@office.mailbox.org>
	<2f804410-376e-fc69-1829-71d4be49e39c@aklaver.com>
	<1597721260.560673.1680538908417@office.mailbox.org>
	<d2bf1d05-fc29-82c6-5b7b-56f8e8459639@aklaver.com>

> 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)

> 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=e94...

--
Erik





view thread (14+ messages)  latest in thread

Message-ID: <314710945.570712.1680548431450@office.mailbox.org>
Permalink:  ../314710945.570712.1680548431450@office.mailbox.org/
Also on:    postgresql.org/message-id/314710945.570712.1680548431450@office.mailbox.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: ewie@ewie.name, adrian.klaver@aklaver.com
  Subject: Re: ​jsonb @@ jsonpath operator doc: ​Only the first item of the result is taken into account
  In-Reply-To: <314710945.570712.1680548431450@office.mailbox.org>
  References: <CACJufxE01sxgvtG4QEvRZPzs_roggsZeVvBSGpjM5tzE5hMCLA@mail.gmail.com> <202761461.525027.1680441711777@office.mailbox.org> <48ed779b-c405-ca4f-c2d1-5ecc0312d9a3@aklaver.com> <550248534.555107.1680534677029@office.mailbox.org> <2f804410-376e-fc69-1829-71d4be49e39c@aklaver.com> <1597721260.560673.1680538908417@office.mailbox.org> <d2bf1d05-fc29-82c6-5b7b-56f8e8459639@aklaver.com> <314710945.570712.1680548431450@office.mailbox.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox