Re: IS JSON STRICT - In oracle => postgres ??

[jian he <jian.universality@xxxxxxxxx>]

On Wed, Jun 14, 2023 at 10:09 PM Holger Jakobs <holger@xxxxxxxxxx> 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?