📣
TiDB Cloud Essential is now in public preview. Try it out →
​
Sign InStart for Free
AI
Developer
Best Practices
API
​
Sign InStart for Free

JSON Functions That Validate JSON Documents

TiDB supports most of the JSON schema validation functions available in MySQL 8.0.

JSON_SCHEMA_VALID()

The JSON_SCHEMA_VALID(schema, json_doc) function validate a JSON document against a schema to ensure data integrity and consistency.

This can be used together with a CHECK constraint to do automatic schema validation when a table is modified.

This function follows the JSON Schema specification.

The supported validation keywords are as follows:

Validation keywordApplied typeDescription
typeAnyTests the type (such as array and string)
enumAnyTests if a value is in the specified array of values
constAnySimilar to enum, but for a single value
allOfAnyMatches all of the specified schemas
anyOfAnyMatches any of the specified schemas
multipleOfnumber/integerTests if the value is a multiple of the specified value
maximumnumber/integerTests if the value is below the maximum (inclusive)
exclusiveMaximumnumber/integerTests if the value is below the maximum (exclusive)
minimumnumber/integerTests if the value is above the minimum (inclusive)
exclusiveMinimumnumber/integerTests if the value is above the minimum (exclusive)
maxlengthstringTests if the length of the value is not exceeding the specified value
minLengthstringTests if the length of the value is at least the specified value
formatstringTests if a string matches a named format
patternstringTests if a string matches a pattern
itemsarraySchema to apply to the items of an array
prefixItemsarraySchema to apply to positional items of an array
maxItemsarrayTests if the number of items in the array is not exceeding the specified value
minItemsarrayTests if the number of items in the array is at least the specified value
uniqueItemsarrayTests if the items in the array are unique, true/false
containsarraySets schema for items contained in the array
maxContainsarrayUsed together with contains to test the maximum times an item can be present
minContainsarrayUsed together with contains to test the minimum times an item can be present
propertiesobjectSchema to apply to the properties of an object
patternPropertiesobjectSchema to apply to certain properties based on pattern matching of the property name
additionalPropertiesobjectWhether additional properties are allowed or not, true/false
minPropertiesobjectTests the minimum number of properties that an object can have
maxPropertiesobjectTests the maximum number of properties that an object can have
requiredobjectTests if the specified property names exist in an object

Examples:

For some of the examples, use this JSON document:

{
    "fruits": [
        "orange",
        "apple",
        "pear"
    ],
    "vegetables": [
        "carrot",
        "pepper",
        "kale"]
}

Use a user defined variable to hold the JSON document.

SET @j := '{"fruits": ["orange", "apple", "pear"], "vegetables": ["carrot", "pepper", "kale"]}';

Start by testing the type:

SELECT JSON_SCHEMA_VALID('{"type": "object"}',@j);

+--------------------------------------------+
| JSON_SCHEMA_VALID('{"type": "object"}',@j) |
+--------------------------------------------+
|                                          1 |
+--------------------------------------------+
1 row in set (0.00 sec)

SELECT JSON_SCHEMA_VALID('{"type": "array"}',@j);

+-------------------------------------------+
| JSON_SCHEMA_VALID('{"type": "array"}',@j) |
+-------------------------------------------+
|                                         0 |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_TYPE(@j);

+---------------+
| JSON_TYPE(@j) |
+---------------+
| OBJECT        |
+---------------+
1 row in set (0.00 sec)

As you can see in the preceding output, the type of @j is object. This matches with the output of JSON_TYPE().

Now validate the presence of certain attributes.

SELECT JSON_SCHEMA_VALID('{"required": ["fruits","vegetables"]}',@j);

+---------------------------------------------------------------+
| JSON_SCHEMA_VALID('{"required": ["fruits","vegetables"]}',@j) |
+---------------------------------------------------------------+
|                                                             1 |
+---------------------------------------------------------------+
1 row in set (0.00 sec)

In the preceding output, you can see that the validation of the presence of the fruits and vegetables attributes succeeds.

