Thread: IS JSON STRICT - In oracle => postgres ??
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
Nikhil Ingale
On Wed, Jun 14, 2023 at 03:30:50PM +0530, Nikhil Ingale wrote: > 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? Well, if the data is in json(b) column, then it is well formed, isn't it? Best regards, depesz
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
Attachment
Nikhil Ingale schrieb am 14.06.2023 um 12:00: > 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? That check constraint isn't necessary in Postgres. A column defined as jsonb (or json) will not accept invalid JSON. That's pretty much the behaviour that's introduced in Oracle 23 with the new JSON data type.
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 IngaleHi 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
in postgres 16, you can try
-------------------------------
with cte(x) as (select '{"hello":"world","hello":"yes"}')
select pg_input_is_valid(x,'jsonb') from cte;
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?
On 14.06.23 12:00, Nikhil Ingale wrote: > 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? PostgreSQL 16 has an IS JSON predicate. It doesn't have a STRICT clause, which I don't see in the SQL standard. So it might not do exactly the same thing.
Information from you guys helped to resolve the issue.
Thank you all for the valuable inputs.
On Tue, Jun 20, 2023 at 1:37 PM Peter Eisentraut <peter@eisentraut.org> wrote:
On 14.06.23 12:00, Nikhil Ingale wrote:
> 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?
PostgreSQL 16 has an IS JSON predicate.
It doesn't have a STRICT clause, which I don't see in the SQL standard.
So it might not do exactly the same thing.