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 1naUF0-0003ga-Cg for pgsql-committers@arkaria.postgresql.org; Sat, 02 Apr 2022 03:15:54 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1naUEz-0008IN-5w for pgsql-committers@arkaria.postgresql.org; Sat, 02 Apr 2022 03:15:53 +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 1naUEy-0008IE-Ux for pgsql-committers@lists.postgresql.org; Sat, 02 Apr 2022 03:15:52 +0000 Received: from wout5-smtp.messagingengine.com ([64.147.123.21]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1naUEw-0007XQ-FW for pgsql-committers@lists.postgresql.org; Sat, 02 Apr 2022 03:15:52 +0000 Received: from compute5.internal (compute5.nyi.internal [10.202.2.45]) by mailout.west.internal (Postfix) with ESMTP id 767E93202033; Fri, 1 Apr 2022 23:15:48 -0400 (EDT) Received: from mailfrontend2 ([10.202.2.163]) by compute5.internal (MEProxy); Fri, 01 Apr 2022 23:15:48 -0400 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=paquier.xyz; h= cc:cc: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; bh=R3SvE7H5jWFpEPvhK8y4dtarEwNsBJOAuugN9W 9+9wg=; b=Prn8U1/ofxJH9vNeBIuYx97wGRllRecoo3BTXtK3xx1pS7IayjupxK nrHQjGAPcet13k+fuSXs/6THNxOAuh3EhjRLo4aJmvd2DT6e4wfE3/4aMcUuigQn YnSUv30JXcVocx+HSF72m9F6kEwDfrpjoJ8RBG35JBuoxdKF4dcmI3n8XnHcJ90d 2niWMOQSM/AaMwYNKiC4/kb7IBGtMfLxOFNO4CI3easLSy/czskln6a/7SxJZmJ8 94hoGli8wFp+2uWvNnkrTWmcF0+j9uXgglm6llZqw8CPR1iG2/DX6I2TKYLTfRto 71zgkBGEEXQhCp830vm4YnbeRRRAacTg== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:cc:content-type:date:date: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=fm3; bh=R3SvE7H5jWFpEPvhK 8y4dtarEwNsBJOAuugN9W9+9wg=; b=jILpkWJSocDotuosHRVSLfqkWbcy0qCZ6 m0DDZ3RcFWKggSy0412XKbRVqvm5IkIxJupm/h/M4WFn04pt/ASdc50g9cBf7QBY XtZTtZL+bdyWpYl0Al/FIXtmcFMkAHCRoNr3hkgL0OdE0i5nX5odRmcvXU5IJlq1 obIwOpr7RKnvnSYmEspEFvdrmPZpXv2u9fAUQP7UQhY5MP6KstDKTm7ZE06PcnL/ pP01OsAQdTZs2hWiSNSmi6Z1eVSnusR/yrzGRnvb2A2hMe5BpN5s7vr2nl99XUzt qJYMX2b4Py0i1uYLxTkc8DIhYvAcCbzJuNY1x1drImsU3ZKiHrSsg== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvvddrudeijedgieduucetufdoteggodetrfdotf fvucfrrhhofhhilhgvmecuhfgrshhtofgrihhlpdfqfgfvpdfurfetoffkrfgpnffqhgen uceurghilhhouhhtmecufedttdenucesvcftvggtihhpihgvnhhtshculddquddttddmne gfrhhlucfvnfffucdlfeehmdenucfjughrpeffhffvuffkfhggtggujgesghdtreertddt vdenucfhrhhomhepofhitghhrggvlhcurfgrqhhuihgvrhcuoehmihgthhgrvghlsehprg hquhhivghrrdighiiiqeenucggtffrrghtthgvrhhnpeffudeffeelgffhkedukeekvedv udfgieetkeekudelfeevhffhffdtudffieeutdenucffohhmrghinhepphhoshhtghhrvg hsqhhlrdhorhhgnecuvehluhhsthgvrhfuihiivgeptdenucfrrghrrghmpehmrghilhhf rhhomhepmhhitghhrggvlhesphgrqhhuihgvrhdrgiihii X-ME-Proxy: Received: by mail.messagingengine.com (Postfix) with ESMTPA; Fri, 1 Apr 2022 23:15:46 -0400 (EDT) Date: Sat, 2 Apr 2022 12:15:44 +0900 From: Michael Paquier To: Andrew Dunstan Cc: pgsql-committers@lists.postgresql.org Subject: Re: pgsql: SQL/JSON query functions Message-ID: References: MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha512; protocol="application/pgp-signature"; boundary="9WDEi0659loew9Hq" Content-Disposition: inline In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --9WDEi0659loew9Hq Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Content-Transfer-Encoding: quoted-printable Hi Andrew, On Tue, Mar 29, 2022 at 08:57:37PM +0000, Andrew Dunstan wrote: > SQL/JSON query functions >=20 > This introduces the SQL/JSON functions for querying JSON data using > jsonpath expressions. The functions are: >=20 > JSON_EXISTS() > JSON_QUERY() > JSON_VALUE() >=20 > All of these functions only operate on jsonb. The workaround for now is > to cast the argument to jsonb. >=20 > JSON_EXISTS() tests if the jsonpath expression applied to the jsonb > value yields any values. JSON_VALUE() must return a single value, and an > error occurs if it tries to return multiple values. JSON_QUERY() must > return a json object or array, and there are various WRAPPER options for > handling scalar or multi-value results. Both these functions have > options for handling EMPTY and ERROR conditions. Some of the tests introduced in this commit are a bit unstable when run through a primary/standby setup. conchuela has showed one failure today: https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=3Dconchuela&dt=3D20= 22-04-02%2002%3A36%3A03 Thanks, -- Michael --9WDEi0659loew9Hq Content-Type: application/pgp-signature; name="signature.asc" -----BEGIN PGP SIGNATURE----- iQIzBAABCgAdFiEEG72nH6vTowiyblFKnvQgOdbyQH0FAmJHv+AACgkQnvQgOdby QH39LQ/+O60JeENL04yXYAKxSlI/6ZO58gOvHkzejQVgpyjz0Ll7d8MBCVQ05dZE Szp0oHRpqLjl8Yajm3ajC9YcDVdiHWKXFTRf6gIZAvS97Rlwny1wOxpD2NcZ6ssz cShMTWLO4XGZ1jEt2h98LXwMUOdiB9SUfjiilchUpS+KJ/5UcIkjaPX68uPID6cV Iv5qlXwqQyreF9U+2iez30Y8w8zuxjDSK0Pvky3DyQv4qmpTfVgdTDrW1y1naj1m pnjZRngRqnHFIWKd3Fjy62xFFYGzm7Te8afBNpnxismHW7o5C9NxU3Y29d6K/G8b QLYTC8hB/3wQiTnMKX6ItdUimG7xItbWWhWB659u7voVURgg/cNyJRE2cn8VYBhB Y5y7GABhNsGtF4YfcolIXSFMirvKHzGOCBqH7gb+r05q/CTmRNZ+Bh7awt10iNwK bkA7zRSDQQHsiE1e+zxlqoBupizss6n0otyKJVjeCYmGCYqBWQ8fgXr56F13K+rc Wn7aHRQ8ip4zdDGmapcT9Sw3O/Ye7mOu9Fpxza/3qOQThZIcyXyeVm8lT1koH6qy 6CnSjDjHkV3IyjTOwbFsw3XKoG7kll2qo9v9j3gcMq4yvqbzRRVSuuTLLPlMOLWK A20Z7+6NYRhBPzeBIG1pJ291wFdIilk/hL7DEUBlyo9HsvBz2w8= =I/Od -----END PGP SIGNATURE----- --9WDEi0659loew9Hq--