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 1q9RBg-0006Xr-61 for pgsql-admin@arkaria.postgresql.org; Wed, 14 Jun 2023 14:09:28 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1q9RBe-0004EJ-23 for pgsql-admin@arkaria.postgresql.org; Wed, 14 Jun 2023 14:09:26 +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 1q9RBd-0004E9-ND for pgsql-admin@lists.postgresql.org; Wed, 14 Jun 2023 14:09:25 +0000 Received: from jakobs.com ([85.214.83.89] helo=rs.plausibolo.de) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1q9RBa-002LJd-Js for pgsql-admin@lists.postgresql.org; Wed, 14 Jun 2023 14:09:25 +0000 Received: from localhost (localhost [127.0.0.1]) by rs.plausibolo.de (Postfix) with ESMTP id A83BD3803F9; Wed, 14 Jun 2023 16:09:21 +0200 (CEST) Received: from rs.plausibolo.de ([127.0.0.1]) by localhost (h2367442.stratoserver.net [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id nRX7M_rK70tl; Wed, 14 Jun 2023 16:09:18 +0200 (CEST) Received: from [192.168.178.28] (ip-037-201-152-231.um10.pools.vodafone-ip.de [37.201.152.231]) by rs.plausibolo.de (Postfix) with ESMTPSA id 32ED73803C8; Wed, 14 Jun 2023 16:09:15 +0200 (CEST) Message-ID: <776384e5-2480-e950-9390-3212f3033856@jakobs.com> Date: Wed, 14 Jun 2023 16:09:14 +0200 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.11.0 Subject: Re: IS JSON STRICT - In oracle => postgres ?? Content-Language: de-DE To: pgsql-admin@lists.postgresql.org References: From: Holger Jakobs In-Reply-To: Content-Type: multipart/signed; micalg=pgp-sha256; protocol="application/pgp-signature"; boundary="------------C3wJo0vsT8NgVEc5H3lxclwk" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is an OpenPGP/MIME signed message (RFC 4880 and 3156) --------------C3wJo0vsT8NgVEc5H3lxclwk Content-Type: multipart/mixed; boundary="------------MMAOZhkFCB9ic4x01DcnVjUN"; protected-headers="v1" From: Holger Jakobs To: pgsql-admin@lists.postgresql.org Message-ID: <776384e5-2480-e950-9390-3212f3033856@jakobs.com> Subject: Re: IS JSON STRICT - In oracle => postgres ?? References: In-Reply-To: --------------MMAOZhkFCB9ic4x01DcnVjUN Content-Type: multipart/alternative; boundary="------------xSUBm2hWcxmSlSr4T6Y0qXY2" --------------xSUBm2hWcxmSlSr4T6Y0qXY2 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: base64 QW0gMTQuMDYuMjMgdW0gMTI6MDAgc2NocmllYiBOaWtoaWwgSW5nYWxlOg0KPiBIacKgQWxs LA0KPiBJbiBvcmFjbGUgd2UgY2FuIHVzZSB0aGUgKmlzIGpzb24qIGNoZWNrIGNvbnN0cmFp bnQgZm9yIHRoZSBqc29uIA0KPiBjb2x1bW4gdG8gZW5zdXJlIHRoZSBkYXRhIGlzIHdlbGwg Zm9ybWVkLg0KPg0KPiBIb3cgZG8gd2UgZW5zdXJlIHRoZSBzYW1lIGluIHBvc3RncmVzIGRh dGFiYXNlPw0KPg0KPiBSZWdhcmRzLA0KPiBOaWtoaWwgSW5nYWxlDQoNCkhpIE5pa2hpbCwN Cg0KSWYgeW91IGluc2lzdCB0aGF0IHlvdSB3YW50IHRvIGNoZWNrIHdpdGhvdXQgcHV0dGlu ZyB0aGUgY29udGVudCBpbnRvIGEgDQpKU09OKEIpIGNvbHVtbiwgeW91IG1pZ2h0IHRyeSB0 aGlzIGZ1bmN0aW9uOg0KDQp8Y3JlYXRlIG9yIHJlcGxhY2UgZnVuY3Rpb24gaXNfdmFsaWRf anNvbihwX2pzb24gdGV4dCkgcmV0dXJucyBib29sZWFuIA0KYXMgJCQgYmVnaW4gcmV0dXJu IChwX2pzb246Ompzb24gaXMgbm90IG51bGwpOyBleGNlcHRpb24gd2hlbiBvdGhlcnMgDQp0 aGVuIHJldHVybiBmYWxzZTsgZW5kOyAkJCBsYW5ndWFnZSBwbHBnc3FsIGltbXV0YWJsZTt8 DQoNCmh0dHBzOi8vc3RhY2tvdmVyZmxvdy5jb20vcXVlc3Rpb25zLzMwMTg3NTU0L2hvdy10 by12ZXJpZnktYS1zdHJpbmctaXMtdmFsaWQtanNvbi1pbi1wb3N0Z3Jlc3FsDQoNCkl0IGp1 c3QgdHJpZXMgdG8gY2FzdCB0aGUgdGV4dCB0byBKU09OLiBJZiBpdCBjYW4gYmUgY2FzdGVk LCBpdCdzIHZhbGlkIA0KSlNPTiwgb3RoZXJ3aXNlIGl0J3Mgbm90Lg0KDQpSZWdhcmRzLA0K DQpIb2xnZXINCg0KLS0gDQpIb2xnZXIgSmFrb2JzLCBCZXJnaXNjaCBHbGFkYmFjaCwgVGVs LiArNDktMTc4LTk3NTkwMTINCg0K --------------xSUBm2hWcxmSlSr4T6Y0qXY2 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Am 14.06.23 um 12:00 schrieb Nikhil Ingale:
Hi=C2=A0All,
In oracle we can use the is json check constraint for the json column to ensure the data is well formed.

How do we ensure the same in postgres database?

Regards,
Nikhil Ingale

Hi Nikhil,

If you insist that you want to check without putting the content into a JSON(B) column, you might try this function:

create or replace function is_valid_=
json(p_json text)
  returns b=
oolean
as
$$
begin
  return (p_json::json is not null);
exception=20
  when others then
     return false; =20
end;
$$
language plpgsql
immutable;

https://stackoverflow.com/questions/30187554/how-to-verify-a-string-is-v= alid-json-in-postgresql

It just tries to cast the text to JSON. If it can be casted, it's valid JSON, otherwise it's not.

Regards,

Holger

--=20
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
--------------xSUBm2hWcxmSlSr4T6Y0qXY2-- --------------MMAOZhkFCB9ic4x01DcnVjUN-- --------------C3wJo0vsT8NgVEc5H3lxclwk Content-Type: application/pgp-signature; name="OpenPGP_signature.asc" Content-Description: OpenPGP digital signature Content-Disposition: attachment; filename="OpenPGP_signature" -----BEGIN PGP SIGNATURE----- wmMEABEIACMWIQScuJCxWJK1p5BOebGKV5yTsxlhugUCZInKCgUDAAAAAAAKCRCKV5yTsxlhuvDq AKCVllNXUSIZf0g6Mx/71yo3acqIMgCfUD1lL0PZMdAZ4zl3fSWk7GtYXLQ= =bl8y -----END PGP SIGNATURE----- --------------C3wJo0vsT8NgVEc5H3lxclwk--