验证 JSON 文档的函数

TiDB 支持使用 MySQL 8.0 中提供的大部分用于验证 JSON 文档的 JSON 函数。

`JSON_SCHEMA_VALID()`

JSON_SCHEMA_VALID(schema, json_doc) 函数根据 schema 验证 JSON 文档，确保数据的完整性和一致性。该函数可以与 CHECK 约束一起使用，以便在修改表时自动进行 schema 验证。该函数遵循 JSON Schema specification。

验证关键词如下：

验证关键词	适用于	描述
`type`	Any	测试类型，如 `array`、`string`
`enum`	Any	测试某个值是否在指定的值数组中
`const`	Any	与 `enum` 相似，但只适用于单个值
`allOf`	Any	匹配所有指定的 schema
`anyOf`	Any	匹配任意指定的 schema
`multipleOf`	`number`/`integer`	测试值是否是指定值的倍数
`maximum`	`number`/`integer`	测试数值是否小于最大值（包括最大值在内）
`exclusiveMaximum`	`number`/`integer`	测试数值是否小于最大值（不包括最大值）
`minimum`	`number`/`integer`	测试数值是否大于最小值（包括最小值在内）
`exclusiveMinimum`	`number`/`integer`	测试值是否大于最小值（不包括最小值）
`maxlength`	`string`	测试值的长度是否不超过指定值
`minLength`	`string`	测试值的长度是否不低于指定值
`format`	`string`	测试字符串是否符合指定格式
`pattern`	`string`	测试字符串是否与模式匹配
`items`	`array`	适用于数组项的 schema
`prefixItems`	`array`	适用于数组的位置项的 schema
`maxItems`	`array`	测试数组中的元素数量是否不超过指定值
`minItems`	`array`	测试数组中的元素数量是否不低于指定值
`uniqueItems`	`array`	测试数组中的元素是否唯一，`true`/`false`
`contains`	`array`	为数组中的元素设置 schema
`maxContains`	`array`	与 `contains` 一起使用时，用于测试某些元素出现的最多次数
`minContains`	`array`	与 `contains` 一起使用时，用于测试某些元素出现的最少次数
`properties`	`object`	适用于对象属性的 schema
`patternProperties`	`object`	根据属性名称的模式匹配，应用于某些属性的 schema
`additionalProperties`	`object`	是否允许额外的属性，`true`/`false`
`minProperties`	`object`	测试对象的最小属性数量
`maxProperties`	`object`	测试对象的最大属性数量
`required`	`object`	必须填写的属性名称

示例：

下面一些示例使用了如下 JSON 文档：

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

使用用户自定义的变量存放 JSON 文档。

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

先测试类型：

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)

从上面的输出中可以看到，@j 的类型是 object，与 JSON_TYPE() 的输出结果一致。

现在验证某些属性是否存在。

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

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

在前面的输出中可以看到，fruits 和 vegetables 的属性是存在的，验证成功。

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)

在前面的输出中可以看到，验证 fruits、vegetables 和 grains 属性是否存在失败了，因为 grains 不存在。

现在验证 fruits 是否为数组。

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)

从上面的输出结果，可以确认 fruits 是数组。

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)

上面的输出结果显示 fruits 不是字符串。

现在验证数组中的元素数量。

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)

前面的输出结果显示，fruits 是一个至少包含 3 个元素的数组。

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)

前面的输出结果显示，fruits 不是一个至少包含 4 个元素的数组，它没有达到元素数量的最低要求。

对于整数值，可以检查它们是否在某个范围内。

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)

对于字符串，可以验证是否匹配指定的模式。

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)

你可以检查一个值是否符合指定的命名格式。可验证的格式包括：ipv4、ipv6、time、date、duration、email、hostname、uuid 和 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)

你还可以使用 enum 来检查一个字符串是否在一个数组中。

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)

你可以使用 anyOf 将某些要求组合起来，验证是否满足其中任意一个要求。

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 兼容性

如果 JSON_SCHEMA_VALID() 中待验证的 schema 无效（如 {"type"： "sting"}），MySQL 可能会接受该 schema ，但 TiDB 会返回错误。注意这里的 "sting" 存在拼写错误，应为 "string"。
MySQL 使用的是较早 draft 版本的 JSON Schema standard。

验证 JSON 文档的函数

JSON_SCHEMA_VALID()

MySQL 兼容性

另请参阅

`JSON_SCHEMA_VALID()`