Postgres Pro
Downloads
Home   >   mailing lists

Re: IS JSON STRICT - In oracle => postgres ?? - Mailing list pgsql-admin

From jian he
Subject Re: IS JSON STRICT - In oracle => postgres ??
Date
Msg-id CACJufxFswLSLSDbT2o3Fab3TaOxxpA_acPeXxaKc7u-KrhqULQ@mail.gmail.com
Whole thread Raw
In response to Re: IS JSON STRICT - In oracle => postgres ??  (Holger Jakobs <holger@jakobs.com>)
List pgsql-admin
Tree view


On Wed, Jun 14, 2023 at 10:09 PM Holger Jakobs <holger@jakobs.com> wrote:
Am 14.06.23 um 12:00 schrieb Nikhil Ingale:
Hi All,
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 boolean
as
$$
begin  return (p_json::json is not null);
exception   when others then     return false;  
end;
$$
language plpgsql
immutable;

https://stackoverflow.com/questions/30187554/how-to-verify-a-string-is-valid-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

-- 
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012

https://pgpedia.info/p/pg_input_is_valid.html
in postgres 16, you can try
-------------------------------
with cte(x) as (select '{"hello":"world","hello":"yes"}')
select pg_input_is_valid(x,'jsonb') from cte;
---------------------------

It just tries to cast the text to JSON. If it can be casted, it's valid JSON, otherwise it's not.
correct me if I am wrong, seems null corner case not handled? 

pgsql-admin by date:

Previous
From: Ron
Date:
Subject: Re: Conversion from Number to Date
Next
From: Konrad J Hambrick
Date:
Subject: Re: Conversion from Number to Date