📣
TiDB Cloud Essential 开放公测中。立即试用 →
​
AI
应用开发
最佳实践
API
​

验证 JSON 文档的函数

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

JSON_SCHEMA_VALID()

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

验证关键词如下：

验证关键词适用于描述
typeAny测试类型，如 array、string
enumAny测试某个值是否在指定的值数组中
constAny与 enum 相似，但只适用于单个值
allOfAny匹配所有指定的 schema
anyOfAny匹配任意指定的 schema
multipleOfnumber/integer测试值是否是指定值的倍数
maximumnumber/integer测试数值是否小于最大值（包括最大值在内）
exclusiveMaximumnumber/integer测试数值是否小于最大值（不包括最大值）
minimumnumber/integer测试数值是否大于最小值（包括最小值在内）
exclusiveMinimumnumber/integer测试值是否大于最小值（不包括最小值）
maxlengthstring测试值的长度是否不超过指定值
minLengthstring测试值的长度是否不低于指定值
formatstring测试字符串是否符合指定格式
patternstring测试字符串是否与模式匹配
itemsarray适用于数组项的 schema
prefixItemsarray适用于数组的位置项的 schema
maxItemsarray测试数组中的元素数量是否不超过指定值
minItemsarray测试数组中的元素数量是否不低于指定值
uniqueItemsarray测试数组中的元素是否唯一，true/false
containsarray为数组中的元素设置 schema
maxContainsarray与 contains 一起使用时，用于测试某些元素出现的最多次数
minContainsarray与 contains 一起使用时，用于测试某些元素出现的最少次数
propertiesobject适用于对象属性的 schema
patternPropertiesobject根据属性名称的模式匹配，应用于某些属性的 schema
additionalPropertiesobject是否允许额外的属性，true/false
minPropertiesobject测试对象的最小属性数量
maxPropertiesobject测试对象的最大属性数量
requiredobject必须填写的属性名称

示例：

下面一些示例使用了如下 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。

另请参阅

文档内容是否有帮助？