📣

TiDB Cloud Serverless 现已更名为
Starter
!此页面由 AI 自动翻译,英文原文请见
此处。

用于验证 JSON 文档的 JSON 函数

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

JSON_SCHEMA_VALID()

JSON_SCHEMA_VALID(schema, json_doc) 函数用于根据 schema 验证 JSON 文档,以确保数据的完整性和一致性。

该函数可以与 CHECK 约束结合使用,在表被修改时自动进行 schema 验证。

此函数遵循 JSON Schema 规范

支持的校验关键字如下:

校验关键字适用类型描述
type任意检查类型(如 arraystring
enum任意检查值是否在指定的值数组中
const任意类似于 enum,但只针对单个值
allOf任意匹配所有指定的 schema
anyOf任意匹配任意一个指定的 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 可能会接受,但 TiDB 会返回错误。注意 "sting" 拼写错误,应该为 "string"
  • MySQL 使用的是较早的 JSON Schema 标准草案版本。

另请参阅

文档内容是否有帮助?