diff options
| author | Rucha Deodhar <rucha.deodhar@mariadb.com> | 2022-10-28 13:03:13 +0530 |
|---|---|---|
| committer | Rucha Deodhar <rucha.deodhar@mariadb.com> | 2023-04-26 11:00:08 +0530 |
| commit | 358b8495f5ffb19a1a89574fd897261e8ff12938 (patch) | |
| tree | 7cf421be3ec83efff6a9905c13c3c9e2687cdc1a /mysql-test/main | |
| parent | af0e0ad18d5295d57696e3d26d5a7b13dca4e420 (diff) | |
| download | mariadb-git-358b8495f5ffb19a1a89574fd897261e8ff12938.tar.gz | |
MDEV-27128: Implement JSON Schema Validation FUNCTION
Implementation:
Implementation is made according to json schema validation draft 2020
JSON schema basically has same structure as that of json object, consisting
of key-value pairs. So it can be parsed in the same manner as
any json object.
However, none of the keywords are mandatory, so making guess about the
json value type based only on the keywords would be incorrect.
Hence we need separate objects denoting each keyword.
So during create_object_and_handle_keyword() we create appropriate objects
based on the keywords and validate each of them individually on the json
document by calling respective validate() function if the type matches.
If any of them fails, return false, else return true.
Diffstat (limited to 'mysql-test/main')
| -rw-r--r-- | mysql-test/main/func_json.result | 1901 | ||||
| -rw-r--r-- | mysql-test/main/func_json.test | 1603 | ||||
| -rw-r--r-- | mysql-test/main/json_debug_nonembedded.result | 7 | ||||
| -rw-r--r-- | mysql-test/main/json_debug_nonembedded.test | 9 |
4 files changed, 3520 insertions, 0 deletions
diff --git a/mysql-test/main/func_json.result b/mysql-test/main/func_json.result index 213cc798a6d..8b2b30cea8f 100644 --- a/mysql-test/main/func_json.result +++ b/mysql-test/main/func_json.result @@ -2562,3 +2562,1904 @@ JSON_EXTRACT('{ "my-key": 1 }', '$.my-key') # # End of 10.9 Test # +# Beginning of 11.1 test +# +# MDEV-27128: Implement JSON Schema Validation FUNCTION +# +# Checking annotations +SET @schema_number= '{ + "title" : "This is title 1", + "description":"this is description 1", + "$comment":"This is comment 1", + "type":"number", + "deprecated":true, + "readOnly":true, + "writeOnly":false, + "example":[2], + "default":4, + "$schema": "https://json-schema.org/draft/2019-09/json-schema-validation.html#rfc.section.9.5" + }'; +SELECT JSON_SCHEMA_VALID(@schema_number, '"string1"'); +JSON_SCHEMA_VALID(@schema_number, '"string1"') +0 +SELECT JSON_SCHEMA_VALID(@schema_number, '2'); +JSON_SCHEMA_VALID(@schema_number, '2') +1 +# Checking empty schema with empty json document +SET @schema= '{}'; +SELECT JSON_SCHEMA_VALID(@schema, ''); +JSON_SCHEMA_VALID(@schema, '') +1 +SELECT JSON_SCHEMA_VALID(@schema, '{}'); +JSON_SCHEMA_VALID(@schema, '{}') +1 +SELECT JSON_SCHEMA_VALID(@schema, '[]'); +JSON_SCHEMA_VALID(@schema, '[]') +1 +SELECT JSON_SCHEMA_VALID(@schema, 'null'); +JSON_SCHEMA_VALID(@schema, 'null') +1 +SELECT JSON_SCHEMA_VALID(@schema, 'true'); +JSON_SCHEMA_VALID(@schema, 'true') +1 +SELECT JSON_SCHEMA_VALID(@schema, 'false'); +JSON_SCHEMA_VALID(@schema, 'false') +1 +# Checking scalar against json schema +# Checking boolean and null +SET @schema_true= '{ "type": "boolean"}'; +SELECT JSON_SCHEMA_VALID(@schema_true, 'true'); +JSON_SCHEMA_VALID(@schema_true, 'true') +1 +SELECT JSON_SCHEMA_VALID(@schema_true, 'false'); +JSON_SCHEMA_VALID(@schema_true, 'false') +1 +SELECT JSON_SCHEMA_VALID(@schema_true, 'null'); +JSON_SCHEMA_VALID(@schema_true, 'null') +0 +SET @schema_true= '{ "type": "boolean", + "const":"false"}'; +SELECT JSON_SCHEMA_VALID(@schema_true, 'true'); +JSON_SCHEMA_VALID(@schema_true, 'true') +0 +SET @schema_true= '{ "type": "boolean", + "enum":[true, null, false]}'; +SELECT JSON_SCHEMA_VALID(@schema_true, 'true'); +JSON_SCHEMA_VALID(@schema_true, 'true') +1 +SET @schema_true= '{ "type": "boolean", + "enum": [null, false]}'; +SELECT JSON_SCHEMA_VALID(@schema_true, 'true'); +JSON_SCHEMA_VALID(@schema_true, 'true') +0 +SET @schema_true= '{ "type": "boolean", + "enum": [null, true]}'; +SELECT JSON_SCHEMA_VALID(@schema_true, 'true'); +JSON_SCHEMA_VALID(@schema_true, 'true') +1 +SET @schema_true= '{ "type": "boolean", + "const":"false"}'; +SELECT JSON_SCHEMA_VALID(@schema_true, 'true'); +JSON_SCHEMA_VALID(@schema_true, 'true') +0 +# Type can be more than one +SET @schema= ' + { + "type":["string", "number","array"] + }'; +SELECT JSON_SCHEMA_VALID(@schema, '{"key1":"val1"}'); +JSON_SCHEMA_VALID(@schema, '{"key1":"val1"}') +0 +SELECT JSON_SCHEMA_VALID(@schema, '"abc"'); +JSON_SCHEMA_VALID(@schema, '"abc"') +1 +SELECT JSON_SCHEMA_VALID(@schema, '3.14'); +JSON_SCHEMA_VALID(@schema, '3.14') +1 +# Checking number +SET @schema_number= '{ + "maximum":7, + "minimum": 3, + "multipleOf":3 +}'; +SELECT JSON_SCHEMA_VALID(@schema_number, '2'); +JSON_SCHEMA_VALID(@schema_number, '2') +0 +SET @schema_number= '{ + "type": "number", + "maximum":13, + "minimum": 4, + "multipleOf":3, + "exclusiveMaximum": 9, + "exclusiveMinimum":4 +}'; +SELECT JSON_SCHEMA_VALID(@schema_number, '2'); +JSON_SCHEMA_VALID(@schema_number, '2') +0 +SELECT JSON_SCHEMA_VALID(@schema_number, '6'); +JSON_SCHEMA_VALID(@schema_number, '6') +1 +SELECT JSON_SCHEMA_VALID(@schema_number, '9'); +JSON_SCHEMA_VALID(@schema_number, '9') +0 +SELECT JSON_SCHEMA_VALID(@schema_number, '5'); +JSON_SCHEMA_VALID(@schema_number, '5') +0 +SET @schema_number= '{ + "type": "number", + "maximum":100, + "minimum": 0, + "enum": [1, 2, "3", [4, 5, 6], {"key1":"val1"}] +}'; +SELECT JSON_SCHEMA_VALID(@schema_number, 1); +JSON_SCHEMA_VALID(@schema_number, 1) +1 +SELECT JSON_SCHEMA_VALID(@schema_number, 3); +JSON_SCHEMA_VALID(@schema_number, 3) +0 +SET @schema_number= '{ + "type":"number", + "maximum":10, + "const":2 + }'; +SELECT JSON_SCHEMA_VALID(@schema_number, '3'); +JSON_SCHEMA_VALID(@schema_number, '3') +0 +SELECT JSON_SCHEMA_VALID(@schema_number, '2'); +JSON_SCHEMA_VALID(@schema_number, '2') +1 +# Checking string +# checking format keyword. (not validating for now) +SET @schema_string= '{ + "type": "string", + "format":"date-time" +}'; +SELECT JSON_SCHEMA_VALID(@schema_string, '"not_date-time"'); +JSON_SCHEMA_VALID(@schema_string, '"not_date-time"') +1 +SET @schema_string= '{ + "type": "string", + "format":"date" +}'; +SELECT JSON_SCHEMA_VALID(@schema_string, '"not_date"'); +JSON_SCHEMA_VALID(@schema_string, '"not_date"') +1 +SET @schema_string= '{ + "type": "string", + "format":"time" +}'; +SELECT JSON_SCHEMA_VALID(@schema_string, '"not_time"'); +JSON_SCHEMA_VALID(@schema_string, '"not_time"') +1 +SET @schema_string= '{ + "type": "string", + "format":"duration" +}'; +SELECT JSON_SCHEMA_VALID(@schema_string, '"not_duration"'); +JSON_SCHEMA_VALID(@schema_string, '"not_duration"') +1 +SET @schema_string= '{ + "type": "string", + "format":"email" +}'; +SELECT JSON_SCHEMA_VALID(@schema_string, '"not_email"'); +JSON_SCHEMA_VALID(@schema_string, '"not_email"') +1 +SET @schema_string= '{ + "type": "string", + "format":"idn-email" +}'; +SELECT JSON_SCHEMA_VALID(@schema_string, '"not_idn-email"'); +JSON_SCHEMA_VALID(@schema_string, '"not_idn-email"') +1 +SET @schema_string= '{ + "type": "string", + "format":"hostname" +}'; +SELECT JSON_SCHEMA_VALID(@schema_string, '"not_hostname"'); +JSON_SCHEMA_VALID(@schema_string, '"not_hostname"') +1 +SET @schema_string= '{ + "type": "string", + "format":"idn-hostname" +}'; +SELECT JSON_SCHEMA_VALID(@schema_string, '"not_idn-hostname"'); +JSON_SCHEMA_VALID(@schema_string, '"not_idn-hostname"') +1 +SET @schema_string= '{ + "type": "string", + "format":"ipv4" +}'; +SELECT JSON_SCHEMA_VALID(@schema_string, '"not_ipv4"'); +JSON_SCHEMA_VALID(@schema_string, '"not_ipv4"') +1 +SET @schema_string= '{ + "type": "string", + "format":"ipv6" +}'; +SELECT JSON_SCHEMA_VALID(@schema_string, '"not_ipv6"'); +JSON_SCHEMA_VALID(@schema_string, '"not_ipv6"') +1 +SET @schema_string= '{ + "type": "string", + "format":"uri" +}'; +SELECT JSON_SCHEMA_VALID(@schema_string, '"not_uri"'); +JSON_SCHEMA_VALID(@schema_string, '"not_uri"') +1 +SET @schema_string= '{ + "type": "string", + "format":"uri-reference" +}'; +SELECT JSON_SCHEMA_VALID(@schema_string, '"not_uri-reference"'); +JSON_SCHEMA_VALID(@schema_string, '"not_uri-reference"') +1 +SET @schema_string= '{ + "type": "string", + "format":"iri" +}'; +SELECT JSON_SCHEMA_VALID(@schema_string, '"not_iri"'); +JSON_SCHEMA_VALID(@schema_string, '"not_iri"') +1 +SET @schema_string= '{ + "type": "string", + "format":"iri-reference" +}'; +SELECT JSON_SCHEMA_VALID(@schema_string, '"not_iri-reference"'); +JSON_SCHEMA_VALID(@schema_string, '"not_iri-reference"') +1 +SET @schema_string= '{ + "type": "string", + "format":"uuid" +}'; +SELECT JSON_SCHEMA_VALID(@schema_string, '"not_uuid"'); +JSON_SCHEMA_VALID(@schema_string, '"not_uuid"') +1 +SET @schema_string= '{ + "type": "string", + "format":"json-pointer" +}'; +SELECT JSON_SCHEMA_VALID(@schema_string, '"not_json-pointer"'); +JSON_SCHEMA_VALID(@schema_string, '"not_json-pointer"') +1 +SET @schema_string= '{ + "type": "string", + "format":"relative-json-pointer" +}'; +SELECT JSON_SCHEMA_VALID(@schema_string, '"not_relative-json-pointer"'); +JSON_SCHEMA_VALID(@schema_string, '"not_relative-json-pointer"') +1 +SET @schema_string= '{ + "type": "string", + "format":"regex" +}'; +SELECT JSON_SCHEMA_VALID(@schema_string, '"not_regex"'); +JSON_SCHEMA_VALID(@schema_string, '"not_regex"') +1 +# Validating other string keywords +SET @schema_string= '{ + "type": "string", + "maxLength":7, + "minLength": 4 +}'; +SELECT JSON_SCHEMA_VALID(@schema_string, '"foobar"'); +JSON_SCHEMA_VALID(@schema_string, '"foobar"') +1 +SET @schema_string= '{ + "type": "string", + "maxLength": 10, + "minLength": 8 +}'; +SELECT JSON_SCHEMA_VALID(@schema_string, '"foobar"'); +JSON_SCHEMA_VALID(@schema_string, '"foobar"') +0 +SELECT JSON_SCHEMA_VALID(@schema_string, '"foobar123"'); +JSON_SCHEMA_VALID(@schema_string, '"foobar123"') +1 +SET @schema_string= '{ + "type": "string", + "maxLength": 10, + "minLength": 3, + "const": "foobar" +}'; +SELECT JSON_SCHEMA_VALID(@schema_string, '"foobar123"'); +JSON_SCHEMA_VALID(@schema_string, '"foobar123"') +0 +SET @schema_string= '{ + "type": "string", + "enum": ["red", "green", "blue"] +}'; +SELECT JSON_SCHEMA_VALID(@schema_string, '"green"'); +JSON_SCHEMA_VALID(@schema_string, '"green"') +1 +SELECT JSON_SCHEMA_VALID(@schema_string, '"orange"'); +JSON_SCHEMA_VALID(@schema_string, '"orange"') +0 +SET @string_schema= '{ + "type":"string", + "pattern":"ab+c" + }'; +SELECT JSON_SCHEMA_VALID(@string_schema, '"abc"'); +JSON_SCHEMA_VALID(@string_schema, '"abc"') +1 +# Validating non-scalar +# Validating array +SET @schema_array= '{"type":"array"}'; +SELECT JSON_SCHEMA_VALID(@schema_array, '[1, 2, {"key1":"val1"}]'); +JSON_SCHEMA_VALID(@schema_array, '[1, 2, {"key1":"val1"}]') +1 +SET @schema_array= '{"type":"array", + "maxItems": 4, + "minItems": 2}'; +SELECT JSON_SCHEMA_VALID(@schema_array, '[1, 2, {"key1":"val1"}]'); +JSON_SCHEMA_VALID(@schema_array, '[1, 2, {"key1":"val1"}]') +1 +SELECT JSON_SCHEMA_VALID(@schema_array, '[1, 2, false, null, "foobar"]'); +JSON_SCHEMA_VALID(@schema_array, '[1, 2, false, null, "foobar"]') +0 +SELECT JSON_SCHEMA_VALID(@schema_array, '[1]'); +JSON_SCHEMA_VALID(@schema_array, '[1]') +0 +SET @schema_array= '{"maxItems": 4, + "minItems": 2}'; +SELECT JSON_SCHEMA_VALID(@schema_array, '[1, 2, {"key1":"val1"}]'); +JSON_SCHEMA_VALID(@schema_array, '[1, 2, {"key1":"val1"}]') +1 +SELECT JSON_SCHEMA_VALID(@schema_array, '[1, 2, false, null, "foobar"]'); +JSON_SCHEMA_VALID(@schema_array, '[1, 2, false, null, "foobar"]') +0 +SELECT JSON_SCHEMA_VALID(@schema_array, '[1, 2]'); +JSON_SCHEMA_VALID(@schema_array, '[1, 2]') +1 +SET @schema_array= '{ + "type":"array", + "items": {"type":"number"}, + "maxItems": 4, + "minItems": 2}'; +SELECT JSON_SCHEMA_VALID(@schema_array, '[1, 2, 3]'); +JSON_SCHEMA_VALID(@schema_array, '[1, 2, 3]') +1 +SELECT JSON_SCHEMA_VALID(@schema_array, '[1, 2, "foobar"]'); +JSON_SCHEMA_VALID(@schema_array, '[1, 2, "foobar"]') +0 +SET @schema_array= '{"type":"array", + "maxItems": 4, + "minItems": 2, + "const": [1, 2, 3, 4]}'; +SELECT JSON_SCHEMA_VALID(@schema_array, '[1, 2, 3, "foobar"]'); +JSON_SCHEMA_VALID(@schema_array, '[1, 2, 3, "foobar"]') +0 +SELECT JSON_SCHEMA_VALID(@schema_array, '[1, 2, 3, 4]'); +JSON_SCHEMA_VALID(@schema_array, '[1, 2, 3, 4]') +1 +SET @schema_array= '{"type":"array", + "enum":[[1,2,3], [4,5,6], [7,8,9]]}'; +SELECT JSON_SCHEMA_VALID(@schema_array, '[4,5,6]'); +JSON_SCHEMA_VALID(@schema_array, '[4,5,6]') +1 +SELECT JSON_SCHEMA_VALID(@schema_array, '[1,5,7]'); +JSON_SCHEMA_VALID(@schema_array, '[1,5,7]') +0 +SET @schema_array= '{ + "type": "array", + "uniqueItems":true + }'; +SELECT JSON_SCHEMA_VALID(@schema_array, '[1, 2, 3, 2.0]'); +JSON_SCHEMA_VALID(@schema_array, '[1, 2, 3, 2.0]') +0 +SELECT JSON_SCHEMA_VALID(@schema_array, '[1, 2, 3, 4.0]'); +JSON_SCHEMA_VALID(@schema_array, '[1, 2, 3, 4.0]') +1 +SET @schema_array= '{ + "type": "array", + "contains": { + "type": "number" + }, + "minContains": 2, + "maxContains": 3 +}'; +SELECT JSON_SCHEMA_VALID(@schema_array, '["string1", "string2", "string3", 1, 2, 3, 4]'); +JSON_SCHEMA_VALID(@schema_array, '["string1", "string2", "string3", 1, 2, 3, 4]') +0 +SELECT JSON_SCHEMA_VALID(@schema_array, '["string1", "string2", "string3", 1, 2, 3]'); +JSON_SCHEMA_VALID(@schema_array, '["string1", "string2", "string3", 1, 2, 3]') +1 +SET @schema_array= '{ + "type": "array", + "prefixItems": [ + { "type": "number", "maximum": 10, "minimum":3}, + { "type": "string" } + ] + }'; +SELECT JSON_SCHEMA_VALID(@schema_array, '[5, "string1",2]'); +JSON_SCHEMA_VALID(@schema_array, '[5, "string1",2]') +1 +SELECT JSON_SCHEMA_VALID(@schema_array, '[2, "string1",2]'); +JSON_SCHEMA_VALID(@schema_array, '[2, "string1",2]') +0 +SET @schema_array= '{ + "type": "array", + "prefixItems": [ + { "type": "number", "maximum": 10, "minimum":3}, + { "type": "string" } + ], + "items":true + }'; +SELECT JSON_SCHEMA_VALID(@schema_array, '[5, "string1",2]'); +JSON_SCHEMA_VALID(@schema_array, '[5, "string1",2]') +1 +SET @schema_array= '{ + "type": "array", + "prefixItems": [ + { "type": "number", "maximum": 10, "minimum":3}, + { "type": "string" } + ], + "items":false + }'; +SELECT JSON_SCHEMA_VALID(@schema_array, '[5, "string1",2]'); +JSON_SCHEMA_VALID(@schema_array, '[5, "string1",2]') +0 +# Checking objects +SET @schema_object= '{"type":"object", + "properties": + { + "number1":{ + "type":"number", + "maximum":12, + "minimum":1 + }, + "string1": { + "type":"string", + "maxLength":10, + "minLength": 4 + }, + "array1": {"type":"array", + "maxItems": 4, + "minItems": 2} + } + }'; +SELECT JSON_SCHEMA_VALID(@schema_object, '{"number1":10, "string1":"foobar","array1":[1,2,3]}'); +JSON_SCHEMA_VALID(@schema_object, '{"number1":10, "string1":"foobar","array1":[1,2,3]}') +1 +SELECT JSON_SCHEMA_VALID(@schema_object, '{"number1":10, "string1":"foobar","array1":[1,2,3,4,5]}'); +JSON_SCHEMA_VALID(@schema_object, '{"number1":10, "string1":"foobar","array1":[1,2,3,4,5]}') +0 +SET @schema_obj= '{ + "type": "object", + "properties": { + "number1":{"type":"number"}, + "string1":{"type":"string"}, + "array1":{"type":"array"} + }, + "dependentRequired": { + "number1":["string1"] + } + }'; +SELECT JSON_SCHEMA_VALID(@schema_obj, '{"array1":[1,2,3], "number1":2, "string1":"abc"}'); +JSON_SCHEMA_VALID(@schema_obj, '{"array1":[1,2,3], "number1":2, "string1":"abc"}') +1 +SELECT JSON_SCHEMA_VALID(@schema_obj, '{"array1":[1,2,3], "number1":2}'); +JSON_SCHEMA_VALID(@schema_obj, '{"array1":[1,2,3], "number1":2}') +0 +SET @schema_obj= '{"type":"object", + "properties": + { + "number1":{ + "type":"number", + "maximum":12, + "minimum":1 + }, + "key1" : { + "type":"object", + "properties": { + "key2" :{ + "type":"string" + } + } + } + }, + "enum": [{"number1":3, "key1":{"key2":"string1"}}, {"number1":5, "key1":{"key2":"string3"}}, {"number1":7, "key1":{"key2":"string5"}}] + }'; +SELECT JSON_SCHEMA_VALID(@schema_obj, '{"number1":5, "key1":{"key2":1}}'); +JSON_SCHEMA_VALID(@schema_obj, '{"number1":5, "key1":{"key2":1}}') +0 +SELECT JSON_SCHEMA_VALID(@schema_obj, '{"number1":5, "key1":{"key2":"string1"}}'); +JSON_SCHEMA_VALID(@schema_obj, '{"number1":5, "key1":{"key2":"string1"}}') +0 +SELECT JSON_SCHEMA_VALID(@schema_obj, '{"number1":5, "key1":{"key2":"string7"}}'); +JSON_SCHEMA_VALID(@schema_obj, '{"number1":5, "key1":{"key2":"string7"}}') +0 +SET @schema_obj= '{"type":"object", + "properties": + { + "number1":{ + "type":"number", + "maximum":12, + "minimum":1 + }, + "obj1" : { + "type":"object", + "properties": { + "obj1_1":{ + "type":"string" + }, + "obj1_2": { + "type":"array" + } + } + }, + "obj2" : { + "type":"object", + "properties" : { + "obj2_1":{ + "type":"number" + } + } + } + }, + "required":["number1", "obj2"] + }'; +SELECT JSON_SCHEMA_VALID(@schema_obj,'{"number1":5, "obj1":{"obj1_1":"string1", "obj1_2":[1, 2, 3]}}'); +JSON_SCHEMA_VALID(@schema_obj,'{"number1":5, "obj1":{"obj1_1":"string1", "obj1_2":[1, 2, 3]}}') +0 +SELECT JSON_SCHEMA_VALID(@schema_obj,'{"number1":5, "obj1":{"obj1_1":"string1", "obj1_2":[1, 2, 3]}, "obj2":{"obj2_1":7}}'); +JSON_SCHEMA_VALID(@schema_obj,'{"number1":5, "obj1":{"obj1_1":"string1", "obj1_2":[1, 2, 3]}, "obj2":{"obj2_1":7}}') +1 +SET @schema_obj= '{"type":"object", + "properties": + { + "number1":{ + "type":"number", + "maximum":12, + "minimum":1 + }, + "obj1" : { + "type":"object", + "properties": { + "obj1_1":{ + "type":"string" + }, + "obj1_2": { + "type":"array" + } + } + }, + "obj2" : { + "type":"object", + "properties" : { + "obj2_1":{ + "type":"number" + } + } + } + }, + "required":["number1", "obj2"], + "const": {"number1":5, "obj1":{"obj1_1":"string1", "obj1_2":[1, 2, 3]}, "obj2":{"obj2_1":7}} + }'; +SELECT JSON_SCHEMA_VALID(@schema_obj,'{"number1":5, "obj1":{"obj1_1":"string1", "obj1_2":[1, 2, 3]}, "obj2":{"obj2_1":7}}'); +JSON_SCHEMA_VALID(@schema_obj,'{"number1":5, "obj1":{"obj1_1":"string1", "obj1_2":[1, 2, 3]}, "obj2":{"obj2_1":7}}') +1 +SELECT JSON_SCHEMA_VALID(@schema_obj,'{"number1":5, "obj1":{"obj1_1":"string1", "obj1_2":[1, 2, 3]}}'); +JSON_SCHEMA_VALID(@schema_obj,'{"number1":5, "obj1":{"obj1_1":"string1", "obj1_2":[1, 2, 3]}}') +0 +SET @schema_obj= '{"type":"object", + "properties": + { + "number1":{ + "type":"number", + "maximum":12, + "minimum":1 + }, + "obj1" : { + "type":"object", + "properties": { + "obj1_1":{ + "type":"string" + }, + "obj1_2": { + "type":"array" + } + } + }, + "obj2" : { + "type":"object", + "properties" : { + "obj2_1":{ + "type":"number" + } + } + } + }, + "maxProperties": 5, + "minProperties":2 + }'; +SELECT JSON_SCHEMA_VALID(@schema_obj,'{"number1":5, "obj1":{"obj1_1":"string1", "obj1_2":[1, 2, 3]}, "obj2":{"obj2_1":7}}'); +JSON_SCHEMA_VALID(@schema_obj,'{"number1":5, "obj1":{"obj1_1":"string1", "obj1_2":[1, 2, 3]}, "obj2":{"obj2_1":7}}') +1 +SELECT JSON_SCHEMA_VALID(@schema_obj,'{"number1":1, "number2":2, "number3":3, "number4":4, "number5":5, "number6":6}'); +JSON_SCHEMA_VALID(@schema_obj,'{"number1":1, "number2":2, "number3":3, "number4":4, "number5":5, "number6":6}') +0 +SET @schema_obj= '{"type":"object", + "properties": + { + "number1":{ + "type":"number", + "maximum":12, + "minimum":1 + }, + "obj1" : { + "type":"object", + "properties": { + "obj1_1":{ + "type":"string" + }, + "obj1_2": { + "type":"array" + } + } + } + }, + "maxProperties": 3, + "minProperties":1, + "additionalProperties":false + }'; +SELECT JSON_SCHEMA_VALID(@schema_obj,'{"number1":5, "obj1":{"obj1_1":"string1", "obj1_2":[1, 2, 3]}, "obj2":"string2"}'); +JSON_SCHEMA_VALID(@schema_obj,'{"number1":5, "obj1":{"obj1_1":"string1", "obj1_2":[1, 2, 3]}, "obj2":"string2"}') +0 +# Demonstrating that irrelavent keywords for a type and irrelavent type +# are ignored, and none of the keywords are mandatory, including "type". +SET @schema_properties= '{ + "properties" : { + "number1":{ "maximum":10 }, + "string1" : { "maxLength": 3} + } + }'; +SELECT JSON_SCHEMA_VALID(@schema_properties, '{ "number1":25, "string1":"ab" }'); +JSON_SCHEMA_VALID(@schema_properties, '{ "number1":25, "string1":"ab" }') +0 +SELECT JSON_SCHEMA_VALID(@schema_properties, '{ "number1":10, "string1":"ab" }'); +JSON_SCHEMA_VALID(@schema_properties, '{ "number1":10, "string1":"ab" }') +1 +SET @schema_properties= '{ + "properties" : { + "number1":{ "maximum":10 }, + "string1" : { "maxLength": 3}, + "obj1" : { + "properties":{ + "number2": {"minimum":8}, + "array2": {"uniqueItems":true} + } + } + } + }'; +SELECT JSON_SCHEMA_VALID(@schema_properties, '{ "number1":2, "string1":"ab", "obj1":{"number2":2, "array2":[1,2,3]} }'); +JSON_SCHEMA_VALID(@schema_properties, '{ "number1":2, "string1":"ab", "obj1":{"number2":2, "array2":[1,2,3]} }') +0 +SELECT JSON_SCHEMA_VALID(@schema_properties, '{ "number1":2, "string1":"ab", "obj1":{"number2":10, "array2":[1,2,3]} }'); +JSON_SCHEMA_VALID(@schema_properties, '{ "number1":2, "string1":"ab", "obj1":{"number2":10, "array2":[1,2,3]} }') +1 +SELECT JSON_SCHEMA_VALID(@schema_properties, '{ "number1":2, "string1":"ab", "obj1":{"array2":[1,2,3]} }'); +JSON_SCHEMA_VALID(@schema_properties, '{ "number1":2, "string1":"ab", "obj1":{"array2":[1,2,3]} }') +1 +SELECT JSON_SCHEMA_VALID(@schema_properties, '{ "number1":2, "string1":"ab", "obj1":{"number2":10, "array2":[1,2,3,2]} }'); +JSON_SCHEMA_VALID(@schema_properties, '{ "number1":2, "string1":"ab", "obj1":{"number2":10, "array2":[1,2,3,2]} }') +0 +SET @schema_num= '{ + "maximum":10, + "minimum":2 + }'; +SELECT JSON_SCHEMA_VALID(@schema_num, '5'); +JSON_SCHEMA_VALID(@schema_num, '5') +1 +SELECT JSON_SCHEMA_VALID(@schema_num, '"abc"'); +JSON_SCHEMA_VALID(@schema_num, '"abc"') +1 +SET @schema_str= '{ + "maxLength":5, + "minLength":2, + "pattern":"a+bc" + }'; +SELECT JSON_SCHEMA_VALID(@schema_str, '"abc"'); +JSON_SCHEMA_VALID(@schema_str, '"abc"') +1 +SELECT JSON_SCHEMA_VALID(@schema_str, '"abcadef"'); +JSON_SCHEMA_VALID(@schema_str, '"abcadef"') +0 +SELECT JSON_SCHEMA_VALID(@schema_str, '"bc"'); +JSON_SCHEMA_VALID(@schema_str, '"bc"') +0 +SET @schema_arr= '{ + "uniqueItems":true, + "items":{"type":"string"}, + "maximum":10 + }'; +SELECT JSON_SCHEMA_VALID(@schema_arr,'["abc", "bcd", "cde"]'); +JSON_SCHEMA_VALID(@schema_arr,'["abc", "bcd", "cde"]') +1 +SELECT JSON_SCHEMA_VALID(@schema_arr,'["abc", "bcd", "abc"]'); +JSON_SCHEMA_VALID(@schema_arr,'["abc", "bcd", "abc"]') +0 +SELECT JSON_SCHEMA_VALID(@schema_arr,'["abc", "bcd", 1]'); +JSON_SCHEMA_VALID(@schema_arr,'["abc", "bcd", 1]') +0 +SET @schema_const1= '{"const":2}'; +SELECT JSON_SCHEMA_VALID(@schema_const1, '2'); +JSON_SCHEMA_VALID(@schema_const1, '2') +1 +SELECT JSON_SCHEMA_VALID(@schema_const1, '"abc"'); +JSON_SCHEMA_VALID(@schema_const1, '"abc"') +0 +SET @schema_const2= '{"const":true}'; +SELECT JSON_SCHEMA_VALID(@schema_const2,'true'); +JSON_SCHEMA_VALID(@schema_const2,'true') +1 +SELECT JSON_SCHEMA_VALID(@schema_const2,'false'); +JSON_SCHEMA_VALID(@schema_const2,'false') +0 +SET @schema_enum='{"enum":[1,2,3,"abc", [4,5,6]]}'; +SELECT JSON_SCHEMA_VALID(@schema_enum,'[4,5,6]'); +JSON_SCHEMA_VALID(@schema_enum,'[4,5,6]') +1 +SELECT JSON_SCHEMA_VALID(@schema_enum,'4'); +JSON_SCHEMA_VALID(@schema_enum,'4') +0 +SELECT JSON_SCHEMA_VALID(@schema_enum,'"abc"'); +JSON_SCHEMA_VALID(@schema_enum,'"abc"') +1 +SET @schema_required='{"required":["num1","str1", "arr1"]}'; +SELECT JSON_SCHEMA_VALID(@schema_required,'{"num1":1, "str1":"abc", "arr1":[1,2,3]}'); +JSON_SCHEMA_VALID(@schema_required,'{"num1":1, "str1":"abc", "arr1":[1,2,3]}') +1 +SELECT JSON_SCHEMA_VALID(@schema_required,'{"num1":1, "arr1":[1,2,3]}'); +JSON_SCHEMA_VALID(@schema_required,'{"num1":1, "arr1":[1,2,3]}') +0 +SELECT JSON_SCHEMA_VALID(@schema_required,'"abcd"'); +JSON_SCHEMA_VALID(@schema_required,'"abcd"') +1 +SET @schema_dep_required='{ + "dependentRequired": { + "num1":["num2","num3"], + "str1":["str2","str3"] + } + }'; +SELECT JSON_SCHEMA_VALID(@schema_dep_required,'{"num1":1, "num2":"abc", "num3":[1,2,3]}'); +JSON_SCHEMA_VALID(@schema_dep_required,'{"num1":1, "num2":"abc", "num3":[1,2,3]}') +1 +SELECT JSON_SCHEMA_VALID(@schema_dep_required,'{"num1":1, "num2":"abc", "num3":[1,2,3], "str1":"abcd"}'); +JSON_SCHEMA_VALID(@schema_dep_required,'{"num1":1, "num2":"abc", "num3":[1,2,3], "str1":"abcd"}') +0 +SELECT JSON_SCHEMA_VALID(@schema_dep_required,'{"num1":1, "num2":"abc", "num3":[1,2,3], "arr1":[1,2,3]}'); +JSON_SCHEMA_VALID(@schema_dep_required,'{"num1":1, "num2":"abc", "num3":[1,2,3], "arr1":[1,2,3]}') +1 +# Checking syntax error +SET @invalid_schema= '{"type":"object" + "properties":{ + "number1": {"type":"number"}, + "obj2": {"type":"object", + "properties": { + "key1": {"type":"number"} + } + } + } + }'; +SELECT JSON_SCHEMA_VALID(@invalid_schema, '{"number1":3, "obj2":{"key1":3}}'); +JSON_SCHEMA_VALID(@invalid_schema, '{"number1":3, "obj2":{"key1":3}}') +1 +Warnings: +Warning 4038 Syntax error in JSON text in argument 2 to function 'json_schema_valid' at position 45 +SET @invalid_json= '{"type":"array", + "maxItems": 4, + "minItems": 2, + "const": [1, 2, 3, 4]}'; +SELECT JSON_SCHEMA_VALID(@schema_array, '[1, 2, 3, 4'); +JSON_SCHEMA_VALID(@schema_array, '[1, 2, 3, 4') +0 +SET @schema_string= '{ + "type": "string", + "maxLength":-2 + }'; +SELECT JSON_SCHEMA_VALID(@schema_string, '"abcxyz"'); +ERROR HY000: Invalid value for keyword maxLength +SET @schema_number= '{ + "type": "number", + "multipleOf":-3 + }'; +SELECT JSON_SCHEMA_VALID(@schema_number, '3'); +ERROR HY000: Invalid value for keyword multipleOf +SET @schema_object= '{ + "type": "object", + "properties":{"num1":{"type":"number"}}, + "required":{} + }'; +SELECT JSON_SCHEMA_VALID(@schema_object, '{"num1":2}'); +ERROR HY000: Invalid value for keyword required +SET @schema_string= '{ + "type": "string", + "maxLength":-10 + }'; +SELECT JSON_SCHEMA_VALID(@schema_string, '"str1"'); +ERROR HY000: Invalid value for keyword maxLength +SET @schema_number= '{"type":"numberz"}'; +SELECT JSON_SCHEMA_VALID(@schema_number, '"string1"'); +ERROR HY000: Invalid value for keyword type +# Using JSON_SCHEMA_VALID() as a constraint validation to insert into table +CREATE TABLE str_table (val_str JSON CHECK(JSON_SCHEMA_VALID('{ + "type":"string", + "maxLength":5, + "minLength":2, + "enum":["ab", "cd", "abc", "def", "abcdef"] + }', val_str))); +CREATE TABLE num_table(val_num JSON CHECK(JSON_SCHEMA_VALID('{ + "type":"number", + "minimum":10, + "maximum":30, + "exclusiveMinimum":11, + "exclusiveMaximum":29, + "multipleOf":5, + "const":15 + }', val_num))); +CREATE TABLE true_table(val_true JSON CHECK(JSON_SCHEMA_VALID('{ + "type":"boolean", + "enum":[true, false, null] + }', val_true))); +CREATE TABLE false_table (val_false JSON CHECK(JSON_SCHEMA_VALID('{ + "type":"boolean" + }', val_false))); +CREATE TABLE null_table (val_null JSON CHECK(JSON_SCHEMA_VALID('{ + "type":"null" + }', val_null))); +CREATE TABLE arr_table (val_arr JSON CHECK(JSON_SCHEMA_VALID('{ + "type":"array", + "uniqueItems":true, + "maxItems":5, + "minItems":1, + "items":true, + "prefixItems":[{"type":"number"}] + }', val_arr))); +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 str_table VALUES ('"ab"'), ('"cd"'), ('"abc"'), ('"def"'); +INSERT INTO str_table VALUES ('"feb"'); +ERROR 23000: CONSTRAINT `str_table.val_str` failed for `test`.`str_table` +INSERT INTO str_table VALUES('"abcdef"'); +ERROR 23000: CONSTRAINT `str_table.val_str` failed for `test`.`str_table` +INSERT INTO str_table VALUES('"fedcba"'); +ERROR 23000: CONSTRAINT `str_table.val_str` failed for `test`.`str_table` +SELECT * FROM str_table; +val_str +"ab" +"cd" +"abc" +"def" +INSERT INTO num_table values('15'); +INSERT INTO num_table values('25'); +ERROR 23000: CONSTRAINT `num_table.val_num` failed for `test`.`num_table` +SELECT * FROM num_table; +val_num +15 +INSERT INTO true_table VALUES ('true'); +SELECT * FROM true_table; +val_true +true +INSERT INTO false_table VALUES('false'); +SELECT * FROM false_table; +val_false +false +INSERT INTO arr_table VALUES ('[10, 2, "abc"]'); +INSERT INTO arr_table VALUES('[100]'); +INSERT INTO arr_table VALUES ('["str1", 2, "abc", 2.0]'); +ERROR 23000: CONSTRAINT `arr_table.val_arr` failed for `test`.`arr_table` +SELECT * FROM arr_table; +val_arr +[10, 2, "abc"] +[100] +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 23000: CONSTRAINT `obj_table.val_obj` failed for `test`.`obj_table` +INSERT INTO obj_table VALUES('{"number1":3, "string1":"abcd"}'); +ERROR 23000: CONSTRAINT `obj_table.val_obj` failed for `test`.`obj_table` +INSERT INTO obj_table VALUES('{"number1":3, "string1":"abcd", "object1":{"key1":"val1", "key2":[1,2,3, "string1"]}'); +ERROR 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}} +DROP TABLE str_table, num_table, true_table, false_table, null_table, arr_table, obj_table; +# array validation +SET @schema_array= '{ + "type": "array", + "prefixItems": [ + { "type": "number", "maximum": 10, "minimum":3}, + { "type": "string" } + ], + "additionalItems" : {"type":"number"}, + "unevaluatedItems": {"type":"string"}, + "items":{"type":"array"} + }'; +SELECT JSON_SCHEMA_VALID(@schema_array, '[5, "string1", [1,2], "2", "string"]'); +JSON_SCHEMA_VALID(@schema_array, '[5, "string1", [1,2], "2", "string"]') +0 +SELECT JSON_SCHEMA_VALID(@schema_array, '[5, "string1", "2", "string"]'); +JSON_SCHEMA_VALID(@schema_array, '[5, "string1", "2", "string"]') +0 +SELECT JSON_SCHEMA_VALID(@schema_array, '[5, "string1", [1,2], [1,2]]'); +JSON_SCHEMA_VALID(@schema_array, '[5, "string1", [1,2], [1,2]]') +1 +SET @schema_array= '{ + "type": "array", + "prefixItems": [ + { "type": "number", "maximum": 10, "minimum":3}, + { "type": "string" } + ], + "additionalItems" : {"type":"number"}, + "unevaluatedItems": {"type":"string"}, + "items": true + }'; +SELECT JSON_SCHEMA_VALID(@schema_array, '[5, "string1", [1,2], "2", "string"]'); +JSON_SCHEMA_VALID(@schema_array, '[5, "string1", [1,2], "2", "string"]') +1 +SET @schema_array= '{ + "type": "array", + "prefixItems": [ + { "type": "number", "maximum": 10, "minimum":3}, + { "type": "string" } + ], + "additionalItems" : {"type":"number"}, + "unevaluatedItems": {"type":"string"}, + "items": false + }'; +SELECT JSON_SCHEMA_VALID(@schema_array, '[5, "string1", [1,2], "2", "string"]'); +JSON_SCHEMA_VALID(@schema_array, '[5, "string1", [1,2], "2", "string"]') +0 +SET @schema_array= '{ + "type": "array", + "prefixItems": [ + { "type": "number", "maximum": 10, "minimum":3}, + { "type": "string" } + ], + "additionalItems" : {"type":"number"}, + "unevaluatedItems": {"type":"string"}, + "items": [{"type":"string"}] + }'; +SELECT JSON_SCHEMA_VALID(@schema_array, '[5, "string1", [1,2], "2", "string"]'); +ERROR HY000: Invalid value for keyword items +# Removing items +SET @schema_array= '{ + "type": "array", + "prefixItems": [ + { "type": "number", "maximum": 10, "minimum":3}, + { "type": "string" } + ], + "additionalItems" : {"type":"number"}, + "unevaluatedItems": {"type":"string"} + }'; +SELECT JSON_SCHEMA_VALID(@schema_array, '[5, "string1", [1,2], "2", "string"]'); +JSON_SCHEMA_VALID(@schema_array, '[5, "string1", [1,2], "2", "string"]') +0 +SELECT JSON_SCHEMA_VALID(@schema_array, '[5, "string1", 1, 2, 3]'); +JSON_SCHEMA_VALID(@schema_array, '[5, "string1", 1, 2, 3]') +1 +SET @schema_array= '{ + "type": "array", + "prefixItems": [ + { "type": "number", "maximum": 10, "minimum":3}, + { "type": "string" } + ], + "additionalItems" : true, + "unevaluatedItems": {"type":"string"} + }'; +SELECT JSON_SCHEMA_VALID(@schema_array, '[5, "string1", 1, 2, 3]'); +JSON_SCHEMA_VALID(@schema_array, '[5, "string1", 1, 2, 3]') +1 +SET @schema_array= '{ + "type": "array", + "prefixItems": [ + { "type": "number", "maximum": 10, "minimum":3}, + { "type": "string" } + ], + "additionalItems" : false, + "unevaluatedItems": {"type":"string"} + }'; +SELECT JSON_SCHEMA_VALID(@schema_array, '[5, "string1", 1, 2, 3]'); +JSON_SCHEMA_VALID(@schema_array, '[5, "string1", 1, 2, 3]') +0 +# Using items in place of additionalItems +SET @schema_array= '{ + "type": "array", + "prefixItems": [ + { "type": "number", "maximum": 10, "minimum":3}, + { "type": "string" } + ], + "items" : false, + "unevaluatedItems": {"type":"string"} + }'; +SELECT JSON_SCHEMA_VALID(@schema_array, '[5, "string1", 1, 2, 3]'); +JSON_SCHEMA_VALID(@schema_array, '[5, "string1", 1, 2, 3]') +0 +SET @schema_array= '{ + "type": "array", + "prefixItems": [ + { "type": "number", "maximum": 10, "minimum":3}, + { "type": "string" } + ], + "items" : true, + "unevaluatedItems": {"type":"string"} + }'; +SELECT JSON_SCHEMA_VALID(@schema_array, '[5, "string1", 1, 2, 3]'); +JSON_SCHEMA_VALID(@schema_array, '[5, "string1", 1, 2, 3]') +1 +SET @schema_array= '{ + "type": "array", + "prefixItems": [ + { "type": "number", "maximum": 10, "minimum":3}, + { "type": "string" } + ], + "items" : {"type":"number"}, + "unevaluatedItems": {"type":"string"} + }'; +SELECT JSON_SCHEMA_VALID(@schema_array, '[5, "string1", 1, 2, 3]'); +JSON_SCHEMA_VALID(@schema_array, '[5, "string1", 1, 2, 3]') +1 +SET @schema_array= '{ + "type": "array", + "prefixItems": [ + { "type": "number", "maximum": 10, "minimum":3}, + { "type": "string" } + ], + "items" : {"type":"number"}, + "unevaluatedItems": {"type":"string"} + }'; +SELECT JSON_SCHEMA_VALID(@schema_array, '[5, "string1", "string2", "string3"]'); +JSON_SCHEMA_VALID(@schema_array, '[5, "string1", "string2", "string3"]') +0 +# Removing items and additionalItems both +SET @schema_array= '{ + "type": "array", + "prefixItems": [ + { "type": "number", "maximum": 10, "minimum":3}, + { "type": "string" } + ], + "unevaluatedItems": {"type":"number"} + }'; +SELECT JSON_SCHEMA_VALID(@schema_array, '[5, "string1", "string2", "string3"]'); +JSON_SCHEMA_VALID(@schema_array, '[5, "string1", "string2", "string3"]') +0 +SELECT JSON_SCHEMA_VALID(@schema_array, '[5, "string1", 1, 2]'); +JSON_SCHEMA_VALID(@schema_array, '[5, "string1", 1, 2]') +1 +SET @schema_array= '{ + "type": "array", + "prefixItems": [ + { "type": "number", "maximum": 10, "minimum":3}, + { "type": "string" } + ], + "unevaluatedItems": true + }'; +SELECT JSON_SCHEMA_VALID(@schema_array, '[5, "string1", "string2", "string3"]'); +JSON_SCHEMA_VALID(@schema_array, '[5, "string1", "string2", "string3"]') +1 +SET @schema_array= '{ + "type": "array", + "prefixItems": [ + { "type": "number", "maximum": 10, "minimum":3}, + { "type": "string" } + ], + "unevaluatedItems": false + }'; +SELECT JSON_SCHEMA_VALID(@schema_array, '[5, "string1", "string2", "string3"]'); +JSON_SCHEMA_VALID(@schema_array, '[5, "string1", "string2", "string3"]') +0 +# Removing items, additionalItems, unevaluatedItems +SET @schema_array= '{ + "type": "array", + "prefixItems": [ + { "type": "number", "maximum": 10, "minimum":3}, + { "type": "string" } + ] + }'; +SELECT JSON_SCHEMA_VALID(@schema_array, '[5, "string1", "string2", "string3"]'); +JSON_SCHEMA_VALID(@schema_array, '[5, "string1", "string2", "string3"]') +1 +# Removing prefixItems +SET @schema_array= '{ + "type": "array", + "items": { "type": "number", "maximum": 10, "minimum":3}, + "additionalItems" : {"type":"number"}, + "unevaluatedItems": {"type":"string"} + }'; +SELECT JSON_SCHEMA_VALID(@schema_array, '[5, 6,"2", "string"]'); +JSON_SCHEMA_VALID(@schema_array, '[5, 6,"2", "string"]') +0 +SET @schema_array= '{ + "type": "array", + "items": { "type": "number", "maximum": 10, "minimum":3}, + "additionalItems" : {"type":"string"}, + "unevaluatedItems": {"type":"string"} + }'; +SELECT JSON_SCHEMA_VALID(@schema_array, '[5, 6,"2", "string"]'); +JSON_SCHEMA_VALID(@schema_array, '[5, 6,"2", "string"]') +0 +SET @schema_array= '{ + "type": "array", + "items": true, + "additionalItems" : {"type":"string"}, + "unevaluatedItems": {"type":"string"} + }'; +SELECT JSON_SCHEMA_VALID(@schema_array, '[5, 6,"2", "string"]'); +JSON_SCHEMA_VALID(@schema_array, '[5, 6,"2", "string"]') +1 +SET @schema_array= '{ + "type": "array", + "items": false, + "additionalItems" : {"type":"string"}, + "unevaluatedItems": {"type":"string"} + }'; +SELECT JSON_SCHEMA_VALID(@schema_array, '[5, 6,"2", "string"]'); +JSON_SCHEMA_VALID(@schema_array, '[5, 6,"2", "string"]') +0 +SELECT JSON_SCHEMA_VALID(@schema_array, '[]'); +JSON_SCHEMA_VALID(@schema_array, '[]') +1 +# Removing prefixItems and additionalItems +SET @schema_array= '{ + "type": "array", + "items": {"type":"string"}, + "unevaluatedItems": {"type":"string"} + }'; +SELECT JSON_SCHEMA_VALID(@schema_array, '["str1", "str2", 2]'); +JSON_SCHEMA_VALID(@schema_array, '["str1", "str2", 2]') +0 +SELECT JSON_SCHEMA_VALID(@schema_array, '["str1", "str2"]'); +JSON_SCHEMA_VALID(@schema_array, '["str1", "str2"]') +1 +SET @schema_array= '{ + "type": "array", + "items": {"type":"string"}, + "unevaluatedItems": {"type":"number"} + }'; +SELECT JSON_SCHEMA_VALID(@schema_array, '["str1", "str2", 2]'); +JSON_SCHEMA_VALID(@schema_array, '["str1", "str2", 2]') +0 +# removing prefixItems, additionalItems and unevaluatedItems +SET @schema_array= '{ + "type": "array", + "items": {"type":"string"} + }'; +SELECT JSON_SCHEMA_VALID(@schema_array, '["str1", "str2", 2]'); +JSON_SCHEMA_VALID(@schema_array, '["str1", "str2", 2]') +0 +SELECT JSON_SCHEMA_VALID(@schema_array, '["str1", "str2"]'); +JSON_SCHEMA_VALID(@schema_array, '["str1", "str2"]') +1 +# Checking that additionalItems alone has no effect on schema without items/prefixItems +# regardless existence of unevaluatedItems +SET @schema_array= '{ + "type": "array", + "additionalItems": {"type":"string"}, + "unevaluatedItems": {"type":"number"} + }'; +SELECT JSON_SCHEMA_VALID(@schema_array, '["str1", "str2", 1]'); +JSON_SCHEMA_VALID(@schema_array, '["str1", "str2", 1]') +1 +SET @schema_array= '{ + "type": "array", + "additionalItems": true, + "unevaluatedItems": {"type":"number"} + }'; +SELECT JSON_SCHEMA_VALID(@schema_array, '["str1", "str2", 1]'); +JSON_SCHEMA_VALID(@schema_array, '["str1", "str2", 1]') +1 +SET @schema_array= '{ + "type": "array", + "additionalItems": false, + "unevaluatedItems": {"type":"number"} + }'; +SELECT JSON_SCHEMA_VALID(@schema_array, '["str1", "str2", 1]'); +JSON_SCHEMA_VALID(@schema_array, '["str1", "str2", 1]') +1 +# checking that unevaluatedItems alone can have effect on schema validation +SET @schema_array= '{ + "type": "array", + "unevaluatedItems": {"type":"number"} + }'; +SELECT JSON_SCHEMA_VALID(@schema_array, '[1]'); +JSON_SCHEMA_VALID(@schema_array, '[1]') +1 +SET @schema_array= '{ + "type": "array", + "unevaluatedItems": {"type":"number"} + }'; +SELECT JSON_SCHEMA_VALID(@schema_array, '[1, "str1"]'); +JSON_SCHEMA_VALID(@schema_array, '[1, "str1"]') +0 +SET @schema_array= '{ + "type": "array", + "unevaluatedItems": false + }'; +SELECT JSON_SCHEMA_VALID(@schema_array, '[1, "str1"]'); +JSON_SCHEMA_VALID(@schema_array, '[1, "str1"]') +0 +SELECT JSON_SCHEMA_VALID(@schema_array, '[]'); +JSON_SCHEMA_VALID(@schema_array, '[]') +1 +SELECT JSON_SCHEMA_VALID(@schema_array, '[1,2,3]'); +JSON_SCHEMA_VALID(@schema_array, '[1,2,3]') +0 +# Object validation +SET @property_names= '{ + "PropertyNames":{ + "pattern": "^I_" + } + }'; +SELECT JSON_SCHEMA_VALID(@property_names, '{"I_int1":3, "O_ob1":{"key1":"val1"}}'); +JSON_SCHEMA_VALID(@property_names, '{"I_int1":3, "O_ob1":{"key1":"val1"}}') +0 +SELECT JSON_SCHEMA_VALID(@property_names, '{"I_int1":3, "I_ob1":{"key1":"val1"}}'); +JSON_SCHEMA_VALID(@property_names, '{"I_int1":3, "I_ob1":{"key1":"val1"}}') +1 +# checking that when a match is found in properties or patternProperties, it must validate and +# validation result affects the schema. If key is not found in properties or patternProperties, and +# additionalProperties exists, it must validate regardless of existence or value for unevaluatedProperties +# and the result of validation with additionalProperties affects result of whole schema +SET @object_schema= '{ + "type":"object", + "properties": { + "key1":{"type":"string"}, + "key2":{"type":"number", "maximum":50} + }, + "patternProperties": { + "^I_": {"type":"number", "maximum":100}, + "^S_" : {"type":"string", "maxLength":4} + }, + "additionalProperties":{ + "type":"array", + "maxItems":5 + }, + "unevaluatedProperties":{"type":"string"} + }'; +SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":20, "S_":"abc", "some_prop1":[1,2,3]}'); +JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":20, "S_":"abc", "some_prop1":[1,2,3]}') +1 +SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": [1,2,3], "I_int":20, "S_":"abc", "some_prop1":[1,2,3]}'); +JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": [1,2,3], "I_int":20, "S_":"abc", "some_prop1":[1,2,3]}') +0 +SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":[1,2,3], "S_":"abc", "some_prop1":[1,2,3]}'); +JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":[1,2,3], "S_":"abc", "some_prop1":[1,2,3]}') +0 +SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":20, "S_":"abc", "some_prop1":"string1"}'); +JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":20, "S_":"abc", "some_prop1":"string1"}') +0 +# removing patternProperties to check that validation falls back on additionalProperties and +# existence of unevaluatedProperties still does not change anything because of existence of additional +# properties +SET @object_schema= '{ + "type":"object", + "properties": { + "key1":{"type":"string"}, + "key2":{"type":"number", "maximum":50} + }, + "additionalProperties":{ + "type":"array", + "maxItems":5 + }, + "unevaluatedProperties":{"type":"string"} + }'; +SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":20, "S_":"abc", "some_prop1":[1,2,3]}'); +JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":20, "S_":"abc", "some_prop1":[1,2,3]}') +0 +SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":[1,2,3], "S_":[1,2,3], "some_prop1":[1,2,3]}'); +JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":[1,2,3], "S_":[1,2,3], "some_prop1":[1,2,3]}') +1 +# Remvoing additionalProperties to check that validation falls back on unevaluatedProperties +SET @object_schema= '{ + "type":"object", + "properties": { + "key1":{"type":"string"}, + "key2":{"type":"number", "maximum":50} + }, + "patternProperties": { + "^I_": {"type":"number", "maximum":100}, + "^S_" : {"type":"string", "maxLength":4} + }, + "unevaluatedProperties":{"type":"string"} + }'; +SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":10, "S_":"abc", "some_prop1":[1,2,3]}'); +JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":10, "S_":"abc", "some_prop1":[1,2,3]}') +0 +SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":10, "S_":"abc", "some_prop1":"str"}'); +JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":10, "S_":"abc", "some_prop1":"str"}') +1 +# Removing unevaluatedProperties has no effect on result when additionalProperties is present +SET @object_schema= '{ + "type":"object", + "properties": { + "key1":{"type":"string"}, + "key2":{"type":"number", "maximum":50} + }, + "patternProperties": { + "^I_": {"type":"number", "maximum":100}, + "^S_" : {"type":"string", "maxLength":4} + }, + "additionalProperties":{ + "type":"array", + "maxItems":5 + } + }'; +SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":20, "S_":"abc", "some_prop1":[1,2,3]}'); +JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":20, "S_":"abc", "some_prop1":[1,2,3]}') +1 +SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":20, "S_":"abc", "some_prop1":"str1"}'); +JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":20, "S_":"abc", "some_prop1":"str1"}') +0 +SET @object_schema= '{ + "type":"object", + "properties": { + "key1":{"type":"string"}, + "key2":{"type":"number", "maximum":50} + }, + "patternProperties": { + "^I_": {"type":"number", "maximum":100}, + "^S_" : {"type":"string", "maxLength":4} + }, + "additionalProperties": false + }'; +SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":20, "S_":"abc", "some_prop1":[1,2,3]}'); +JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":20, "S_":"abc", "some_prop1":[1,2,3]}') +0 +SET @object_schema= '{ + "type":"object", + "properties": { + "key1":{"type":"string"}, + "key2":{"type":"number", "maximum":50} + }, + "patternProperties": { + "^I_": {"type":"number", "maximum":100}, + "^S_" : {"type":"string", "maxLength":4} + }, + "additionalProperties": true + }'; +SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":20, "S_":"abc", "some_prop1":[1,2,3]}'); +JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":20, "S_":"abc", "some_prop1":[1,2,3]}') +1 +# Checking that in absence of additionalProperties, validation falls back on evaluatedProperties +SET @object_schema= '{ + "type":"object", + "properties": { + "key1":{"type":"string"}, + "key2":{"type":"number", "maximum":50} + }, + "patternProperties": { + "^I_": {"type":"number", "maximum":100}, + "^S_" : {"type":"string", "maxLength":4} + }, + "unevaluatedProperties":{"type":"string"} + }'; +SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 3, "I_int":20, "S_":"abc", "some_prop1":"str1"}'); +JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 3, "I_int":20, "S_":"abc", "some_prop1":"str1"}') +1 +SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 3, "I_int":20, "S_":"abc", "some_prop1":[1,2,3]}'); +JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 3, "I_int":20, "S_":"abc", "some_prop1":[1,2,3]}') +0 +SET @object_schema= '{ + "type":"object", + "properties": { + "key1":{"type":"string"}, + "key2":{"type":"number", "maximum":50} + }, + "unevaluatedProperties":{"type":"string"} + }'; +SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 3, "I_int":"str2", "S_":"abc", "some_prop1":[1,2,3]}'); +JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 3, "I_int":"str2", "S_":"abc", "some_prop1":[1,2,3]}') +0 +SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 3, "I_int":20, "S_":"abc", "some_prop1":[1,2,3]}'); +JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 3, "I_int":20, "S_":"abc", "some_prop1":[1,2,3]}') +0 +# Properties to check if patternProperties get evaluated but keys not found in patternProperties get evaluated against +# additionalProperties regardless of existence of unevaluatedProperperties +SET @object_schema= '{ + "type":"object", + "patternProperties": { + "^I_": {"type":"number", "maximum":100}, + "^S_" : {"type":"string", "maxLength":4} + }, + "additionalProperties":{ + "type":"array", + "maxItems":5 + }, + "unevaluatedProperties":{"type":"string"} + }'; +SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":20, "S_":"abc", "some_prop1":[1,2,3]}'); +JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":20, "S_":"abc", "some_prop1":[1,2,3]}') +0 +SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":[1, 2, 3], "key2": [1, 2, 3], "I_int":20, "S_":"abc", "some_prop1":[1,2,3]}'); +JSON_SCHEMA_VALID(@object_schema, '{"key1":[1, 2, 3], "key2": [1, 2, 3], "I_int":20, "S_":"abc", "some_prop1":[1,2,3]}') +1 +SET @object_schema= '{ + "type":"object", + "patternProperties": { + "^I_": {"type":"number", "maximum":100}, + "^S_" : {"type":"string", "maxLength":4} + }, + "additionalProperties":{ + "type":"array", + "maxItems":5 + } + }'; +SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":20, "S_":"abc", "some_prop1":[1,2,3]}'); +JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":20, "S_":"abc", "some_prop1":[1,2,3]}') +0 +SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":[1, 2, 3], "key2": [1, 2, 3], "I_int":20, "S_":"abc", "some_prop1":[1,2,3]}'); +JSON_SCHEMA_VALID(@object_schema, '{"key1":[1, 2, 3], "key2": [1, 2, 3], "I_int":20, "S_":"abc", "some_prop1":[1,2,3]}') +1 +# Checking that in absence of additionalProperties and properties, the keys not found in patternProperties are +# evaluated against unevaluatedProperties +SET @object_schema= '{ + "type":"object", + "patternProperties": { + "^I_": {"type":"number", "maximum":100}, + "^S_" : {"type":"string", "maxLength":4} + }, + "unevaluatedProperties":{"type":"string"} + }'; +SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":20, "S_":"abc", "some_prop1":[1,2,3]}'); +JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":20, "S_":"abc", "some_prop1":[1,2,3]}') +0 +SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": "str2", "I_int":20, "S_":"abc", "some_prop1":"str1"}'); +JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": "str2", "I_int":20, "S_":"abc", "some_prop1":"str1"}') +1 +# checking that in absence of properties, additionalProperties and unevaluatedPropoerties, the keys that are +# not found are considered validated. +SET @object_schema= '{ + "type":"object", + "patternProperties": { + "^I_": {"type":"number", "maximum":100}, + "^S_" : {"type":"string", "maxLength":4} + } + }'; +SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":20, "S_":"abc", "some_prop1":[1,2,3]}'); +JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":20, "S_":"abc", "some_prop1":[1,2,3]}') +1 +# checking that additionalProperties are evaluated in absence of patternProperties and properties, regardless +# of presence of unevaluatedProperties +SET @object_schema= '{ + "type":"object", + "additionalProperties":{ + "type":"array", + "maxItems":5 + }, + "unevaluatedProperties":{"type":"string"} + }'; +SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":20, "S_":"abc", "some_prop1":[1,2,3]}'); +JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":20, "S_":"abc", "some_prop1":[1,2,3]}') +0 +SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":[1,2,3], "key2": [1,2,3], "I_int":[1,2,3], "S_":[1,2,3], "some_prop1":[1,2,3]}'); +JSON_SCHEMA_VALID(@object_schema, '{"key1":[1,2,3], "key2": [1,2,3], "I_int":[1,2,3], "S_":[1,2,3], "some_prop1":[1,2,3]}') +1 +SET @object_schema= '{ + "type":"object", + "additionalProperties":{ + "type":"array", + "maxItems":5 + } + }'; +SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":20, "S_":"abc", "some_prop1":[1,2,3]}'); +JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":20, "S_":"abc", "some_prop1":[1,2,3]}') +0 +SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":[1,2,3], "key2": [1,2,3], "I_int":[1,2,3], "S_":[1,2,3], "some_prop1":[1,2,3]}'); +JSON_SCHEMA_VALID(@object_schema, '{"key1":[1,2,3], "key2": [1,2,3], "I_int":[1,2,3], "S_":[1,2,3], "some_prop1":[1,2,3]}') +1 +# Checking that in absence of properties, patternProperties and additionalProperties, validation falls back on unevaluatedProperties +SET @object_schema= '{ + "type":"object", + "unevaluatedProperties":{"type":"string"} + }'; +SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": "str1", "I_int":"str2", "S_":"abc", "some_prop1":"str3"}'); +JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": "str1", "I_int":"str2", "S_":"abc", "some_prop1":"str3"}') +1 +SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":[1,2,3], "key2": [1,2,3], "I_int":[1,2,3], "S_":[1,2,3], "some_prop1":[1,2,3]}'); +JSON_SCHEMA_VALID(@object_schema, '{"key1":[1,2,3], "key2": [1,2,3], "I_int":[1,2,3], "S_":[1,2,3], "some_prop1":[1,2,3]}') +0 +SET @object_schema= '{ + "type":"object", + "unevaluatedProperties": false + }'; +SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": "str1", "I_int":"str2", "S_":"abc", "some_prop1":"str3"}'); +JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": "str1", "I_int":"str2", "S_":"abc", "some_prop1":"str3"}') +0 +SELECT JSON_SCHEMA_VALID(@object_schema, '{}'); +JSON_SCHEMA_VALID(@object_schema, '{}') +1 +SET @object_schema= '{ + "type":"object", + "unevaluatedProperties": true + }'; +SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": "str1", "I_int":"str2", "S_":"abc", "some_prop1":"str3"}'); +JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": "str1", "I_int":"str2", "S_":"abc", "some_prop1":"str3"}') +1 +SELECT JSON_SCHEMA_VALID(@object_schema, '{}'); +JSON_SCHEMA_VALID(@object_schema, '{}') +1 +SET @json_schema_dependent_schemas='{ + "type": "object", + "properties": { + "str1": { "type": "string" }, + "num1": { "type": "number" } + }, + + "required": ["str1"], + + "dependentSchemas": { + "num1": { + "properties": { + "str2": { "type": "string" } + }, + "required": ["str2"] + } + } +}'; +SELECT JSON_SCHEMA_VALID(@json_schema_dependent_schemas, '{ "str1": "str", "num1":4}'); +JSON_SCHEMA_VALID(@json_schema_dependent_schemas, '{ "str1": "str", "num1":4}') +0 +SELECT JSON_SCHEMA_VALID(@json_schema_dependent_schemas, '{ "str1": "str"}'); +JSON_SCHEMA_VALID(@json_schema_dependent_schemas, '{ "str1": "str"}') +1 +# Validating logic +SET @not_schema= '{ + "not":{ + "maximum": 4 + } + }'; +SELECT JSON_SCHEMA_VALID(@not_schema, '{"I_int1":3, "O_ob1":{"key1":"val1"}}'); +JSON_SCHEMA_VALID(@not_schema, '{"I_int1":3, "O_ob1":{"key1":"val1"}}') +0 +SELECT JSON_SCHEMA_VALID(@not_schema, '3'); +JSON_SCHEMA_VALID(@not_schema, '3') +0 +SELECT JSON_SCHEMA_VALID(@not_schema, '10'); +JSON_SCHEMA_VALID(@not_schema, '10') +1 +SET @not_schema= '{ + "not":{ + "properties": { + "num1" : {"type":"number", "maximum":5}, + "string1" : { "maxLength": 3} + } + } + }'; +SELECT JSON_SCHEMA_VALID(@not_schema, '{"num1":10, "string2":"abcd"}'); +JSON_SCHEMA_VALID(@not_schema, '{"num1":10, "string2":"abcd"}') +1 +SELECT JSON_SCHEMA_VALID(@not_schema, '{"num1":2, "string2":"abcd"}'); +JSON_SCHEMA_VALID(@not_schema, '{"num1":2, "string2":"abcd"}') +0 +SET @any_of_schema= '{ + "anyOf":[{ + "properties": { + "num1" : {"type":"number", "maximum":5}, + "string1" : { "maxLength": 3} + } + }, + { + "properties":{ + "num1" : {"type":"number", "maximum": 1}, + "string1" : { "maxLength":5} + } + } + ] + }'; +SELECT JSON_SCHEMA_VALID(@any_of_schema, '{"num1":2, "string1":"abcd"}'); +JSON_SCHEMA_VALID(@any_of_schema, '{"num1":2, "string1":"abcd"}') +0 +SELECT JSON_SCHEMA_VALID(@any_of_schema, '{"num1":2, "string1":"abc"}'); +JSON_SCHEMA_VALID(@any_of_schema, '{"num1":2, "string1":"abc"}') +1 +SET @any_of_schema= '{ + "anyOf": [ + {"type":"number", "maximum":5}, + {"type":"string"} + ] + }'; +SELECT JSON_SCHEMA_VALID(@any_of_schema, '2'); +JSON_SCHEMA_VALID(@any_of_schema, '2') +1 +SELECT JSON_SCHEMA_VALID(@any_of_schema, '6'); +JSON_SCHEMA_VALID(@any_of_schema, '6') +0 +SELECT JSON_SCHEMA_VALID(@any_of_schema, '[1, 2, 3]'); +JSON_SCHEMA_VALID(@any_of_schema, '[1, 2, 3]') +0 +SET @one_of_schema= '{ + "oneOf":[{ + "properties": { + "num1" : {"type":"number", "maximum":5}, + "string1" : { "maxLength": 3} + } + }, + { + "properties":{ + "num1" : {"type":"number", "maximum": 1}, + "string1" : { "maxLength":5} + } + } + ] + }'; +SELECT JSON_SCHEMA_VALID(@one_of_schema, '{"num1":2, "string1":"abcd"}'); +JSON_SCHEMA_VALID(@one_of_schema, '{"num1":2, "string1":"abcd"}') +0 +SELECT JSON_SCHEMA_VALID(@one_of_schema, '{"num1":2, "string1":"abc"}'); +JSON_SCHEMA_VALID(@one_of_schema, '{"num1":2, "string1":"abc"}') +1 +SET @one_of_schema= '{ + "oneOf": [ + {"type":"number", "maximum":5}, + {"type":"number", "maximum":3} + ] + }'; +SELECT JSON_SCHEMA_VALID(@one_of_schema, '2'); +JSON_SCHEMA_VALID(@one_of_schema, '2') +0 +SELECT JSON_SCHEMA_VALID(@one_of_schema, '4'); +JSON_SCHEMA_VALID(@one_of_schema, '4') +1 +SET @all_of_schema= '{ + "allOf":[{ + "properties": { + "num1" : {"type":"number", "maximum":5}, + "string1" : { "maxLength": 3} + } + }, + { + "properties":{ + "num1" : {"type":"number", "maximum": 1}, + "string1" : { "maxLength":5} + } + } + ] + }'; +SELECT JSON_SCHEMA_VALID(@all_of_schema, '{"num1":2, "string1":"abcd"}'); +JSON_SCHEMA_VALID(@all_of_schema, '{"num1":2, "string1":"abcd"}') +0 +SET @all_of_schema= '{ + "allOf":[ + { + "properties":{ + "num1": {"type":"number"}, + "string1": {"type":"string"} + } + }, + { + "properties":{ + "num1": {"maximum":10}, + "string1": {"maxLength":5} + } + } + ] + }'; +SELECT JSON_SCHEMA_VALID(@all_of_schema, '{"num1":5, "string1":"abc"}'); +JSON_SCHEMA_VALID(@all_of_schema, '{"num1":5, "string1":"abc"}') +1 +SELECT JSON_SCHEMA_VALID(@all_of_schema, '{"num1":5, "string1":"foobar"}'); +JSON_SCHEMA_VALID(@all_of_schema, '{"num1":5, "string1":"foobar"}') +0 +SET @condition_schema= '{ + "if" : {"maximum":30, "multipleOf":3}, + "then":{"minimum":5}, + "else":{"minimum":10} + }'; +SELECT JSON_SCHEMA_VALID(@condition_schema, '6'); +JSON_SCHEMA_VALID(@condition_schema, '6') +1 +SELECT JSON_SCHEMA_VALID(@condition_schema, '4'); +JSON_SCHEMA_VALID(@condition_schema, '4') +0 +SELECT JSON_SCHEMA_VALID(@condition_schema, '13'); +JSON_SCHEMA_VALID(@condition_schema, '13') +1 +SET @condition_schema= '{ + "if" : {"maximum":30, "multipleOf":3} + }'; +SELECT JSON_SCHEMA_VALID(@condition_schema, '6'); +JSON_SCHEMA_VALID(@condition_schema, '6') +1 +SELECT JSON_SCHEMA_VALID(@condition_schema, '7'); +JSON_SCHEMA_VALID(@condition_schema, '7') +1 +SET @condition_schema= '{ + "then":{"minimum":5}, + "else":{"minimum":10} + }'; +SELECT JSON_SCHEMA_VALID(@condition_schema, '4'); +JSON_SCHEMA_VALID(@condition_schema, '4') +1 +SELECT JSON_SCHEMA_VALID(@condition_schema, '11'); +JSON_SCHEMA_VALID(@condition_schema, '11') +1 +# Checking unevaluatedProperperties with logical properties +SET @all_of_unevaluated='{ + "allOf": [ + { + "type": "object", + "properties": { + "street_address": { "type": "string" }, + "city": { "type": "string" }, + "state": { "type": "string" } + }, + "required": ["street_address", "city", "state"] + } + ], + + "properties": { + "type": { "enum": ["residential", "business"] } + }, + "required": ["type"], + "unevaluatedProperties": false +}'; +SELECT JSON_SCHEMA_VALID(@all_of_unevaluated, '{ + "street_address": "1600 Pennsylvania Avenue NW", + "city": "Washington", + "state": "DC", + "type": "business" +}'); +JSON_SCHEMA_VALID(@all_of_unevaluated, '{ + "street_address": "1600 Pennsylvania Avenue NW", + "city": "Washington", + "state": "DC", + "type": "business" +}') +1 +SET @all_of_unevaluated='{ + "allOf": [ + { + "type": "object", + "properties": { + "street_address": { "type": "string" }, + "city": { "type": "string" }, + "state": { "type": "string" } + }, + "required": ["street_address", "city", "state"] + } + ], + + "unevaluatedProperties": {"type":"number"} +}'; +SELECT JSON_SCHEMA_VALID(@all_of_unevaluated, '{ + "street_address": "1600 Pennsylvania Avenue NW", + "city": "Washington", + "state": "DC", + "type": "business" +}'); +JSON_SCHEMA_VALID(@all_of_unevaluated, '{ + "street_address": "1600 Pennsylvania Avenue NW", + "city": "Washington", + "state": "DC", + "type": "business" +}') +0 +SET @any_of_unevaluated='{ + "anyOf": [ + { + "type": "object", + "properties": { + "street_address": { "type": "string" }, + "city": { "type": "string" }, + "state": { "type": "string" } + }, + "required": ["street_address", "city", "state"] + } + ], + + "unevaluatedProperties": {"type":"number"} +}'; +SELECT JSON_SCHEMA_VALID(@any_of_unevaluated, '{ + "street_address": "1600 Pennsylvania Avenue NW", + "city": "Washington", + "state": "DC", + "type": "business" +}'); +JSON_SCHEMA_VALID(@any_of_unevaluated, '{ + "street_address": "1600 Pennsylvania Avenue NW", + "city": "Washington", + "state": "DC", + "type": "business" +}') +0 +SET @all_of_unevaluated='{ + "allOf": [ + { + "type": "array" + }, + { + "maxItems":10 + }, + { + "prefixItems": [ {"type":"number"}, {"type":"string"}], + "additionalItems":{"type":"array"} + } + ], + + "unevaluatedItems": {"type":"number"} +}'; +SELECT JSON_SCHEMA_VALID(@all_of_unevaluated, '[1, "str", 2]'); +JSON_SCHEMA_VALID(@all_of_unevaluated, '[1, "str", 2]') +0 +SET @all_of_unevaluated='{ + "anyOf": [ + { + "type": "array" + }, + { + "maxItems":10 + }, + { + "prefixItems": [ {"type":"number"}, {"type":"string"}], + "additionalItems":{"type":"array"} + } + ], + + "unevaluatedItems": {"type":"number"} +}'; +SELECT JSON_SCHEMA_VALID(@all_of_unevaluated, '[1, "str", 2]'); +JSON_SCHEMA_VALID(@all_of_unevaluated, '[1, "str", 2]') +1 +SET @all_of_unevaluated='{ + "oneOf": [ + { + "type": "array" + }, + { + "maxItems":10 + }, + { + "prefixItems": [ {"type":"number"}, {"type":"string"}], + "additionalItems":{"type":"array"} + } + ], + + "unevaluatedItems": {"type":"number"} +}'; +SELECT JSON_SCHEMA_VALID(@all_of_unevaluated, '[1, "str", 2]'); +JSON_SCHEMA_VALID(@all_of_unevaluated, '[1, "str", 2]') +0 +# Media string +SET @schema_media_string= '{ + "type": "string", + "contentMediaType": "text/html" +}'; +SELECT JSON_SCHEMA_VALID(@schema_media_string, '"str1"'); +JSON_SCHEMA_VALID(@schema_media_string, '"str1"') +1 +SET @schema_reference= '{"$ref": "http://example.com/custom-email-validator.json#"}'; +SELECT JSON_SCHEMA_VALID(@schema_reference, '{}'); +ERROR HY000: $ref keyword is not supported +SET @schema_reference= '{"$id": "http://example.com/custom-email-validator.json#"}'; +SELECT JSON_SCHEMA_VALID(@schema_reference, '{}'); +ERROR HY000: $id keyword is not supported +SET @schema_reference= '{"$anchor": "http://example.com/custom-email-validator.json#"}'; +SELECT JSON_SCHEMA_VALID(@schema_reference, '{}'); +ERROR HY000: $anchor keyword is not supported +SET @schema_reference= '{"$defs": "http://example.com/custom-email-validator.json#"}'; +SELECT JSON_SCHEMA_VALID(@schema_reference, '{}'); +ERROR HY000: $defs keyword is not supported +# End of 11.1 test diff --git a/mysql-test/main/func_json.test b/mysql-test/main/func_json.test index 93d8c2a7d6a..7836a1f32ec 100644 --- a/mysql-test/main/func_json.test +++ b/mysql-test/main/func_json.test @@ -1755,3 +1755,1606 @@ SELECT JSON_EXTRACT('{ "my-key": 1 }', '$.my-key'); --echo # --echo # End of 10.9 Test --echo # + +--echo # Beginning of 11.1 test + +--echo # +--echo # MDEV-27128: Implement JSON Schema Validation FUNCTION +--echo # + +--echo # Checking annotations + +SET @schema_number= '{ + "title" : "This is title 1", + "description":"this is description 1", + "$comment":"This is comment 1", + "type":"number", + "deprecated":true, + "readOnly":true, + "writeOnly":false, + "example":[2], + "default":4, + "$schema": "https://json-schema.org/draft/2019-09/json-schema-validation.html#rfc.section.9.5" + }'; +SELECT JSON_SCHEMA_VALID(@schema_number, '"string1"'); +SELECT JSON_SCHEMA_VALID(@schema_number, '2'); + +--echo # Checking empty schema with empty json document + +SET @schema= '{}'; + +SELECT JSON_SCHEMA_VALID(@schema, ''); +SELECT JSON_SCHEMA_VALID(@schema, '{}'); +SELECT JSON_SCHEMA_VALID(@schema, '[]'); +SELECT JSON_SCHEMA_VALID(@schema, 'null'); +SELECT JSON_SCHEMA_VALID(@schema, 'true'); +SELECT JSON_SCHEMA_VALID(@schema, 'false'); + +--echo # Checking scalar against json schema + +--echo # Checking boolean and null + +SET @schema_true= '{ "type": "boolean"}'; +SELECT JSON_SCHEMA_VALID(@schema_true, 'true'); +SELECT JSON_SCHEMA_VALID(@schema_true, 'false'); +SELECT JSON_SCHEMA_VALID(@schema_true, 'null'); + +SET @schema_true= '{ "type": "boolean", + "const":"false"}'; +SELECT JSON_SCHEMA_VALID(@schema_true, 'true'); +SET @schema_true= '{ "type": "boolean", + "enum":[true, null, false]}'; +SELECT JSON_SCHEMA_VALID(@schema_true, 'true'); +SET @schema_true= '{ "type": "boolean", + "enum": [null, false]}'; +SELECT JSON_SCHEMA_VALID(@schema_true, 'true'); +SET @schema_true= '{ "type": "boolean", + "enum": [null, true]}'; +SELECT JSON_SCHEMA_VALID(@schema_true, 'true'); +SET @schema_true= '{ "type": "boolean", + "const":"false"}'; +SELECT JSON_SCHEMA_VALID(@schema_true, 'true'); + +--echo # Type can be more than one + +SET @schema= ' + { + "type":["string", "number","array"] + }'; +SELECT JSON_SCHEMA_VALID(@schema, '{"key1":"val1"}'); +SELECT JSON_SCHEMA_VALID(@schema, '"abc"'); +SELECT JSON_SCHEMA_VALID(@schema, '3.14'); + +--echo # Checking number + +SET @schema_number= '{ + "maximum":7, + "minimum": 3, + "multipleOf":3 +}'; +SELECT JSON_SCHEMA_VALID(@schema_number, '2'); + +SET @schema_number= '{ + "type": "number", + "maximum":13, + "minimum": 4, + "multipleOf":3, + "exclusiveMaximum": 9, + "exclusiveMinimum":4 +}'; + +SELECT JSON_SCHEMA_VALID(@schema_number, '2'); +SELECT JSON_SCHEMA_VALID(@schema_number, '6'); +SELECT JSON_SCHEMA_VALID(@schema_number, '9'); +SELECT JSON_SCHEMA_VALID(@schema_number, '5'); + +SET @schema_number= '{ + "type": "number", + "maximum":100, + "minimum": 0, + "enum": [1, 2, "3", [4, 5, 6], {"key1":"val1"}] +}'; +SELECT JSON_SCHEMA_VALID(@schema_number, 1); +SELECT JSON_SCHEMA_VALID(@schema_number, 3); + +SET @schema_number= '{ + "type":"number", + "maximum":10, + "const":2 + }'; +SELECT JSON_SCHEMA_VALID(@schema_number, '3'); +SELECT JSON_SCHEMA_VALID(@schema_number, '2'); + +--echo # Checking string + +--echo # checking format keyword. (not validating for now) + +SET @schema_string= '{ + "type": "string", + "format":"date-time" +}'; +SELECT JSON_SCHEMA_VALID(@schema_string, '"not_date-time"'); +SET @schema_string= '{ + "type": "string", + "format":"date" +}'; +SELECT JSON_SCHEMA_VALID(@schema_string, '"not_date"'); +SET @schema_string= '{ + "type": "string", + "format":"time" +}'; +SELECT JSON_SCHEMA_VALID(@schema_string, '"not_time"'); +SET @schema_string= '{ + "type": "string", + "format":"duration" +}'; +SELECT JSON_SCHEMA_VALID(@schema_string, '"not_duration"'); +SET @schema_string= '{ + "type": "string", + "format":"email" +}'; +SELECT JSON_SCHEMA_VALID(@schema_string, '"not_email"'); +SET @schema_string= '{ + "type": "string", + "format":"idn-email" +}'; +SELECT JSON_SCHEMA_VALID(@schema_string, '"not_idn-email"'); +SET @schema_string= '{ + "type": "string", + "format":"hostname" +}'; +SELECT JSON_SCHEMA_VALID(@schema_string, '"not_hostname"'); +SET @schema_string= '{ + "type": "string", + "format":"idn-hostname" +}'; +SELECT JSON_SCHEMA_VALID(@schema_string, '"not_idn-hostname"'); +SET @schema_string= '{ + "type": "string", + "format":"ipv4" +}'; +SELECT JSON_SCHEMA_VALID(@schema_string, '"not_ipv4"'); +SET @schema_string= '{ + "type": "string", + "format":"ipv6" +}'; +SELECT JSON_SCHEMA_VALID(@schema_string, '"not_ipv6"'); +SET @schema_string= '{ + "type": "string", + "format":"uri" +}'; +SELECT JSON_SCHEMA_VALID(@schema_string, '"not_uri"'); +SET @schema_string= '{ + "type": "string", + "format":"uri-reference" +}'; +SELECT JSON_SCHEMA_VALID(@schema_string, '"not_uri-reference"'); +SET @schema_string= '{ + "type": "string", + "format":"iri" +}'; +SELECT JSON_SCHEMA_VALID(@schema_string, '"not_iri"'); +SET @schema_string= '{ + "type": "string", + "format":"iri-reference" +}'; +SELECT JSON_SCHEMA_VALID(@schema_string, '"not_iri-reference"'); +SET @schema_string= '{ + "type": "string", + "format":"uuid" +}'; +SELECT JSON_SCHEMA_VALID(@schema_string, '"not_uuid"'); +SET @schema_string= '{ + "type": "string", + "format":"json-pointer" +}'; +SELECT JSON_SCHEMA_VALID(@schema_string, '"not_json-pointer"'); +SET @schema_string= '{ + "type": "string", + "format":"relative-json-pointer" +}'; +SELECT JSON_SCHEMA_VALID(@schema_string, '"not_relative-json-pointer"'); +SET @schema_string= '{ + "type": "string", + "format":"regex" +}'; +SELECT JSON_SCHEMA_VALID(@schema_string, '"not_regex"'); + +--echo # Validating other string keywords + +SET @schema_string= '{ + "type": "string", + "maxLength":7, + "minLength": 4 +}'; +SELECT JSON_SCHEMA_VALID(@schema_string, '"foobar"'); + +SET @schema_string= '{ + "type": "string", + "maxLength": 10, + "minLength": 8 +}'; +SELECT JSON_SCHEMA_VALID(@schema_string, '"foobar"'); +SELECT JSON_SCHEMA_VALID(@schema_string, '"foobar123"'); + +SET @schema_string= '{ + "type": "string", + "maxLength": 10, + "minLength": 3, + "const": "foobar" +}'; +SELECT JSON_SCHEMA_VALID(@schema_string, '"foobar123"'); + +SET @schema_string= '{ + "type": "string", + "enum": ["red", "green", "blue"] +}'; +SELECT JSON_SCHEMA_VALID(@schema_string, '"green"'); +SELECT JSON_SCHEMA_VALID(@schema_string, '"orange"'); + +SET @string_schema= '{ + "type":"string", + "pattern":"ab+c" + }'; +SELECT JSON_SCHEMA_VALID(@string_schema, '"abc"'); + +--echo # Validating non-scalar + +--echo # Validating array + +SET @schema_array= '{"type":"array"}'; +SELECT JSON_SCHEMA_VALID(@schema_array, '[1, 2, {"key1":"val1"}]'); + + +SET @schema_array= '{"type":"array", + "maxItems": 4, + "minItems": 2}'; +SELECT JSON_SCHEMA_VALID(@schema_array, '[1, 2, {"key1":"val1"}]'); +SELECT JSON_SCHEMA_VALID(@schema_array, '[1, 2, false, null, "foobar"]'); +SELECT JSON_SCHEMA_VALID(@schema_array, '[1]'); +SET @schema_array= '{"maxItems": 4, + "minItems": 2}'; +SELECT JSON_SCHEMA_VALID(@schema_array, '[1, 2, {"key1":"val1"}]'); +SELECT JSON_SCHEMA_VALID(@schema_array, '[1, 2, false, null, "foobar"]'); +SELECT JSON_SCHEMA_VALID(@schema_array, '[1, 2]'); + +SET @schema_array= '{ + "type":"array", + "items": {"type":"number"}, + "maxItems": 4, + "minItems": 2}'; +SELECT JSON_SCHEMA_VALID(@schema_array, '[1, 2, 3]'); +SELECT JSON_SCHEMA_VALID(@schema_array, '[1, 2, "foobar"]'); + +SET @schema_array= '{"type":"array", + "maxItems": 4, + "minItems": 2, + "const": [1, 2, 3, 4]}'; +SELECT JSON_SCHEMA_VALID(@schema_array, '[1, 2, 3, "foobar"]'); +SELECT JSON_SCHEMA_VALID(@schema_array, '[1, 2, 3, 4]'); + +SET @schema_array= '{"type":"array", + "enum":[[1,2,3], [4,5,6], [7,8,9]]}'; +SELECT JSON_SCHEMA_VALID(@schema_array, '[4,5,6]'); +SELECT JSON_SCHEMA_VALID(@schema_array, '[1,5,7]'); + +SET @schema_array= '{ + "type": "array", + "uniqueItems":true + }'; +SELECT JSON_SCHEMA_VALID(@schema_array, '[1, 2, 3, 2.0]'); +SELECT JSON_SCHEMA_VALID(@schema_array, '[1, 2, 3, 4.0]'); + +SET @schema_array= '{ + "type": "array", + "contains": { + "type": "number" + }, + "minContains": 2, + "maxContains": 3 +}'; +SELECT JSON_SCHEMA_VALID(@schema_array, '["string1", "string2", "string3", 1, 2, 3, 4]'); +SELECT JSON_SCHEMA_VALID(@schema_array, '["string1", "string2", "string3", 1, 2, 3]'); + +SET @schema_array= '{ + "type": "array", + "prefixItems": [ + { "type": "number", "maximum": 10, "minimum":3}, + { "type": "string" } + ] + }'; +SELECT JSON_SCHEMA_VALID(@schema_array, '[5, "string1",2]'); +SELECT JSON_SCHEMA_VALID(@schema_array, '[2, "string1",2]'); + +SET @schema_array= '{ + "type": "array", + "prefixItems": [ + { "type": "number", "maximum": 10, "minimum":3}, + { "type": "string" } + ], + "items":true + }'; +SELECT JSON_SCHEMA_VALID(@schema_array, '[5, "string1",2]'); +SET @schema_array= '{ + "type": "array", + "prefixItems": [ + { "type": "number", "maximum": 10, "minimum":3}, + { "type": "string" } + ], + "items":false + }'; +SELECT JSON_SCHEMA_VALID(@schema_array, '[5, "string1",2]'); + +--echo # Checking objects + +SET @schema_object= '{"type":"object", + "properties": + { + "number1":{ + "type":"number", + "maximum":12, + "minimum":1 + }, + "string1": { + "type":"string", + "maxLength":10, + "minLength": 4 + }, + "array1": {"type":"array", + "maxItems": 4, + "minItems": 2} + } + }'; +SELECT JSON_SCHEMA_VALID(@schema_object, '{"number1":10, "string1":"foobar","array1":[1,2,3]}'); +SELECT JSON_SCHEMA_VALID(@schema_object, '{"number1":10, "string1":"foobar","array1":[1,2,3,4,5]}'); + +SET @schema_obj= '{ + "type": "object", + "properties": { + "number1":{"type":"number"}, + "string1":{"type":"string"}, + "array1":{"type":"array"} + }, + "dependentRequired": { + "number1":["string1"] + } + }'; +SELECT JSON_SCHEMA_VALID(@schema_obj, '{"array1":[1,2,3], "number1":2, "string1":"abc"}'); +SELECT JSON_SCHEMA_VALID(@schema_obj, '{"array1":[1,2,3], "number1":2}'); + +SET @schema_obj= '{"type":"object", + "properties": + { + "number1":{ + "type":"number", + "maximum":12, + "minimum":1 + }, + "key1" : { + "type":"object", + "properties": { + "key2" :{ + "type":"string" + } + } + } + }, + "enum": [{"number1":3, "key1":{"key2":"string1"}}, {"number1":5, "key1":{"key2":"string3"}}, {"number1":7, "key1":{"key2":"string5"}}] + }'; +SELECT JSON_SCHEMA_VALID(@schema_obj, '{"number1":5, "key1":{"key2":1}}'); +SELECT JSON_SCHEMA_VALID(@schema_obj, '{"number1":5, "key1":{"key2":"string1"}}'); +SELECT JSON_SCHEMA_VALID(@schema_obj, '{"number1":5, "key1":{"key2":"string7"}}'); + +SET @schema_obj= '{"type":"object", + "properties": + { + "number1":{ + "type":"number", + "maximum":12, + "minimum":1 + }, + "obj1" : { + "type":"object", + "properties": { + "obj1_1":{ + "type":"string" + }, + "obj1_2": { + "type":"array" + } + } + }, + "obj2" : { + "type":"object", + "properties" : { + "obj2_1":{ + "type":"number" + } + } + } + }, + "required":["number1", "obj2"] + }'; +SELECT JSON_SCHEMA_VALID(@schema_obj,'{"number1":5, "obj1":{"obj1_1":"string1", "obj1_2":[1, 2, 3]}}'); +SELECT JSON_SCHEMA_VALID(@schema_obj,'{"number1":5, "obj1":{"obj1_1":"string1", "obj1_2":[1, 2, 3]}, "obj2":{"obj2_1":7}}'); + +SET @schema_obj= '{"type":"object", + "properties": + { + "number1":{ + "type":"number", + "maximum":12, + "minimum":1 + }, + "obj1" : { + "type":"object", + "properties": { + "obj1_1":{ + "type":"string" + }, + "obj1_2": { + "type":"array" + } + } + }, + "obj2" : { + "type":"object", + "properties" : { + "obj2_1":{ + "type":"number" + } + } + } + }, + "required":["number1", "obj2"], + "const": {"number1":5, "obj1":{"obj1_1":"string1", "obj1_2":[1, 2, 3]}, "obj2":{"obj2_1":7}} + }'; +SELECT JSON_SCHEMA_VALID(@schema_obj,'{"number1":5, "obj1":{"obj1_1":"string1", "obj1_2":[1, 2, 3]}, "obj2":{"obj2_1":7}}'); +SELECT JSON_SCHEMA_VALID(@schema_obj,'{"number1":5, "obj1":{"obj1_1":"string1", "obj1_2":[1, 2, 3]}}'); + +SET @schema_obj= '{"type":"object", + "properties": + { + "number1":{ + "type":"number", + "maximum":12, + "minimum":1 + }, + "obj1" : { + "type":"object", + "properties": { + "obj1_1":{ + "type":"string" + }, + "obj1_2": { + "type":"array" + } + } + }, + "obj2" : { + "type":"object", + "properties" : { + "obj2_1":{ + "type":"number" + } + } + } + }, + "maxProperties": 5, + "minProperties":2 + }'; +SELECT JSON_SCHEMA_VALID(@schema_obj,'{"number1":5, "obj1":{"obj1_1":"string1", "obj1_2":[1, 2, 3]}, "obj2":{"obj2_1":7}}'); +SELECT JSON_SCHEMA_VALID(@schema_obj,'{"number1":1, "number2":2, "number3":3, "number4":4, "number5":5, "number6":6}'); + +SET @schema_obj= '{"type":"object", + "properties": + { + "number1":{ + "type":"number", + "maximum":12, + "minimum":1 + }, + "obj1" : { + "type":"object", + "properties": { + "obj1_1":{ + "type":"string" + }, + "obj1_2": { + "type":"array" + } + } + } + }, + "maxProperties": 3, + "minProperties":1, + "additionalProperties":false + }'; +SELECT JSON_SCHEMA_VALID(@schema_obj,'{"number1":5, "obj1":{"obj1_1":"string1", "obj1_2":[1, 2, 3]}, "obj2":"string2"}'); + +--echo # Demonstrating that irrelavent keywords for a type and irrelavent type +--echo # are ignored, and none of the keywords are mandatory, including "type". + +SET @schema_properties= '{ + "properties" : { + "number1":{ "maximum":10 }, + "string1" : { "maxLength": 3} + } + }'; +SELECT JSON_SCHEMA_VALID(@schema_properties, '{ "number1":25, "string1":"ab" }'); +SELECT JSON_SCHEMA_VALID(@schema_properties, '{ "number1":10, "string1":"ab" }'); + +SET @schema_properties= '{ + "properties" : { + "number1":{ "maximum":10 }, + "string1" : { "maxLength": 3}, + "obj1" : { + "properties":{ + "number2": {"minimum":8}, + "array2": {"uniqueItems":true} + } + } + } + }'; +SELECT JSON_SCHEMA_VALID(@schema_properties, '{ "number1":2, "string1":"ab", "obj1":{"number2":2, "array2":[1,2,3]} }'); +SELECT JSON_SCHEMA_VALID(@schema_properties, '{ "number1":2, "string1":"ab", "obj1":{"number2":10, "array2":[1,2,3]} }'); +SELECT JSON_SCHEMA_VALID(@schema_properties, '{ "number1":2, "string1":"ab", "obj1":{"array2":[1,2,3]} }'); +SELECT JSON_SCHEMA_VALID(@schema_properties, '{ "number1":2, "string1":"ab", "obj1":{"number2":10, "array2":[1,2,3,2]} }'); + +SET @schema_num= '{ + "maximum":10, + "minimum":2 + }'; +SELECT JSON_SCHEMA_VALID(@schema_num, '5'); +SELECT JSON_SCHEMA_VALID(@schema_num, '"abc"'); + +SET @schema_str= '{ + "maxLength":5, + "minLength":2, + "pattern":"a+bc" + }'; +SELECT JSON_SCHEMA_VALID(@schema_str, '"abc"'); +SELECT JSON_SCHEMA_VALID(@schema_str, '"abcadef"'); +SELECT JSON_SCHEMA_VALID(@schema_str, '"bc"'); + +SET @schema_arr= '{ + "uniqueItems":true, + "items":{"type":"string"}, + "maximum":10 + }'; +SELECT JSON_SCHEMA_VALID(@schema_arr,'["abc", "bcd", "cde"]'); +SELECT JSON_SCHEMA_VALID(@schema_arr,'["abc", "bcd", "abc"]'); +SELECT JSON_SCHEMA_VALID(@schema_arr,'["abc", "bcd", 1]'); + +SET @schema_const1= '{"const":2}'; +SELECT JSON_SCHEMA_VALID(@schema_const1, '2'); +SELECT JSON_SCHEMA_VALID(@schema_const1, '"abc"'); + +SET @schema_const2= '{"const":true}'; +SELECT JSON_SCHEMA_VALID(@schema_const2,'true'); +SELECT JSON_SCHEMA_VALID(@schema_const2,'false'); + +SET @schema_enum='{"enum":[1,2,3,"abc", [4,5,6]]}'; +SELECT JSON_SCHEMA_VALID(@schema_enum,'[4,5,6]'); +SELECT JSON_SCHEMA_VALID(@schema_enum,'4'); +SELECT JSON_SCHEMA_VALID(@schema_enum,'"abc"'); + +SET @schema_required='{"required":["num1","str1", "arr1"]}'; +SELECT JSON_SCHEMA_VALID(@schema_required,'{"num1":1, "str1":"abc", "arr1":[1,2,3]}'); +SELECT JSON_SCHEMA_VALID(@schema_required,'{"num1":1, "arr1":[1,2,3]}'); +SELECT JSON_SCHEMA_VALID(@schema_required,'"abcd"'); + +SET @schema_dep_required='{ + "dependentRequired": { + "num1":["num2","num3"], + "str1":["str2","str3"] + } + }'; +SELECT JSON_SCHEMA_VALID(@schema_dep_required,'{"num1":1, "num2":"abc", "num3":[1,2,3]}'); +SELECT JSON_SCHEMA_VALID(@schema_dep_required,'{"num1":1, "num2":"abc", "num3":[1,2,3], "str1":"abcd"}'); +SELECT JSON_SCHEMA_VALID(@schema_dep_required,'{"num1":1, "num2":"abc", "num3":[1,2,3], "arr1":[1,2,3]}'); + +--echo # Checking syntax error +SET @invalid_schema= '{"type":"object" + "properties":{ + "number1": {"type":"number"}, + "obj2": {"type":"object", + "properties": { + "key1": {"type":"number"} + } + } + } + }'; +SELECT JSON_SCHEMA_VALID(@invalid_schema, '{"number1":3, "obj2":{"key1":3}}'); + +SET @invalid_json= '{"type":"array", + "maxItems": 4, + "minItems": 2, + "const": [1, 2, 3, 4]}'; +SELECT JSON_SCHEMA_VALID(@schema_array, '[1, 2, 3, 4'); + +SET @schema_string= '{ + "type": "string", + "maxLength":-2 + }'; +--error ER_JSON_INVALID_VALUE_FOR_KEYWORD +SELECT JSON_SCHEMA_VALID(@schema_string, '"abcxyz"'); + +SET @schema_number= '{ + "type": "number", + "multipleOf":-3 + }'; +--error ER_JSON_INVALID_VALUE_FOR_KEYWORD +SELECT JSON_SCHEMA_VALID(@schema_number, '3'); + +SET @schema_object= '{ + "type": "object", + "properties":{"num1":{"type":"number"}}, + "required":{} + }'; +--error ER_JSON_INVALID_VALUE_FOR_KEYWORD +SELECT JSON_SCHEMA_VALID(@schema_object, '{"num1":2}'); + +SET @schema_string= '{ + "type": "string", + "maxLength":-10 + }'; +--error ER_JSON_INVALID_VALUE_FOR_KEYWORD +SELECT JSON_SCHEMA_VALID(@schema_string, '"str1"'); + +SET @schema_number= '{"type":"numberz"}'; +--error ER_JSON_INVALID_VALUE_FOR_KEYWORD +SELECT JSON_SCHEMA_VALID(@schema_number, '"string1"'); + +--echo # Using JSON_SCHEMA_VALID() as a constraint validation to insert into table + +CREATE TABLE str_table (val_str JSON CHECK(JSON_SCHEMA_VALID('{ + "type":"string", + "maxLength":5, + "minLength":2, + "enum":["ab", "cd", "abc", "def", "abcdef"] + }', val_str))); +CREATE TABLE num_table(val_num JSON CHECK(JSON_SCHEMA_VALID('{ + "type":"number", + "minimum":10, + "maximum":30, + "exclusiveMinimum":11, + "exclusiveMaximum":29, + "multipleOf":5, + "const":15 + }', val_num))); +CREATE TABLE true_table(val_true JSON CHECK(JSON_SCHEMA_VALID('{ + "type":"boolean", + "enum":[true, false, null] + }', val_true))); +CREATE TABLE false_table (val_false JSON CHECK(JSON_SCHEMA_VALID('{ + "type":"boolean" + }', val_false))); +CREATE TABLE null_table (val_null JSON CHECK(JSON_SCHEMA_VALID('{ + "type":"null" + }', val_null))); +CREATE TABLE arr_table (val_arr JSON CHECK(JSON_SCHEMA_VALID('{ + "type":"array", + "uniqueItems":true, + "maxItems":5, + "minItems":1, + "items":true, + "prefixItems":[{"type":"number"}] + }', val_arr))); +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 str_table VALUES ('"ab"'), ('"cd"'), ('"abc"'), ('"def"'); +--error ER_CONSTRAINT_FAILED +INSERT INTO str_table VALUES ('"feb"'); +--error ER_CONSTRAINT_FAILED +INSERT INTO str_table VALUES('"abcdef"'); +--error ER_CONSTRAINT_FAILED +INSERT INTO str_table VALUES('"fedcba"'); +SELECT * FROM str_table; + + +INSERT INTO num_table values('15'); +--error ER_CONSTRAINT_FAILED +INSERT INTO num_table values('25'); +SELECT * FROM num_table; + +INSERT INTO true_table VALUES ('true'); +SELECT * FROM true_table; + +INSERT INTO false_table VALUES('false'); +SELECT * FROM false_table; + +INSERT INTO arr_table VALUES ('[10, 2, "abc"]'); +INSERT INTO arr_table VALUES('[100]'); +--error ER_CONSTRAINT_FAILED +INSERT INTO arr_table VALUES ('["str1", 2, "abc", 2.0]'); +SELECT * FROM arr_table; + +INSERT INTO obj_table VALUES('{"number1":4, "string1":"abcd", "object1":{"key1":"val1", "key2":[1,2,3, "string1"], "key3":4}}'); +--error ER_CONSTRAINT_FAILED +INSERT INTO obj_table VALUES('{"number1":3, "string1":"abcd", "object1":{"key1":"val1", "key2":[1,2,3, "string1"], "key3":4}}'); +--error ER_CONSTRAINT_FAILED +INSERT INTO obj_table VALUES('{"number1":3, "string1":"abcd"}'); +--error ER_CONSTRAINT_FAILED +INSERT INTO obj_table VALUES('{"number1":3, "string1":"abcd", "object1":{"key1":"val1", "key2":[1,2,3, "string1"]}'); +SELECT * FROM obj_table; + +DROP TABLE str_table, num_table, true_table, false_table, null_table, arr_table, obj_table; + +--echo # array validation + +SET @schema_array= '{ + "type": "array", + "prefixItems": [ + { "type": "number", "maximum": 10, "minimum":3}, + { "type": "string" } + ], + "additionalItems" : {"type":"number"}, + "unevaluatedItems": {"type":"string"}, + "items":{"type":"array"} + }'; +SELECT JSON_SCHEMA_VALID(@schema_array, '[5, "string1", [1,2], "2", "string"]'); +SELECT JSON_SCHEMA_VALID(@schema_array, '[5, "string1", "2", "string"]'); +SELECT JSON_SCHEMA_VALID(@schema_array, '[5, "string1", [1,2], [1,2]]'); + +SET @schema_array= '{ + "type": "array", + "prefixItems": [ + { "type": "number", "maximum": 10, "minimum":3}, + { "type": "string" } + ], + "additionalItems" : {"type":"number"}, + "unevaluatedItems": {"type":"string"}, + "items": true + }'; +SELECT JSON_SCHEMA_VALID(@schema_array, '[5, "string1", [1,2], "2", "string"]'); + +SET @schema_array= '{ + "type": "array", + "prefixItems": [ + { "type": "number", "maximum": 10, "minimum":3}, + { "type": "string" } + ], + "additionalItems" : {"type":"number"}, + "unevaluatedItems": {"type":"string"}, + "items": false + }'; +SELECT JSON_SCHEMA_VALID(@schema_array, '[5, "string1", [1,2], "2", "string"]'); + +SET @schema_array= '{ + "type": "array", + "prefixItems": [ + { "type": "number", "maximum": 10, "minimum":3}, + { "type": "string" } + ], + "additionalItems" : {"type":"number"}, + "unevaluatedItems": {"type":"string"}, + "items": [{"type":"string"}] + }'; +--error ER_JSON_INVALID_VALUE_FOR_KEYWORD +SELECT JSON_SCHEMA_VALID(@schema_array, '[5, "string1", [1,2], "2", "string"]'); + +--echo # Removing items + +SET @schema_array= '{ + "type": "array", + "prefixItems": [ + { "type": "number", "maximum": 10, "minimum":3}, + { "type": "string" } + ], + "additionalItems" : {"type":"number"}, + "unevaluatedItems": {"type":"string"} + }'; +SELECT JSON_SCHEMA_VALID(@schema_array, '[5, "string1", [1,2], "2", "string"]'); +SELECT JSON_SCHEMA_VALID(@schema_array, '[5, "string1", 1, 2, 3]'); + +SET @schema_array= '{ + "type": "array", + "prefixItems": [ + { "type": "number", "maximum": 10, "minimum":3}, + { "type": "string" } + ], + "additionalItems" : true, + "unevaluatedItems": {"type":"string"} + }'; +SELECT JSON_SCHEMA_VALID(@schema_array, '[5, "string1", 1, 2, 3]'); + +SET @schema_array= '{ + "type": "array", + "prefixItems": [ + { "type": "number", "maximum": 10, "minimum":3}, + { "type": "string" } + ], + "additionalItems" : false, + "unevaluatedItems": {"type":"string"} + }'; +SELECT JSON_SCHEMA_VALID(@schema_array, '[5, "string1", 1, 2, 3]'); + +--echo # Using items in place of additionalItems + +SET @schema_array= '{ + "type": "array", + "prefixItems": [ + { "type": "number", "maximum": 10, "minimum":3}, + { "type": "string" } + ], + "items" : false, + "unevaluatedItems": {"type":"string"} + }'; +SELECT JSON_SCHEMA_VALID(@schema_array, '[5, "string1", 1, 2, 3]'); +SET @schema_array= '{ + "type": "array", + "prefixItems": [ + { "type": "number", "maximum": 10, "minimum":3}, + { "type": "string" } + ], + "items" : true, + "unevaluatedItems": {"type":"string"} + }'; +SELECT JSON_SCHEMA_VALID(@schema_array, '[5, "string1", 1, 2, 3]'); + +SET @schema_array= '{ + "type": "array", + "prefixItems": [ + { "type": "number", "maximum": 10, "minimum":3}, + { "type": "string" } + ], + "items" : {"type":"number"}, + "unevaluatedItems": {"type":"string"} + }'; +SELECT JSON_SCHEMA_VALID(@schema_array, '[5, "string1", 1, 2, 3]'); + +SET @schema_array= '{ + "type": "array", + "prefixItems": [ + { "type": "number", "maximum": 10, "minimum":3}, + { "type": "string" } + ], + "items" : {"type":"number"}, + "unevaluatedItems": {"type":"string"} + }'; +SELECT JSON_SCHEMA_VALID(@schema_array, '[5, "string1", "string2", "string3"]'); + +--echo # Removing items and additionalItems both + +SET @schema_array= '{ + "type": "array", + "prefixItems": [ + { "type": "number", "maximum": 10, "minimum":3}, + { "type": "string" } + ], + "unevaluatedItems": {"type":"number"} + }'; +SELECT JSON_SCHEMA_VALID(@schema_array, '[5, "string1", "string2", "string3"]'); +SELECT JSON_SCHEMA_VALID(@schema_array, '[5, "string1", 1, 2]'); + + +SET @schema_array= '{ + "type": "array", + "prefixItems": [ + { "type": "number", "maximum": 10, "minimum":3}, + { "type": "string" } + ], + "unevaluatedItems": true + }'; +SELECT JSON_SCHEMA_VALID(@schema_array, '[5, "string1", "string2", "string3"]'); + +SET @schema_array= '{ + "type": "array", + "prefixItems": [ + { "type": "number", "maximum": 10, "minimum":3}, + { "type": "string" } + ], + "unevaluatedItems": false + }'; +SELECT JSON_SCHEMA_VALID(@schema_array, '[5, "string1", "string2", "string3"]'); + +--echo # Removing items, additionalItems, unevaluatedItems + +SET @schema_array= '{ + "type": "array", + "prefixItems": [ + { "type": "number", "maximum": 10, "minimum":3}, + { "type": "string" } + ] + }'; +SELECT JSON_SCHEMA_VALID(@schema_array, '[5, "string1", "string2", "string3"]'); + + +--echo # Removing prefixItems + + +SET @schema_array= '{ + "type": "array", + "items": { "type": "number", "maximum": 10, "minimum":3}, + "additionalItems" : {"type":"number"}, + "unevaluatedItems": {"type":"string"} + }'; +SELECT JSON_SCHEMA_VALID(@schema_array, '[5, 6,"2", "string"]'); + + +SET @schema_array= '{ + "type": "array", + "items": { "type": "number", "maximum": 10, "minimum":3}, + "additionalItems" : {"type":"string"}, + "unevaluatedItems": {"type":"string"} + }'; +SELECT JSON_SCHEMA_VALID(@schema_array, '[5, 6,"2", "string"]'); + +SET @schema_array= '{ + "type": "array", + "items": true, + "additionalItems" : {"type":"string"}, + "unevaluatedItems": {"type":"string"} + }'; +SELECT JSON_SCHEMA_VALID(@schema_array, '[5, 6,"2", "string"]'); + +SET @schema_array= '{ + "type": "array", + "items": false, + "additionalItems" : {"type":"string"}, + "unevaluatedItems": {"type":"string"} + }'; +SELECT JSON_SCHEMA_VALID(@schema_array, '[5, 6,"2", "string"]'); +SELECT JSON_SCHEMA_VALID(@schema_array, '[]'); + +--echo # Removing prefixItems and additionalItems + +SET @schema_array= '{ + "type": "array", + "items": {"type":"string"}, + "unevaluatedItems": {"type":"string"} + }'; +SELECT JSON_SCHEMA_VALID(@schema_array, '["str1", "str2", 2]'); +SELECT JSON_SCHEMA_VALID(@schema_array, '["str1", "str2"]'); + +SET @schema_array= '{ + "type": "array", + "items": {"type":"string"}, + "unevaluatedItems": {"type":"number"} + }'; +SELECT JSON_SCHEMA_VALID(@schema_array, '["str1", "str2", 2]'); + +--echo # removing prefixItems, additionalItems and unevaluatedItems + +SET @schema_array= '{ + "type": "array", + "items": {"type":"string"} + }'; +SELECT JSON_SCHEMA_VALID(@schema_array, '["str1", "str2", 2]'); +SELECT JSON_SCHEMA_VALID(@schema_array, '["str1", "str2"]'); + +--echo # Checking that additionalItems alone has no effect on schema without items/prefixItems +--echo # regardless existence of unevaluatedItems + +SET @schema_array= '{ + "type": "array", + "additionalItems": {"type":"string"}, + "unevaluatedItems": {"type":"number"} + }'; +SELECT JSON_SCHEMA_VALID(@schema_array, '["str1", "str2", 1]'); + +SET @schema_array= '{ + "type": "array", + "additionalItems": true, + "unevaluatedItems": {"type":"number"} + }'; +SELECT JSON_SCHEMA_VALID(@schema_array, '["str1", "str2", 1]'); + +SET @schema_array= '{ + "type": "array", + "additionalItems": false, + "unevaluatedItems": {"type":"number"} + }'; +SELECT JSON_SCHEMA_VALID(@schema_array, '["str1", "str2", 1]'); + + +--echo # checking that unevaluatedItems alone can have effect on schema validation + +SET @schema_array= '{ + "type": "array", + "unevaluatedItems": {"type":"number"} + }'; +SELECT JSON_SCHEMA_VALID(@schema_array, '[1]'); + +SET @schema_array= '{ + "type": "array", + "unevaluatedItems": {"type":"number"} + }'; +SELECT JSON_SCHEMA_VALID(@schema_array, '[1, "str1"]'); + +SET @schema_array= '{ + "type": "array", + "unevaluatedItems": false + }'; +SELECT JSON_SCHEMA_VALID(@schema_array, '[1, "str1"]'); +SELECT JSON_SCHEMA_VALID(@schema_array, '[]'); +SELECT JSON_SCHEMA_VALID(@schema_array, '[1,2,3]'); + + +--echo # Object validation + +SET @property_names= '{ + "PropertyNames":{ + "pattern": "^I_" + } + }'; +SELECT JSON_SCHEMA_VALID(@property_names, '{"I_int1":3, "O_ob1":{"key1":"val1"}}'); +SELECT JSON_SCHEMA_VALID(@property_names, '{"I_int1":3, "I_ob1":{"key1":"val1"}}'); + +--echo # checking that when a match is found in properties or patternProperties, it must validate and +--echo # validation result affects the schema. If key is not found in properties or patternProperties, and +--echo # additionalProperties exists, it must validate regardless of existence or value for unevaluatedProperties +--echo # and the result of validation with additionalProperties affects result of whole schema + +SET @object_schema= '{ + "type":"object", + "properties": { + "key1":{"type":"string"}, + "key2":{"type":"number", "maximum":50} + }, + "patternProperties": { + "^I_": {"type":"number", "maximum":100}, + "^S_" : {"type":"string", "maxLength":4} + }, + "additionalProperties":{ + "type":"array", + "maxItems":5 + }, + "unevaluatedProperties":{"type":"string"} + }'; +SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":20, "S_":"abc", "some_prop1":[1,2,3]}'); +SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": [1,2,3], "I_int":20, "S_":"abc", "some_prop1":[1,2,3]}'); +SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":[1,2,3], "S_":"abc", "some_prop1":[1,2,3]}'); +SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":20, "S_":"abc", "some_prop1":"string1"}'); + +--echo # removing patternProperties to check that validation falls back on additionalProperties and +--echo # existence of unevaluatedProperties still does not change anything because of existence of additional +--echo # properties + + +SET @object_schema= '{ + "type":"object", + "properties": { + "key1":{"type":"string"}, + "key2":{"type":"number", "maximum":50} + }, + "additionalProperties":{ + "type":"array", + "maxItems":5 + }, + "unevaluatedProperties":{"type":"string"} + }'; +SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":20, "S_":"abc", "some_prop1":[1,2,3]}'); +SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":[1,2,3], "S_":[1,2,3], "some_prop1":[1,2,3]}'); + +--echo # Remvoing additionalProperties to check that validation falls back on unevaluatedProperties + +SET @object_schema= '{ + "type":"object", + "properties": { + "key1":{"type":"string"}, + "key2":{"type":"number", "maximum":50} + }, + "patternProperties": { + "^I_": {"type":"number", "maximum":100}, + "^S_" : {"type":"string", "maxLength":4} + }, + "unevaluatedProperties":{"type":"string"} + }'; +SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":10, "S_":"abc", "some_prop1":[1,2,3]}'); +SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":10, "S_":"abc", "some_prop1":"str"}'); + +--echo # Removing unevaluatedProperties has no effect on result when additionalProperties is present + +SET @object_schema= '{ + "type":"object", + "properties": { + "key1":{"type":"string"}, + "key2":{"type":"number", "maximum":50} + }, + "patternProperties": { + "^I_": {"type":"number", "maximum":100}, + "^S_" : {"type":"string", "maxLength":4} + }, + "additionalProperties":{ + "type":"array", + "maxItems":5 + } + }'; +SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":20, "S_":"abc", "some_prop1":[1,2,3]}'); +SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":20, "S_":"abc", "some_prop1":"str1"}'); + +SET @object_schema= '{ + "type":"object", + "properties": { + "key1":{"type":"string"}, + "key2":{"type":"number", "maximum":50} + }, + "patternProperties": { + "^I_": {"type":"number", "maximum":100}, + "^S_" : {"type":"string", "maxLength":4} + }, + "additionalProperties": false + }'; +SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":20, "S_":"abc", "some_prop1":[1,2,3]}'); + +SET @object_schema= '{ + "type":"object", + "properties": { + "key1":{"type":"string"}, + "key2":{"type":"number", "maximum":50} + }, + "patternProperties": { + "^I_": {"type":"number", "maximum":100}, + "^S_" : {"type":"string", "maxLength":4} + }, + "additionalProperties": true + }'; +SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":20, "S_":"abc", "some_prop1":[1,2,3]}'); + + +--echo # Checking that in absence of additionalProperties, validation falls back on evaluatedProperties + +SET @object_schema= '{ + "type":"object", + "properties": { + "key1":{"type":"string"}, + "key2":{"type":"number", "maximum":50} + }, + "patternProperties": { + "^I_": {"type":"number", "maximum":100}, + "^S_" : {"type":"string", "maxLength":4} + }, + "unevaluatedProperties":{"type":"string"} + }'; +SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 3, "I_int":20, "S_":"abc", "some_prop1":"str1"}'); +SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 3, "I_int":20, "S_":"abc", "some_prop1":[1,2,3]}'); + +SET @object_schema= '{ + "type":"object", + "properties": { + "key1":{"type":"string"}, + "key2":{"type":"number", "maximum":50} + }, + "unevaluatedProperties":{"type":"string"} + }'; +SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 3, "I_int":"str2", "S_":"abc", "some_prop1":[1,2,3]}'); +SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 3, "I_int":20, "S_":"abc", "some_prop1":[1,2,3]}'); + +--echo # Properties to check if patternProperties get evaluated but keys not found in patternProperties get evaluated against +--echo # additionalProperties regardless of existence of unevaluatedProperperties + +SET @object_schema= '{ + "type":"object", + "patternProperties": { + "^I_": {"type":"number", "maximum":100}, + "^S_" : {"type":"string", "maxLength":4} + }, + "additionalProperties":{ + "type":"array", + "maxItems":5 + }, + "unevaluatedProperties":{"type":"string"} + }'; +SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":20, "S_":"abc", "some_prop1":[1,2,3]}'); +SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":[1, 2, 3], "key2": [1, 2, 3], "I_int":20, "S_":"abc", "some_prop1":[1,2,3]}'); + +SET @object_schema= '{ + "type":"object", + "patternProperties": { + "^I_": {"type":"number", "maximum":100}, + "^S_" : {"type":"string", "maxLength":4} + }, + "additionalProperties":{ + "type":"array", + "maxItems":5 + } + }'; +SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":20, "S_":"abc", "some_prop1":[1,2,3]}'); +SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":[1, 2, 3], "key2": [1, 2, 3], "I_int":20, "S_":"abc", "some_prop1":[1,2,3]}'); + +--echo # Checking that in absence of additionalProperties and properties, the keys not found in patternProperties are +--echo # evaluated against unevaluatedProperties +SET @object_schema= '{ + "type":"object", + "patternProperties": { + "^I_": {"type":"number", "maximum":100}, + "^S_" : {"type":"string", "maxLength":4} + }, + "unevaluatedProperties":{"type":"string"} + }'; +SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":20, "S_":"abc", "some_prop1":[1,2,3]}'); +SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": "str2", "I_int":20, "S_":"abc", "some_prop1":"str1"}'); + +--echo # checking that in absence of properties, additionalProperties and unevaluatedPropoerties, the keys that are +--echo # not found are considered validated. + +SET @object_schema= '{ + "type":"object", + "patternProperties": { + "^I_": {"type":"number", "maximum":100}, + "^S_" : {"type":"string", "maxLength":4} + } + }'; +SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":20, "S_":"abc", "some_prop1":[1,2,3]}'); + +--echo # checking that additionalProperties are evaluated in absence of patternProperties and properties, regardless +--echo # of presence of unevaluatedProperties + +SET @object_schema= '{ + "type":"object", + "additionalProperties":{ + "type":"array", + "maxItems":5 + }, + "unevaluatedProperties":{"type":"string"} + }'; +SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":20, "S_":"abc", "some_prop1":[1,2,3]}'); +SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":[1,2,3], "key2": [1,2,3], "I_int":[1,2,3], "S_":[1,2,3], "some_prop1":[1,2,3]}'); + +SET @object_schema= '{ + "type":"object", + "additionalProperties":{ + "type":"array", + "maxItems":5 + } + }'; +SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": 10, "I_int":20, "S_":"abc", "some_prop1":[1,2,3]}'); +SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":[1,2,3], "key2": [1,2,3], "I_int":[1,2,3], "S_":[1,2,3], "some_prop1":[1,2,3]}'); + +--echo # Checking that in absence of properties, patternProperties and additionalProperties, validation falls back on unevaluatedProperties + +SET @object_schema= '{ + "type":"object", + "unevaluatedProperties":{"type":"string"} + }'; +SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": "str1", "I_int":"str2", "S_":"abc", "some_prop1":"str3"}'); +SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":[1,2,3], "key2": [1,2,3], "I_int":[1,2,3], "S_":[1,2,3], "some_prop1":[1,2,3]}'); + +SET @object_schema= '{ + "type":"object", + "unevaluatedProperties": false + }'; +SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": "str1", "I_int":"str2", "S_":"abc", "some_prop1":"str3"}'); +SELECT JSON_SCHEMA_VALID(@object_schema, '{}'); + +SET @object_schema= '{ + "type":"object", + "unevaluatedProperties": true + }'; +SELECT JSON_SCHEMA_VALID(@object_schema, '{"key1":"val1", "key2": "str1", "I_int":"str2", "S_":"abc", "some_prop1":"str3"}'); +SELECT JSON_SCHEMA_VALID(@object_schema, '{}'); + +SET @json_schema_dependent_schemas='{ + "type": "object", + "properties": { + "str1": { "type": "string" }, + "num1": { "type": "number" } + }, + + "required": ["str1"], + + "dependentSchemas": { + "num1": { + "properties": { + "str2": { "type": "string" } + }, + "required": ["str2"] + } + } +}'; +SELECT JSON_SCHEMA_VALID(@json_schema_dependent_schemas, '{ "str1": "str", "num1":4}'); +SELECT JSON_SCHEMA_VALID(@json_schema_dependent_schemas, '{ "str1": "str"}'); + +--echo # Validating logic + +SET @not_schema= '{ + "not":{ + "maximum": 4 + } + }'; +SELECT JSON_SCHEMA_VALID(@not_schema, '{"I_int1":3, "O_ob1":{"key1":"val1"}}'); +SELECT JSON_SCHEMA_VALID(@not_schema, '3'); +SELECT JSON_SCHEMA_VALID(@not_schema, '10'); + +SET @not_schema= '{ + "not":{ + "properties": { + "num1" : {"type":"number", "maximum":5}, + "string1" : { "maxLength": 3} + } + } + }'; +SELECT JSON_SCHEMA_VALID(@not_schema, '{"num1":10, "string2":"abcd"}'); +SELECT JSON_SCHEMA_VALID(@not_schema, '{"num1":2, "string2":"abcd"}'); + +SET @any_of_schema= '{ + "anyOf":[{ + "properties": { + "num1" : {"type":"number", "maximum":5}, + "string1" : { "maxLength": 3} + } + }, + { + "properties":{ + "num1" : {"type":"number", "maximum": 1}, + "string1" : { "maxLength":5} + } + } + ] + }'; +SELECT JSON_SCHEMA_VALID(@any_of_schema, '{"num1":2, "string1":"abcd"}'); +SELECT JSON_SCHEMA_VALID(@any_of_schema, '{"num1":2, "string1":"abc"}'); + +SET @any_of_schema= '{ + "anyOf": [ + {"type":"number", "maximum":5}, + {"type":"string"} + ] + }'; +SELECT JSON_SCHEMA_VALID(@any_of_schema, '2'); +SELECT JSON_SCHEMA_VALID(@any_of_schema, '6'); +SELECT JSON_SCHEMA_VALID(@any_of_schema, '[1, 2, 3]'); + +SET @one_of_schema= '{ + "oneOf":[{ + "properties": { + "num1" : {"type":"number", "maximum":5}, + "string1" : { "maxLength": 3} + } + }, + { + "properties":{ + "num1" : {"type":"number", "maximum": 1}, + "string1" : { "maxLength":5} + } + } + ] + }'; +SELECT JSON_SCHEMA_VALID(@one_of_schema, '{"num1":2, "string1":"abcd"}'); +SELECT JSON_SCHEMA_VALID(@one_of_schema, '{"num1":2, "string1":"abc"}'); + +SET @one_of_schema= '{ + "oneOf": [ + {"type":"number", "maximum":5}, + {"type":"number", "maximum":3} + ] + }'; +SELECT JSON_SCHEMA_VALID(@one_of_schema, '2'); +SELECT JSON_SCHEMA_VALID(@one_of_schema, '4'); + +SET @all_of_schema= '{ + "allOf":[{ + "properties": { + "num1" : {"type":"number", "maximum":5}, + "string1" : { "maxLength": 3} + } + }, + { + "properties":{ + "num1" : {"type":"number", "maximum": 1}, + "string1" : { "maxLength":5} + } + } + ] + }'; +SELECT JSON_SCHEMA_VALID(@all_of_schema, '{"num1":2, "string1":"abcd"}'); + +SET @all_of_schema= '{ + "allOf":[ + { + "properties":{ + "num1": {"type":"number"}, + "string1": {"type":"string"} + } + }, + { + "properties":{ + "num1": {"maximum":10}, + "string1": {"maxLength":5} + } + } + ] + }'; +SELECT JSON_SCHEMA_VALID(@all_of_schema, '{"num1":5, "string1":"abc"}'); +SELECT JSON_SCHEMA_VALID(@all_of_schema, '{"num1":5, "string1":"foobar"}'); + +SET @condition_schema= '{ + "if" : {"maximum":30, "multipleOf":3}, + "then":{"minimum":5}, + "else":{"minimum":10} + }'; +SELECT JSON_SCHEMA_VALID(@condition_schema, '6'); +SELECT JSON_SCHEMA_VALID(@condition_schema, '4'); +SELECT JSON_SCHEMA_VALID(@condition_schema, '13'); + +SET @condition_schema= '{ + "if" : {"maximum":30, "multipleOf":3} + }'; +SELECT JSON_SCHEMA_VALID(@condition_schema, '6'); +SELECT JSON_SCHEMA_VALID(@condition_schema, '7'); + +SET @condition_schema= '{ + "then":{"minimum":5}, + "else":{"minimum":10} + }'; +SELECT JSON_SCHEMA_VALID(@condition_schema, '4'); +SELECT JSON_SCHEMA_VALID(@condition_schema, '11'); + +--echo # Checking unevaluatedProperperties with logical properties + +SET @all_of_unevaluated='{ + "allOf": [ + { + "type": "object", + "properties": { + "street_address": { "type": "string" }, + "city": { "type": "string" }, + "state": { "type": "string" } + }, + "required": ["street_address", "city", "state"] + } + ], + + "properties": { + "type": { "enum": ["residential", "business"] } + }, + "required": ["type"], + "unevaluatedProperties": false +}'; +SELECT JSON_SCHEMA_VALID(@all_of_unevaluated, '{ + "street_address": "1600 Pennsylvania Avenue NW", + "city": "Washington", + "state": "DC", + "type": "business" +}'); + +SET @all_of_unevaluated='{ + "allOf": [ + { + "type": "object", + "properties": { + "street_address": { "type": "string" }, + "city": { "type": "string" }, + "state": { "type": "string" } + }, + "required": ["street_address", "city", "state"] + } + ], + + "unevaluatedProperties": {"type":"number"} +}'; +SELECT JSON_SCHEMA_VALID(@all_of_unevaluated, '{ + "street_address": "1600 Pennsylvania Avenue NW", + "city": "Washington", + "state": "DC", + "type": "business" +}'); + +SET @any_of_unevaluated='{ + "anyOf": [ + { + "type": "object", + "properties": { + "street_address": { "type": "string" }, + "city": { "type": "string" }, + "state": { "type": "string" } + }, + "required": ["street_address", "city", "state"] + } + ], + + "unevaluatedProperties": {"type":"number"} +}'; +SELECT JSON_SCHEMA_VALID(@any_of_unevaluated, '{ + "street_address": "1600 Pennsylvania Avenue NW", + "city": "Washington", + "state": "DC", + "type": "business" +}'); + +SET @all_of_unevaluated='{ + "allOf": [ + { + "type": "array" + }, + { + "maxItems":10 + }, + { + "prefixItems": [ {"type":"number"}, {"type":"string"}], + "additionalItems":{"type":"array"} + } + ], + + "unevaluatedItems": {"type":"number"} +}'; +SELECT JSON_SCHEMA_VALID(@all_of_unevaluated, '[1, "str", 2]'); + +SET @all_of_unevaluated='{ + "anyOf": [ + { + "type": "array" + }, + { + "maxItems":10 + }, + { + "prefixItems": [ {"type":"number"}, {"type":"string"}], + "additionalItems":{"type":"array"} + } + ], + + "unevaluatedItems": {"type":"number"} +}'; +SELECT JSON_SCHEMA_VALID(@all_of_unevaluated, '[1, "str", 2]'); + +SET @all_of_unevaluated='{ + "oneOf": [ + { + "type": "array" + }, + { + "maxItems":10 + }, + { + "prefixItems": [ {"type":"number"}, {"type":"string"}], + "additionalItems":{"type":"array"} + } + ], + + "unevaluatedItems": {"type":"number"} +}'; +SELECT JSON_SCHEMA_VALID(@all_of_unevaluated, '[1, "str", 2]'); + +--echo # Media string + +SET @schema_media_string= '{ + "type": "string", + "contentMediaType": "text/html" +}'; +SELECT JSON_SCHEMA_VALID(@schema_media_string, '"str1"'); + +SET @schema_reference= '{"$ref": "http://example.com/custom-email-validator.json#"}'; +--error ER_JSON_SCHEMA_KEYWORD_UNSUPPORTED +SELECT JSON_SCHEMA_VALID(@schema_reference, '{}'); + +SET @schema_reference= '{"$id": "http://example.com/custom-email-validator.json#"}'; +--error ER_JSON_SCHEMA_KEYWORD_UNSUPPORTED +SELECT JSON_SCHEMA_VALID(@schema_reference, '{}'); + +SET @schema_reference= '{"$anchor": "http://example.com/custom-email-validator.json#"}'; +--error ER_JSON_SCHEMA_KEYWORD_UNSUPPORTED +SELECT JSON_SCHEMA_VALID(@schema_reference, '{}'); + +SET @schema_reference= '{"$defs": "http://example.com/custom-email-validator.json#"}'; +--error ER_JSON_SCHEMA_KEYWORD_UNSUPPORTED +SELECT JSON_SCHEMA_VALID(@schema_reference, '{}'); + + +--echo # End of 11.1 test diff --git a/mysql-test/main/json_debug_nonembedded.result b/mysql-test/main/json_debug_nonembedded.result index e759cafa38b..0e7458edd9f 100644 --- a/mysql-test/main/json_debug_nonembedded.result +++ b/mysql-test/main/json_debug_nonembedded.result @@ -7,6 +7,13 @@ SET @json1= '{"key1":"val1"}'; SET @json2= '{"key1":"val1"}'; SELECT JSON_OVERLAPS(@json1, @json2); ERROR HY000: Thread stack overrun: 'used bytes' used of a 'available' byte stack, and 'X' bytes needed. Consider increasing the thread_stack system variable. +SET @schema_array= '{ + "type":"array", + "items": {"type":"number"}, + "maxItems": 4, + "minItems": 2}'; +SELECT JSON_SCHEMA_VALID(@schema_array, '[1, 2, 3]'); +ERROR HY000: Thread stack overrun: 'used bytes' used of a 'available' byte stack, and 'X' bytes needed. Consider increasing the thread_stack system variable. SET @@debug_dbug= @saved_dbug; # # End of 10.9 test diff --git a/mysql-test/main/json_debug_nonembedded.test b/mysql-test/main/json_debug_nonembedded.test index 5813b46e556..fb4fe67b5d3 100644 --- a/mysql-test/main/json_debug_nonembedded.test +++ b/mysql-test/main/json_debug_nonembedded.test @@ -15,6 +15,15 @@ SET @json2= '{"key1":"val1"}'; --error ER_STACK_OVERRUN_NEED_MORE SELECT JSON_OVERLAPS(@json1, @json2); +SET @schema_array= '{ + "type":"array", + "items": {"type":"number"}, + "maxItems": 4, + "minItems": 2}'; +--replace_regex /overrun: [0-9]* bytes used of a [0-9]* byte stack, and [0-9]* bytes needed/overrun: 'used bytes' used of a 'available' byte stack, and 'X' bytes needed/ +--error ER_STACK_OVERRUN_NEED_MORE +SELECT JSON_SCHEMA_VALID(@schema_array, '[1, 2, 3]'); + SET @@debug_dbug= @saved_dbug; --echo # |