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 1pizoe-0008E9-QS for pgsql-general@arkaria.postgresql.org; Sun, 02 Apr 2023 15:40:25 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pizod-0005pS-OM for pgsql-general@arkaria.postgresql.org; Sun, 02 Apr 2023 15:40:23 +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 1pizoc-0005pH-2i for pgsql-general@lists.postgresql.org; Sun, 02 Apr 2023 15:40:23 +0000 Received: from out1-smtp.messagingengine.com ([66.111.4.25]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pizoY-0000h3-QA for pgsql-general@lists.postgresql.org; Sun, 02 Apr 2023 15:40:21 +0000 Received: from compute5.internal (compute5.nyi.internal [10.202.2.45]) by mailout.nyi.internal (Postfix) with ESMTP id F404D5C017E; Sun, 2 Apr 2023 11:40:15 -0400 (EDT) Received: from mailfrontend1 ([10.202.2.162]) by compute5.internal (MEProxy); Sun, 02 Apr 2023 11:40:15 -0400 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=aklaver.com; h= cc:content-transfer-encoding:content-type:content-type:date:date :from:from:in-reply-to:in-reply-to:message-id:mime-version :references:reply-to:sender:subject:subject:to:to; s=fm1; t= 1680450015; x=1680536415; bh=QcP3AmJ+wupau/c6Zt/73weNwJgEjnsXei0 qPX1z2cU=; b=MPnQH4tknKHO2bTzl0LjS2w6oJa2xj9WpQouDXZpdQohfOw3mqy Lfgy52wybcsRWosDGmEUP0j9l7smwwbsBidk1HJKvOaHe4V3XZEbhngJxADpsr2B Ic2eOewHWzql2wW6e0Wr8hLD23E5Lr9LVfQr32P47QEQdlVQ9mA7w+5UHN69y5Wj ZH3V5KEP6MssJJM5eSPBq9poTXxGVTcOG5e0BJN1DiYtG2foBl0UKxs1l9jjiq9t KUVQMfLvuBTH+rwuprupEjqSicX2IRv/k3WJYiWJkN2XyrcU0ruVO65RN5sBhNoq QDMGR8M3690QbutoKWNH0Yis5SOgeGsP/Xw== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:content-transfer-encoding:content-type :content-type:date:date:feedback-id:feedback-id:from:from :in-reply-to:in-reply-to:message-id:mime-version:references :reply-to:sender:subject:subject:to:to:x-me-proxy:x-me-proxy :x-me-sender:x-me-sender:x-sasl-enc; s=fm2; t=1680450015; x= 1680536415; bh=QcP3AmJ+wupau/c6Zt/73weNwJgEjnsXei0qPX1z2cU=; b=k G9w9/Ufu9zjyvN5lpdPesZ3YlSuwub+/A9nfXcHypL+DJImQPH68pWBJCqILyWvh Ghw0EWsBPp8QAa9WEnp6mwKZAVKpA5L+AwLXhFhD2fkOnupVUqNzOE0McfWXehvZ DPFISJReRUXixf1Zq9Q29//CBjj58VQqnkKxYBOxiUPP0Gm8B04/AIAIHe5W/rVN zxOtt0ulNcYYDoweoQ6al28LF6HXs1Aq19hgQFcFzga8rlxluC1iyO4npNsykxHS STp8/HhA1+/AvIDhhAAqTKUlzFOBtd60vkVT6386kNPF53/Wzb+aZQuNTEaC189t Qd0EJD1TGuPmBVmG2V5bw== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvhedrvdeihedgledvucetufdoteggodetrfdotf fvucfrrhhofhhilhgvmecuhfgrshhtofgrihhlpdfqfgfvpdfurfetoffkrfgpnffqhgen uceurghilhhouhhtmecufedttdenucesvcftvggtihhpihgvnhhtshculddquddttddmne cujfgurhepkfffgggfuffvfhfhjggtgfesthekredttdefjeenucfhrhhomheptegurhhi rghnucfmlhgrvhgvrhcuoegrughrihgrnhdrkhhlrghvvghrsegrkhhlrghvvghrrdgtoh hmqeenucggtffrrghtthgvrhhnpeelkeegjefhkeetheefudevteehiedvudehkeduleet fedvffeuhedtvdegtdeffeenucffohhmrghinhepphhoshhtghhrvghsqhhlrdhorhhgne cuvehluhhsthgvrhfuihiivgeptdenucfrrghrrghmpehmrghilhhfrhhomheprggurhhi rghnrdhklhgrvhgvrhesrghklhgrvhgvrhdrtghomh X-ME-Proxy: Feedback-ID: i76984098:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Sun, 2 Apr 2023 11:40:15 -0400 (EDT) Message-ID: <48ed779b-c405-ca4f-c2d1-5ecc0312d9a3@aklaver.com> Date: Sun, 2 Apr 2023 08:40:14 -0700 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.9.1 Subject: =?UTF-8?B?UmU6IOKAi2pzb25iIEBAIGpzb25wYXRoIG9wZXJhdG9yIGRvYzog4oCL?= =?UTF-8?Q?Only_the_first_item_of_the_result_is_taken_into_account?= To: Erik Wienhold , jian he , pgsql-general list References: <202761461.525027.1680441711777@office.mailbox.org> Content-Language: en-US From: Adrian Klaver In-Reply-To: <202761461.525027.1680441711777@office.mailbox.org> Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 4/2/23 06:21, Erik Wienhold wrote: >> On 01/04/2023 08:02 CEST jian he wrote: >> >> Hi, >> https://www.postgresql.org/docs/current/functions-json.html >>> 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. >>> '{"a":[1,2,3,4,5]}'::jsonb @@ '$.a[*] > 2' → t >> >> select jsonb_path_query('{"a":[1,2,3,4,5]}', '$.a[*]'); >> return >> >>> jsonb_path_query >>> ------------------ >>>  1 >>>  2 >>>  3 >>>  4 >>>  5 >>> (5 rows) >> >> I don't understand:"Only the first item of the result is taken into account.". >> >> Here,JSON path predicate check for the specified JSON valuereturn true, some >> return false. (1 > 2 is false, 2 > 2 is false). > > The result is true if any array element matches the predicate because predicates > are evaluated on sequences. The documentation for executePredicate in > src/backend/utils/adt/jsonpath_exec.c explains it: > >> 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. > > https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/utils/adt/jsonpath_exec.c;h=b561f0e7e803f0e5a546ad118a47f625225b9708;hb=HEAD#l1461 > > Difference between using a predicate as path expression vs filter expression: > > =# select jsonb_path_query('{"a":[1,2,3,4,5]}', '$.a[*] > 2'); > jsonb_path_query > ------------------ > true > (1 row) > > =# select jsonb_path_query('{"a":[1,2,3,4,5]}', '$.a[*] ? (@ > 2)'); > jsonb_path_query > ------------------ > 3 > 4 > 5 > (3 rows) > > If you want the predicate result for each element, you must apply the predicate > to the rows returned from jsonb_path_query: > > =# select elem, elem::float > 2 as pred from jsonb_path_query('{"a":[1,2,3,4,5]}', '$.a[*]') elem; > elem | pred > ------+------ > 1 | f > 2 | f > 3 | t > 4 | t > 5 | t > (5 rows) 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. > > -- > Erik > > -- Adrian Klaver adrian.klaver@aklaver.com