Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1ieDTr-0003qi-T3 for pgsql-bugs@arkaria.postgresql.org; Mon, 09 Dec 2019 07:29:21 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1ieDTo-0006lq-J3 for pgsql-bugs@arkaria.postgresql.org; Mon, 09 Dec 2019 07:29:16 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1ieDTm-0006lj-Nm for pgsql-bugs@lists.postgresql.org; Mon, 09 Dec 2019 07:29:16 +0000 Received: from out2-smtp.messagingengine.com ([66.111.4.26]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1ieDTf-0000F6-6G for pgsql-bugs@lists.postgresql.org; Mon, 09 Dec 2019 07:29:14 +0000 Received: from compute4.internal (compute4.nyi.internal [10.202.2.44]) by mailout.nyi.internal (Postfix) with ESMTP id 70078229C2; Mon, 9 Dec 2019 02:29:03 -0500 (EST) Received: from mailfrontend2 ([10.202.2.163]) by compute4.internal (MEProxy); Mon, 09 Dec 2019 02:29:03 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=paquier.xyz; h= date:from:to:cc:subject:message-id:references:mime-version :content-type:in-reply-to; s=fm3; bh=F6ZY9YJlqip0efBlcX1g8Jw5Pud xYabN+ylLhzZ1DhA=; b=OPz2XIA/bKSk5qBGkCj6xaxwxnc080ilk0nLE8fg4rz yjUf6GZPV7wXAD+R6olAc2TuSSMUYIOTRNAM0QQpsVUn8VWdNAjaa8kk08N3mY2h bY+lhTV10k6cXyzt0u8U+Dr47XK+FFJdlTCSynr5XSMeXYHveh1F6WmI/yh0VLYg PbVRtmt386owwHKwLsrmnOuspfGwYBIiTM6nvAJRA/wEVYR3LoWDEZEFSefslZwu n7u2uqniuuzByg9PARcGxyXcmocUGuOet2buaN8GbCmDj2aKMLMSnbZucDUEwR3q iaX729MCPB5twx/QKMgRR6ow84gUqt7byM7OxZia2Ng== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:content-type:date:from:in-reply-to :message-id:mime-version:references:subject:to:x-me-proxy :x-me-proxy:x-me-sender:x-me-sender:x-sasl-enc; s=fm1; bh=F6ZY9Y Jlqip0efBlcX1g8Jw5PudxYabN+ylLhzZ1DhA=; b=cXtbra6aJn6/mFqpD32iAC ueL9w8tvsRKX+5pQ2XWNB6n28lqIbu3pGeVwgkypzvIraxwcyzfgPRVUasAh8xll c45VGzbn3gHah8AybW1b+ptOK2BbaT1vW+3AaSCdXoNq24CLMo5rsF/d+WQLgKja rL4jA9vDOAuS2p/F1g8Mk+9SjGabDMaUT6XCX/cLsjMAOBaPS2sZegMxUvjphCxO oUuUfghf5UPuaXsywGzbkPaFOw5KSCZoIT+S8jkXujjwjiG5EcDnPKAq7en1/COf /NtgvmDFwVy1PjeFA9vDS+x3RPHaWSWUPJoAtusxjS7/O4SFvFav2TBWq2bcNRoQ == X-ME-Sender: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedufedrudekledguddthecutefuodetggdotefrod ftvfcurfhrohhfihhlvgemucfhrghsthforghilhdpqfgfvfdpuffrtefokffrpgfnqfgh necuuegrihhlohhuthemuceftddtnecusecvtfgvtghiphhivghnthhsucdlqddutddtmd enfghrlhcuvffnffculdejtddmnecujfgurhepfffhvffukfhfgggtuggjsehgtderredt tdejnecuhfhrohhmpefoihgthhgrvghlucfrrghquhhivghruceomhhitghhrggvlhesph grqhhuihgvrhdrgiihiieqnecukfhppeduuddurddutddvrddukedtrddukeehnecurfgr rhgrmhepmhgrihhlfhhrohhmpehmihgthhgrvghlsehprghquhhivghrrdighiiinecuve hluhhsthgvrhfuihiivgeptd X-ME-Proxy: Received: from paquier.xyz (ee0822lan1.rev.em-net.ne.jp [111.102.180.185]) by mail.messagingengine.com (Postfix) with ESMTPA id 9206530600E0; Mon, 9 Dec 2019 02:29:01 -0500 (EST) Date: Mon, 9 Dec 2019 16:28:56 +0900 From: Michael Paquier To: Jan =?utf-8?Q?Przemys=C5=82aw_W=C3=B3jcik?= Cc: pgsql-bugs@lists.postgresql.org, Nikita Glukhov , Alexander Korotkov Subject: Re: Unexpected extra row from jsonb_path_query() with a recursive path Message-ID: <20191209072856.GA71819@paquier.xyz> References: MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha512; protocol="application/pgp-signature"; boundary="+HP7ph2BbKc20aGI" Content-Disposition: inline In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --+HP7ph2BbKc20aGI Content-Type: text/plain; charset=utf-8 Content-Disposition: inline Content-Transfer-Encoding: quoted-printable (Adding Alexander and Nikita in CC.) On Sun, Dec 08, 2019 at 12:43:18PM +0100, Jan Przemys=C5=82aw W=C3=B3jcik w= rote: > select jsonb_path_query('{"data": [{"key": "value"}]}', '$.**.key') >=20 > Actual output: >=20 > jsonb_path_query > ------------------ > "value" > "value" > (2 rows) >=20 > Expected output: >=20 > jsonb_path_query > ------------------ > "value" > (1 row) So... .** means that it processes all the levels of the JSON hierarchy and that it returns all the member values, including all the members of an array as well as the array itself. Hence, based on your previous example we have that: =3D# select jsonb_path_query('{"data": [{"key": "value"}]}', '$.**'); jsonb_path_query ------------------------------ {"data": [{"key": "value"}]} [{"key": "value"}] {"key": "value"} "value" (4 rows) that's what I would expect here with both the array and each one of its elements to be selected. Hence the question of Jan would be I guess the following: should the value of first element of the array be selected or not in this case? I am not completely sure if that's the wanted behavior or not, but I would expect {"key": "value"} to be the only part selected, and only one value to be returned, so this smells like a bug. Jan, is that what you meant?=20 Alexander, Nikita, any opinions to offer? -- Michael --+HP7ph2BbKc20aGI Content-Type: application/pgp-signature; name="signature.asc" -----BEGIN PGP SIGNATURE----- iQIzBAABCgAdFiEEG72nH6vTowiyblFKnvQgOdbyQH0FAl3t97gACgkQnvQgOdby QH1gjQ//TrygZBXmrtb2nv40wgyuQW47HZZNQOy5wZWY+/dU1oqrRaeiUMeOejM+ 4EJ4UGlcmK9Yrqbg03vEl9BeMR0rdCkm2g/IouHpaItVK6h1iKEHX9I3fJ+sXqO7 n5/IGl0CH9d7OiAgNwVmXM08qHGzrZYSDRJVciSmc1QEy7FEHuLxPrEbKhohJrdT UYC141JjWpM8rbiKpN3yTbkKK6nVKTfa5C76Vbypm8G5QqUQEHVXm0eOtaAdiH1p SYJA+R47t7uNFjO7165wIAhR7S+6zaTgtz/7znu77wICTHJuQD9+fUHoBxWZjO+h qrzi/mejfJEXivF4B2WJTTaH38CGNOxTyZUt2W9yUaFnxfyugyHiBbC4qJb2AT2R LyxbXBN5WM+6DgRJgFR5/BaMDCveB2uQ7Oh3KGzyKeKHfPBwPZhhhGnBrBz162wc w0vJV7I8QJ1Ay94Duwsw27OhhCn7MBjGOq6JBXSXFUTHFVknDtPpz2YFkLWAFxHW gQs1d6iBv271zBVGiPeZR/6w/d9b/NPyC3qw9BruPITUDxHxVEtekfF9mDpg+p76 bupfUUZ/kF2ACrtwQX5qJk4uH67zg3lZ4AE+SqjHUqiWVnE0DVVVpsgqKJQVsNoc 89LeW6Hnd5eb7bmty2QaqThAbFZrWX8JdOWT0Jt1tj6ZtS8totg= =/+4K -----END PGP SIGNATURE----- --+HP7ph2BbKc20aGI--