terça-feira, 22 de agosto de 2023

[ORACLE] How can I create and check JSON schema in Oracle 23c? - Part 1

Hello everyone

I hope you're doing well!

As we know, JSON is very flexible and sometimes we need to validate the JSON structure.
Oracle 23c has an easy way to validate the JSON schema.

I'm going to access my database in docker:
docker exec -it Orcl23c /bin/bash 

I'll create a new table (I'll use the Visual Studio for better visuialization):

create table mylegos (
    id              number,
    json_data  json           validate '{
"type"      : "object",
"properties": { 
                                        "code"        : {
                                                          "type"           : "string",
  "minLength": 1,
  "maxLength": 10
     },
"collection": {
                          "type"           : "string",
  "minLength": 1,
  "maxLength": 15
     },   
"model"     : {
                          "type"           : "string",
  "minLength": 1,
  "maxLength": 20
     },
   "peaces"         : {
  "type"         : "number",
  "minimum": 0,
  "maximum": 4000
}
  },
"required" : ["code", "collection", "model", "peaces"]
}',
constraint t1_pk primary key (id)
);  


In my table, all JSON atributes are mandatory.

Now, I'll check the JSON schema:

set pages 120 lines 1000
set long 1000000
column table_name format a15
column column_name format a15
column constraint_name format a20
column json_schema format a60

select table_name, 
   column_name,
   constraint_name, 
   json_schema
from user_json_schema_columns;


And finally, I'll put some data into the table and simulated an error.

insert into mylegos (id, json_data) values 
   (1, json('
   {
"code":"42125",
"collection":"Technic",
"model":"Ferrari 488 GTE AF Corse #51",
"peaces": 1677
   }
'
)
);



Why did this happen?

I'm getting an error because the model has more than 20 characters. And it violated the "maxLength":20 constraint of the model attribute.

When I use the correct rule:

insert into mylegos (id, json_data) values 
   (1, json('
   {
 "code":"42125",
 "collection":"Technic",
 "model":"Ferrari 488 GTE AF51",
 "peaces": 1677
   }
'
)
);


select json_serialize(ml.json_data pretty) from mylegos ml;


And thats it! As simple as it sounds!

I hope this post helps you!!!

Regards
Mario

Nenhum comentário:

Postar um comentário

Isso te ajudou? Comente...

Postagem em destaque

[ORACLE] Increasing the number of cores in a virtualized Oracle Database Appliance.

Hello everyone. How are you going? Today, I'll show you the process to increase the number of cores in a virtualized ODA HA X7-2. Import...