JSON_SCHEMA_VALID()¶

JSON_SCHEMA_VALID() validates a JSON document against a JSON Schema (draft 4), returning 1 if the document conforms to the schema, 0 if it does not, and NULL if any argument is NULL.

Description¶

JSON_SCHEMA_VALID() checks whether a JSON document satisfies the constraints defined in a JSON Schema. The schema must be a JSON object. Supported schema keywords include:

type (string, number, object, array)
properties with nested type constraints
required array of property names
minimum and maximum for numeric types
minLength and maxLength for string types
pattern for regex validation on strings
enum for enumerated value validation
items for array element validation
minItems and maxItems for array length
multipleOf for numeric divisibility
additionalProperties (allowed by default in draft 4)

Syntax¶

> JSON_SCHEMA_VALID(schema, json_doc)

Arguments¶

Arguments	Description
schema	Required. A JSON object specifying the validation schema.
json_doc	Required. The JSON document to validate against the schema.

Examples¶

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

-- Object with required field
SELECT JSON_SCHEMA_VALID('{"type":"object","properties":{"lat":{"type":"number","minimum":-90,"maximum":90}},"required":["lat"]}', '{"lat":60}');

-- Missing required field
SELECT JSON_SCHEMA_VALID('{"type":"object","properties":{"lat":{"type":"number","minimum":-90,"maximum":90}},"required":["lat"]}', '{}');

-- Value exceeds maximum
SELECT JSON_SCHEMA_VALID('{"type":"object","properties":{"lat":{"type":"number","minimum":-90,"maximum":90}},"required":["lat"]}', '{"lat":100}');

-- Without required, empty object validates
SELECT JSON_SCHEMA_VALID('{"type":"object","properties":{"lat":{"type":"number","minimum":-90,"maximum":90}}}', '{}');

-- Number range
SELECT JSON_SCHEMA_VALID('{"type":"number","minimum":0,"maximum":100}', '42');
SELECT JSON_SCHEMA_VALID('{"type":"number","minimum":0,"maximum":100}', '200');

-- String length
SELECT JSON_SCHEMA_VALID('{"type":"string","minLength":2,"maxLength":5}', '"hi"');
SELECT JSON_SCHEMA_VALID('{"type":"string","minLength":2,"maxLength":5}', '"too_long_string"');

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

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

-- Array with items
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}', '[]');

-- MultipleOf
SELECT JSON_SCHEMA_VALID('{"type":"number","multipleOf":5}', '10');
SELECT JSON_SCHEMA_VALID('{"type":"number","multipleOf":5}', '7');

-- Nested objects
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 inputs
SELECT JSON_SCHEMA_VALID(NULL, '{"a":1}');
SELECT JSON_SCHEMA_VALID('{"type":"object"}', NULL);

-- Via JSON functions
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;