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

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