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 1pjMwT-0005i2-5J for pgsql-general@arkaria.postgresql.org; Mon, 03 Apr 2023 16:22:01 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pjMwP-0006EQ-Tn for pgsql-general@arkaria.postgresql.org; Mon, 03 Apr 2023 16:21:57 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pjMwP-0006EH-Dw for pgsql-general@lists.postgresql.org; Mon, 03 Apr 2023 16:21:57 +0000 Received: from mout-u-107.mailbox.org ([2001:67c:2050:101:465::107]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pjMwM-0005WT-Ul for pgsql-general@lists.postgresql.org; Mon, 03 Apr 2023 16:21:56 +0000 Received: from smtp1.mailbox.org (unknown [10.196.197.1]) (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-107.mailbox.org (Postfix) with ESMTPS id 4Pqx3x2JBcz9sSL; Mon, 3 Apr 2023 18:21:49 +0200 (CEST) Date: Mon, 3 Apr 2023 18:21:48 +0200 (CEST) From: Erik Wienhold To: Adrian Klaver , pgsql-general list Message-ID: <1597721260.560673.1680538908417@office.mailbox.org> In-Reply-To: <2f804410-376e-fc69-1829-71d4be49e39c@aklaver.com> 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> 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 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk > On 03/04/2023 17:36 CEST Adrian Klaver wrote: > > On 4/3/23 08:11, Erik Wienhold wrote: > >> On 02/04/2023 17:40 CEST Adrian Klaver wro= te: > >> > >> That is a long way from: > >> > >> jsonb @@ jsonpath =E2=86=92 boolean > >> > >> Returns the result of a JSON path predicate check for the specified JS= ON > >> 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 sugges= tion > > to update the docs. Obviously it's quite a mouthful and needs to be bo= iled > > 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 redunda= nt (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 f= irst two jsonb_path_query examples in my initial response, where the second exam= ple 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." -- Erik