JSON_SCHEMA_VALIDATION_REPORT()¶

JSON_SCHEMA_VALIDATION_REPORT() validates a JSON document against a JSON Schema (draft 4) and returns a detailed validation report as a JSON object, including a valid/invalid flag and specific error messages.

Description¶

JSON_SCHEMA_VALIDATION_REPORT() performs the same validation as JSON_SCHEMA_VALID() but returns a structured JSON object instead of a simple boolean. The report includes:

A valid field (boolean) indicating whether the document passed.
An errors array containing objects that describe each validation failure, including the schema path, document path, and a human-readable message.

This function is useful for debugging validation issues or when you need programmatic access to the specific reasons a document failed validation.

Syntax¶

> JSON_SCHEMA_VALIDATION_REPORT(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;

-- Valid document: report shows valid = true
SELECT JSON_SCHEMA_VALIDATION_REPORT('{"type":"object","required":["name"]}', '{"name":"test"}');

-- Missing required field: report shows detailed error
SELECT JSON_SCHEMA_VALIDATION_REPORT('{"type":"object","required":["name","age"]}', '{"name":"test"}');

-- Wrong type
SELECT JSON_SCHEMA_VALIDATION_REPORT('{"type":"object","properties":{"x":{"type":"number"}},"required":["x"]}', '{"x":"not_a_number"}');

-- Out of range
SELECT JSON_SCHEMA_VALIDATION_REPORT('{"type":"number","minimum":0,"maximum":100}', '200');

-- NULL input
SELECT JSON_SCHEMA_VALIDATION_REPORT(NULL, '{}');
SELECT JSON_SCHEMA_VALIDATION_REPORT('{"type":"object"}', NULL);

-- Table-based validation
CREATE TABLE t1 (j JSON);
INSERT INTO t1 VALUES ('{"a":1}'), ('{"a":"not_number"}'), ('{"b":2}');
SELECT JSON_SCHEMA_VALIDATION_REPORT(JSON_OBJECT('type', 'object', 'properties', JSON_OBJECT('a', JSON_OBJECT('type', 'number')), 'required', JSON_ARRAY('a')), j) FROM t1;

DROP TABLE t1;
DROP DATABASE dbfuncs;