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
