JSON_SCHEMA_VALID()

JSON_SCHEMA_VALID() 根据 JSON Schema（draft 4）验证 JSON 文档，文档符合 schema 时返回 1，不符合时返回 0，任一参数为 NULL 时返回 NULL。

函数说明

JSON_SCHEMA_VALID() 检查 JSON 文档是否满足 JSON Schema 中定义的约束。schema 必须是一个 JSON 对象。支持的 schema 关键字包括：

  • type（string、number、object、array）

  • 带嵌套类型约束的 properties

  • required 属性名数组

  • 数值类型的 minimum 和 maximum

  • 字符串类型的 minLength 和 maxLength

  • 字符串的正则验证 pattern

  • 枚举值验证 enum

  • 数组元素验证 items

  • 数组长度的 minItems 和 maxItems

  • 数值可整除性的 multipleOf

  • additionalProperties（draft 4 中默认允许）

Syntax

> JSON_SCHEMA_VALID(schema, json_doc)

Arguments

参数

说明

schema

必填。指定验证 schema 的 JSON 对象。

json_doc

必填。要根据 schema 验证的 JSON 文档。

Examples

DROP DATABASE IF EXISTS dbfuncs;
CREATE DATABASE dbfuncs;
USE dbfuncs;

-- 带必填字段的对象
SELECT JSON_SCHEMA_VALID('{"type":"object","properties":{"lat":{"type":"number","minimum":-90,"maximum":90}},"required":["lat"]}', '{"lat":60}');

-- 缺少必填字段
SELECT JSON_SCHEMA_VALID('{"type":"object","properties":{"lat":{"type":"number","minimum":-90,"maximum":90}},"required":["lat"]}', '{}');

-- 值超过最大值
SELECT JSON_SCHEMA_VALID('{"type":"object","properties":{"lat":{"type":"number","minimum":-90,"maximum":90}},"required":["lat"]}', '{"lat":100}');

-- 无必填字段时，空对象通过验证
SELECT JSON_SCHEMA_VALID('{"type":"object","properties":{"lat":{"type":"number","minimum":-90,"maximum":90}}}', '{}');

-- 数字范围
SELECT JSON_SCHEMA_VALID('{"type":"number","minimum":0,"maximum":100}', '42');
SELECT JSON_SCHEMA_VALID('{"type":"number","minimum":0,"maximum":100}', '200');

-- 字符串长度
SELECT JSON_SCHEMA_VALID('{"type":"string","minLength":2,"maxLength":5}', '"hi"');
SELECT JSON_SCHEMA_VALID('{"type":"string","minLength":2,"maxLength":5}', '"too_long_string"');

-- 正则模式
SELECT JSON_SCHEMA_VALID('{"type":"string","pattern":"^[a-z]+$"}', '"hello"');
SELECT JSON_SCHEMA_VALID('{"type":"string","pattern":"^[a-z]+$"}', '"HELLO"');

-- 枚举
SELECT JSON_SCHEMA_VALID('{"type":"string","enum":["red","green","blue"]}', '"red"');
SELECT JSON_SCHEMA_VALID('{"type":"string","enum":["red","green","blue"]}', '"yellow"');

-- 带元素约束的数组
SELECT JSON_SCHEMA_VALID('{"type":"array","items":{"type":"number"},"minItems":1,"maxItems":3}', '[1,2,3]');
SELECT JSON_SCHEMA_VALID('{"type":"array","items":{"type":"number"},"minItems":1,"maxItems":3}', '[]');

-- 可整除性
SELECT JSON_SCHEMA_VALID('{"type":"number","multipleOf":5}', '10');
SELECT JSON_SCHEMA_VALID('{"type":"number","multipleOf":5}', '7');

-- 嵌套对象
SELECT JSON_SCHEMA_VALID('{"type":"object","properties":{"addr":{"type":"object","properties":{"city":{"type":"string"},"zip":{"type":"string"}},"required":["city"]}},"required":["addr"]}', '{"addr":{"city":"NYC","zip":"10001"}}');

-- NULL 输入
SELECT JSON_SCHEMA_VALID(NULL, '{"a":1}');
SELECT JSON_SCHEMA_VALID('{"type":"object"}', NULL);

-- 通过 JSON 函数
SELECT JSON_SCHEMA_VALID(JSON_OBJECT('type', 'number', 'minimum', 0, 'maximum', 100), JSON_OBJECT('val', 42));
SELECT JSON_SCHEMA_VALID(JSON_OBJECT('type', 'number', 'minimum', 0, 'maximum', 100), JSON_OBJECT('val', 200));

DROP DATABASE dbfuncs;