验证 JSON 文档的函数

本文档介绍用于验证 JSON 文档的函数。

JSON_SCHEMA_VALID()

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

验证关键词如下:

验证关键词适用于描述
typeAny测试类型,如 arraystring
enumAny测试某个值是否在指定的值数组中
constAnyenum 相似,但只适用于单个值
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
maxContainsarraycontains 一起使用时,用于测试某些元素出现的最多次数
minContainsarraycontains 一起使用时,用于测试某些元素出现的最少次数
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)

在前面的输出中可以看到,fruitsvegetables 的属性是存在的,验证成功。

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)

在前面的输出中可以看到,验证 fruitsvegetablesgrains 属性是否存在失败了,因为 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)

你可以检查一个值是否符合指定的命名格式。可验证的格式包括:ipv4ipv6timedatedurationemailhostnameuuiduri

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。

另请参阅

文档内容是否有帮助?