A JSON schema describes what valid documents look like: which fields exist, their types, allowed values, and which are required. DBMS_JSON_SCHEMA checks documents against a schema in PL/SQL and reports every problem it finds, and DESCRIBE generates a JSON schema from a table or view.
Code for This Guide
The main examples are in the examples/pkg-json folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
dbms_json_schema.is_valid(json_doc, json_schema) return boolean -- function dbms_json_schema.is_valid(json_doc, json_schema, ok_out, errors_out); -- procedure dbms_json_schema.describe(object_name) return json
Validate Loyalty Documents
The schema requires a member ID matching a pattern and a tier from a list, and allows non-negative integer points.
Example:
declare
c_schema constant json := json(q'[{
"type" : "object",
"properties": {"memberId": {"type": "string", "pattern": "^NM[0-9]{6}$"},
"tier" : {"enum": ["Blue", "Silver", "Gold", "Platinum"]},
"points" : {"type": "integer", "minimum": 0}},
"required" : ["memberId", "tier"]}]');
v_ok boolean;
v_errs json;
begin
dbms_output.put_line('valid? ' || dbms_json_schema.is_valid(
json('{"memberId":"NM101369","tier":"Gold","points":5200}'), c_schema));
dbms_json_schema.is_valid(json('{"memberId":"X1","tier":"Diamond","points":-5}'),
c_schema, v_ok, v_errs);
dbms_output.put_line('valid? ' || case when v_ok then 'yes' else 'no' end);
dbms_output.put_line(json_serialize(v_errs pretty));
end;
/
select json_value(d, '$.dbObject') as db_object,
json_serialize(json_query(d, '$.properties.REGION')) as region_column
from (select dbms_json_schema.describe('COUNTRIES') as d);Output:
valid? 1
valid? no
{
"valid" : false,
"errors" :
[
{
"schemaPath" : "$",
"instancePath" : "$",
"code" : "JZN-00501",
"error" : "JSON schema validation failed"
},
{
"schemaPath" : "$.properties",
"instancePath" : "$",
"code" : "JZN-00514",
"error" : "invalid properties: 'memberId', 'tier', 'points'"
},
{
"schemaPath" : "$.properties.points.minimum",
"instancePath" : "$.points",
"code" : "JZN-00507",
"error" : "value less than minimum value, actual: -5, expected: 0"
},
{
"schemaPath" : "$.properties.tier.enum",
"instancePath" : "$.tier",
"code" : "JZN-00505",
"error" : "element was not equal to any of the enumerated values"
},
{
"schemaPath" : "$.properties.memberId.pattern",
"instancePath" : "$.memberId",
"code" : "JZN-00538",
"error" : "value did not match the expected pattern"
}
]
}
PL/SQL procedure successfully completed.
DB_OBJECT REGION_COLUMN
___________________ ___________________________________________________________
NIMBUS.COUNTRIES {"type":"string","extendedType":"string","maxLength":30}- The first document is valid, so the function returns TRUE, printed as 1.
- The procedure form returns FALSE and a JSON report with every error: points below the minimum, a tier not in the list, and a member ID that does not match the pattern.
- DESCRIBE('COUNTRIES') generates a schema from the table, where REGION is a string of at most 30 characters.
Things to Know
- To enforce a schema on a column, add it in the column definition with VALIDATE, so invalid documents are rejected on insert.
- Use the procedure form when you need to tell users what is wrong, the function form for a quick check.
- JSON schemas follow the JSON Schema standard, with Oracle extensions such as extendedType for SQL types.
Related Guides
Conclusion
DBMS_JSON_SCHEMA.IS_VALID checks documents against a JSON schema and lists every error, and DESCRIBE produces a schema from a table or view. Validate documents from outside before storing them, and report the errors in a form users can act on.
See my articles more often when you search Google.