SELECT JSON_SCHEMA_VALID('{"required": ["fruits","vegetables","grains"]}',@j);

+------------------------------------------------------------------------+
| JSON_SCHEMA_VALID('{"required": ["fruits","vegetables","grains"]}',@j) |
+------------------------------------------------------------------------+
|                                                                      0 |
+------------------------------------------------------------------------+
1 row in set (0.00 sec)

In the preceding output, you can see that the validation of the presence of the fruits, vegetables and grains attributes fails because grains is not present.

Now validate that fruits is an array.

SELECT JSON_SCHEMA_VALID('{"properties": {"fruits": {"type": "array"}}}',@j);

+-----------------------------------------------------------------------+
| JSON_SCHEMA_VALID('{"properties": {"fruits": {"type": "array"}}}',@j) |
+-----------------------------------------------------------------------+
|                                                                     1 |
+-----------------------------------------------------------------------+
1 row in set (0.01 sec)

The preceding output confirms that fruits is an array.

SELECT JSON_SCHEMA_VALID('{"properties": {"fruits": {"type": "string"}}}',@j);

+------------------------------------------------------------------------+
| JSON_SCHEMA_VALID('{"properties": {"fruits": {"type": "string"}}}',@j) |
+------------------------------------------------------------------------+
|                                                                      0 |
+------------------------------------------------------------------------+
1 row in set (0.00 sec)

The preceding output shows that fruits is not a string.

Now verify the number of items in the array.

SELECT JSON_SCHEMA_VALID('{"properties": {"fruits": {"type": "array", "minItems": 3}}}',@j);

+--------------------------------------------------------------------------------------+
| JSON_SCHEMA_VALID('{"properties": {"fruits": {"type": "array", "minItems": 3}}}',@j) |
+--------------------------------------------------------------------------------------+
|                                                                                    1 |
+--------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

The preceding output shows that fruits is an array with at least 3 items.

SELECT JSON_SCHEMA_VALID('{"properties": {"fruits": {"type": "array", "minItems": 4}}}',@j);

+--------------------------------------------------------------------------------------+
| JSON_SCHEMA_VALID('{"properties": {"fruits": {"type": "array", "minItems": 4}}}',@j) |
+--------------------------------------------------------------------------------------+
|                                                                                    0 |
+--------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

The preceding output shows that fruits is not an array with at least 4 items. This is because it does not meet the minimum number of items.

For integers values, you can check if they are in a certain range.

SELECT JSON_SCHEMA_VALID('{"type": "integer", "minimum": 40, "maximum": 45}', '42');
+------------------------------------------------------------------------------+
| JSON_SCHEMA_VALID('{"type": "integer", "minimum": 40, "maximum": 45}', '42') |
+------------------------------------------------------------------------------+
|                                                                            1 |
+------------------------------------------------------------------------------+
1 row in set (0.01 sec)

SELECT JSON_SCHEMA_VALID('{"type": "integer", "minimum": 40, "maximum": 45}', '123');

+-------------------------------------------------------------------------------+
| JSON_SCHEMA_VALID('{"type": "integer", "minimum": 40, "maximum": 45}', '123') |
+-------------------------------------------------------------------------------+
|                                                                             0 |
+-------------------------------------------------------------------------------+
1 row in set (0.00 sec)

For a string, you can validate whether it matches a certain pattern.

SELECT JSON_SCHEMA_VALID('{"type": "string", "pattern": "^Ti"}', '"TiDB"');

+---------------------------------------------------------------------+
| JSON_SCHEMA_VALID('{"type": "string", "pattern": "^Ti"}', '"TiDB"') |
+---------------------------------------------------------------------+
|                                                                   1 |
+---------------------------------------------------------------------+
1 row in set (0.00 sec)

SELECT JSON_SCHEMA_VALID('{"type": "string", "pattern": "^Ti"}', '"PingCAP"');

