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

On Wed, Jun 14, 2023 at 10:09 PM Holger Jakobs <holger(at)jakobs(dot)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 booleanas
> $$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?