JSON_SCHEMA_VALID()

Syntax

JSON_SCHEMA_VALID(schema, json);

Description

Starting with version 11.1, MariaDB Server supports JSON schema validation by using function JSON_SCHEMA_VALID(). If a json is valid against a schema, it returns true otherwise returns false.

It supports json schema draft 2020 with few exceptions:

• External resources are not supported
• Hyper schema keywords are not supported

Examples

To create validation rules for json field

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)));
Query OK, 0 rows affected (0.001 sec)

INSERT INTO obj_table VALUES('{"number1":4, "string1":"abcd", "object1":{"key1":"val1", "key2":[1,2,3, "string1"], "key3":4}}');
Query OK, 1 row affected (0.004 sec)
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}} |
+--------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

SET @schema= '{
                                "properties" : {
                                                 "number1":{ "maximum":10 },
                                                 "string1" : { "maxLength": 3} 
                                               }
                              }';
Query OK, 0 rows affected (0.000 sec)

SELECT JSON_SCHEMA_VALID(@schema, '{ "number1":25, "string1":"ab" }');
+----------------------------------------------------------------+
| JSON_SCHEMA_VALID(@schema, '{ "number1":25, "string1":"ab" }') |
+----------------------------------------------------------------+
|                                                              0 |
+----------------------------------------------------------------+
1 row in set (0.000 sec)

SELECT JSON_SCHEMA_VALID(@schema, '{ "number1":10, "string1":"ab" }');
+----------------------------------------------------------------+
| JSON_SCHEMA_VALID(@schema, '{ "number1":10, "string1":"ab" }') |
+----------------------------------------------------------------+
|                                                              1 |
+----------------------------------------------------------------+
1 row in set (0.001 sec)