+------------------------------------------------------------------------+
| JSON_SCHEMA_VALID('{"type": "string", "pattern": "^Ti"}', '"PingCAP"') |
+------------------------------------------------------------------------+
|                                                                      0 |
+------------------------------------------------------------------------+
1 row in set (0.00 sec)

You can check whether a value matches a certain named format. The formats that can be validated include ipv4, ipv6, time, date, duration, email, hostname, uuid, and uri.

SELECT JSON_SCHEMA_VALID('{"format": "ipv4"}', '"127.0.0.1"');

+--------------------------------------------------------+
| JSON_SCHEMA_VALID('{"format": "ipv4"}', '"127.0.0.1"') |
+--------------------------------------------------------+
|                                                      1 |
+--------------------------------------------------------+
1 row in set (0.00 sec)

SELECT JSON_SCHEMA_VALID('{"format": "ipv4"}', '"327.0.0.1"');

+--------------------------------------------------------+
| JSON_SCHEMA_VALID('{"format": "ipv4"}', '"327.0.0.1"') |
+--------------------------------------------------------+
|                                                      0 |
+--------------------------------------------------------+
1 row in set (0.00 sec)

You can also use enum to check if a string is in an array.

SELECT JSON_SCHEMA_VALID('{"enum": ["TiDB", "MySQL"]}', '"TiDB"');

+------------------------------------------------------------+
| JSON_SCHEMA_VALID('{"enum": ["TiDB", "MySQL"]}', '"TiDB"') |
+------------------------------------------------------------+
|                                                          1 |
+------------------------------------------------------------+
1 row in set (0.00 sec)

SELECT JSON_SCHEMA_VALID('{"enum": ["TiDB", "MySQL"]}', '"MySQL"');

+-------------------------------------------------------------+
| JSON_SCHEMA_VALID('{"enum": ["TiDB", "MySQL"]}', '"MySQL"') |
+-------------------------------------------------------------+
|                                                           1 |
+-------------------------------------------------------------+
1 row in set (0.00 sec)

SELECT JSON_SCHEMA_VALID('{"enum": ["TiDB", "MySQL"]}', '"SQLite"');

+--------------------------------------------------------------+
| JSON_SCHEMA_VALID('{"enum": ["TiDB", "MySQL"]}', '"SQLite"') |
+--------------------------------------------------------------+
|                                                            0 |
+--------------------------------------------------------------+
1 row in set (0.00 sec)

With anyOf, you can combine certain requirements and validate whether any of the requirements is met.

SELECT JSON_SCHEMA_VALID('{"anyOf": [{"type": "string"},{"type": "integer"}]}', '"TiDB"');

+------------------------------------------------------------------------------------+
| JSON_SCHEMA_VALID('{"anyOf": [{"type": "string"},{"type": "integer"}]}', '"TiDB"') |
+------------------------------------------------------------------------------------+
|                                                                                  1 |
+------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

SELECT JSON_SCHEMA_VALID('{"anyOf": [{"type": "string"},{"type": "integer"}]}', '["TiDB", "MySQL"]');

+-----------------------------------------------------------------------------------------------+
| JSON_SCHEMA_VALID('{"anyOf": [{"type": "string"},{"type": "integer"}]}', '["TiDB", "MySQL"]') |
+-----------------------------------------------------------------------------------------------+
|                                                                                             0 |
+-----------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

SELECT JSON_SCHEMA_VALID('{"anyOf": [{"type": "string"},{"type": "integer"}]}', '5');

+-------------------------------------------------------------------------------+
| JSON_SCHEMA_VALID('{"anyOf": [{"type": "string"},{"type": "integer"}]}', '5') |
+-------------------------------------------------------------------------------+
|                                                                             1 |
+-------------------------------------------------------------------------------+
1 row in set (0.00 sec)

MySQL compatibility

  • If the schema to be validated in JSON_SCHEMA_VALID() is invalid (such as {"type": "sting"}), MySQL might accept it, but TiDB returns an error. Note that there is a spelling mistake in "sting", which should be "string".
  • MySQL uses an older draft version of the JSON Schema standard.

See also

Was this page helpful?