Forum Stats

  • 3,767,857 Users
  • 2,252,726 Discussions
  • 7,874,366 Comments

Discussions

json

Hi Friends,

Can anybody tell me that whether we can verify JSON data is valid or invalid in oracle(12c).

Basically I have one table where JSON data is inserted. Before transfer this data to another table i need to validate if JSON is valid or invalid.

Answers

  • MaxOrgiyan-Oracle
    MaxOrgiyan-Oracle Posts: 64 Employee
    edited Mar 3, 2021 9:47AM

    Yes, add an is json format json constraint:

    alter table tableNameHere add constraint constraintNameHere check(columnNameHere is json format json);

    If the column with the specified name contains valid JSON, then the above will succeed without error. Otherwise it will give an error.

    Examples:

    SQL >create table myt (c1 blob);

    SQL >insert into myt values (utl_raw.cast_to_raw('{"name" : "marco"}'));

    SQL> alter table myt add constraint myc check(c1 is json format json);

    Table altered.

    SQL> create table myt2 (c1 blob);

    SQL >insert into myt2 values (utl_raw.cast_to_raw('not JSON'));

    SQL> alter table myt2 add constraint myc2 check(c1 is json format json);

    ORA-02293: cannot validate (SCOTT.MYC2) - check constraint violated