Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pjPQ3-00064I-6z for pgsql-general@arkaria.postgresql.org; Mon, 03 Apr 2023 19:00:43 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pjPQ1-00032s-OU for pgsql-general@arkaria.postgresql.org; Mon, 03 Apr 2023 19:00:41 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pjPQ1-00032I-Dj for pgsql-general@lists.postgresql.org; Mon, 03 Apr 2023 19:00:41 +0000 Received: from mout-u-204.mailbox.org ([2001:67c:2050:101:465::204]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1pjPPw-000gQt-Uq for pgsql-general@lists.postgresql.org; Mon, 03 Apr 2023 19:00:40 +0000 Received: from smtp2.mailbox.org (smtp2.mailbox.org [IPv6:2001:67c:2050:b231:465::2]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange ECDHE (P-384) server-signature RSA-PSS (4096 bits) server-digest SHA256) (No client certificate requested) by mout-u-204.mailbox.org (Postfix) with ESMTPS id 4Pr0b40Jjqz9sM6; Mon, 3 Apr 2023 21:00:32 +0200 (CEST) Date: Mon, 3 Apr 2023 21:00:31 +0200 (CEST) From: Erik Wienhold To: Adrian Klaver , pgsql-general list Message-ID: <314710945.570712.1680548431450@office.mailbox.org> In-Reply-To: References: <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> Subject: =?UTF-8?Q?Re:_=E2=80=8Bjsonb_@@_jsonpath_operator_doc:_=E2=80=8BOnly_the?= =?UTF-8?Q?_first_item_of_the_result_is_taken_into_account?= MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable X-Priority: 3 Importance: Normal X-Rspamd-Queue-Id: 4Pr0b40Jjqz9sM6 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk > On 03/04/2023 18:37 CEST Adrian Klaver wrote: > > On 4/3/23 09:21, Erik Wienhold wrote: > >> On 03/04/2023 17:36 CEST Adrian Klaver wro= te: > >> > >> On 4/3/23 08:11, Erik Wienhold wrote: > >>>> On 02/04/2023 17:40 CEST Adrian Klaver w= rote: > >>>> > >>>> That is a long way from: > >>>> > >>>> jsonb @@ jsonpath =E2=86=92 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 t= he > >>>> result is not Boolean, then NULL is returned. > >>> > >>> What do you mean? I responded to the OP's question. It's not a sugg= estion > >>> 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. I= f > >> 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 red= undant > > (and causing the OP's confusion) because the path predicate produces on= ly a > > single item: true, false, or null. That's what I wanted to show with t= he 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[*] ? (@ =3D=3D "test")' ; > ?column? > ---------- > f The filter expression does not match any values because predicate '@ =3D=3D= "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 th= e 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 p= ath evaluation steps use only those items for which the filter expression retu= rned true." https://www.postgresql.org/docs/current/functions-json.html > select '{"a":[1,2,3,4,5]}'::jsonb @@ '$.a[*] =3D=3D "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 pred= icate: =09select '{"a":[1,2,3,4,5,"test"]}'::jsonb @@ '$.a[*] =3D=3D "test"'; =09 ?column? =09---------- =09 t =09(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: =09select '{"a":[1,2,3,4,5,"test"]}'::jsonb @@ 'strict $.a[*] =3D=3D "test"= '; =09 ?column? =09---------- =09 NULL =09(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: =09The operators support, among the others, "jsonb @? jsonpath" and =09"jsonb @@ jsonpath". Expressions containing these operators are easily =09expressed through each other. =09=09jb @? 'path' <=3D> jb @@ 'EXISTS(path)' =09=09jb @@ 'expr' <=3D> jb @? '$ ? (expr)' https://git.postgresql.org/gitweb/?p=3Dpostgresql.git;a=3Dblob;f=3Dsrc/back= end/utils/adt/jsonb_gin.c;h=3De941439d7493365f8954c791f0e2368c080189b8;hb= =3DHEAD#l15 -- Erik