JSON_SCHEMA_VALID

JSON_SCHEMA_VALID is available from MariaDB 11.1.

Syntax

JSON_SCHEMA_VALID(schema, json);

Description

JSON_SCHEMA_VALID allows MariaDB to support JSON schema validation. If a given json is valid against a schema it returns true. When JSON does not validate against the schema, it does not return a message about which keyword it failed against and only returns false.

The function supports JSON Schema Draft 2020 with a few exceptions:

• External resources are not supported.

• Hyper schema keywords are not supported.

• Formats like date, email etc are treated as annotations.

Examples

To create validation rules for json field, do this:

CREATE TABLE obj_table(val_obj JSON CHECK(JSON_SCHEMA_VALID('{  "type":"object",  "properties": {  "number1":{  "type":"number",  "maximum":5,  "const":4  },  "string1":{  "type":"string",  "maxLength":5,  "minLength":3  },  "object1":{  "type":"object",  "properties":{  "key1": {"type":"string"},  "key2":{"type":"array"},  "key3":{"type":"number", "minimum":3}  },  "dependentRequired": { "key1":["key3"] }  }  },  "required":["number1","object1"]  }', val_obj)));  INSERT INTO obj_table VALUES(  '{"number1":4, "string1":"abcd",   "object1":{"key1":"val1", "key2":[1,2,3, "string1"], "key3":4}}' );  INSERT INTO obj_table VALUES(  '{"number1":3, "string1":"abcd",   "object1":{"key1":"val1", "key2":[1,2,3, "string1"], "key3":4}}' ); ERROR 4025 (23000): CONSTRAINT `obj_table.val_obj` failed for `test`.`obj_table`  SELECT * FROM obj_table; +--------------------------------------------------------------------------------------------------+ | val_obj | +--------------------------------------------------------------------------------------------------+ | {"number1":4, "string1":"abcd", "object1":{"key1":"val1", "key2":[1,2,3, "string1"], "key3":4}} | +--------------------------------------------------------------------------------------------------+  SET @schema= '{  "properties" : {  "number1":{ "maximum":10 },  "string1" : { "maxLength": 3}   } }';  SELECT JSON_SCHEMA_VALID(@schema, '{ "number1":25, "string1":"ab" }'); +----------------------------------------------------------------+ | JSON_SCHEMA_VALID(@schema, '{ "number1":25, "string1":"ab" }') | +----------------------------------------------------------------+ | 0 | +----------------------------------------------------------------+  SELECT JSON_SCHEMA_VALID(@schema, '{ "number1":10, "string1":"ab" }'); +----------------------------------------------------------------+ | JSON_SCHEMA_VALID(@schema, '{ "number1":10, "string1":"ab" }') | +----------------------------------------------------------------+ | 1 | +----------------------------------------------------------------+

_{This page is licensed: CC BY-SA / Gnu FDL}