In MySQL, the JSON_SCHEMA_VALIDATION_REPORT() function validates a JSON document against a JSON schema and returns a report of the outcome of that validation. The report is returned as a JSON document.

If the document is valid, only one field is returned: "valid" : true. If the document is not valid, a more extensive report is provided.

Syntax

The syntax goes like this:

JSON_SCHEMA_VALIDATION_REPORT(schema,document)

Where schema is the JSON schema, and document is the JSON document that we want to check against that schema.

Example

Here’s an example to demonstrate:

SET @schema = '{
  "$schema": "https://json-schema.org/draft/2020-12/schema",
  "$id": "https://example.com/product.schema.json",
  "title": "Product",
  "description": "A product from the catalog",
  "type": "object",
  "properties": {
    "productId": {
      "description": "The unique identifier for a product",
      "type": "integer"
    },
    "productName": {
      "description": "Name of the product",
      "type": "string"
    },
    "price": {
      "description": "The price of the product",
      "type": "number",
      "exclusiveMinimum": 0
    }
  },
  "required": [ "productId", "productName", "price" ]
}';

SET @document = '{
    "productId": 1,
    "productName": "Left-handed screwdriver",
    "price": 250
    }';

SELECT JSON_SCHEMA_VALIDATION_REPORT(@schema, @document);

Result:

+---------------------------------------------------+
| JSON_SCHEMA_VALIDATION_REPORT(@schema, @document) |
+---------------------------------------------------+
| {"valid": true}                                   |
+---------------------------------------------------+

In this case the JSON document is valid and so the report returns {"valid": true}.

Invalid JSON Document

Here’s what happens when the JSON document is not valid:

SET @schema = '{
  "$schema": "https://json-schema.org/draft/2020-12/schema",
  "$id": "https://example.com/product.schema.json",
  "title": "Product",
  "description": "A product from the catalog",
  "type": "object",
  "properties": {
    "productId": {
      "description": "The unique identifier for a product",
      "type": "integer"
    },
    "productName": {
      "description": "Name of the product",
      "type": "string"
    },
    "price": {
      "description": "The price of the product",
      "type": "number",
      "exclusiveMinimum": 0
    }
  },
  "required": [ "productId", "productName", "price" ]
}';

SET @document = '{
    "productId": 1,
    "productName": "Left-handed screwdriver"
    }';

SELECT JSON_SCHEMA_VALIDATION_REPORT(@schema, @document);

Result:

+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| JSON_SCHEMA_VALIDATION_REPORT(@schema, @document)                                                                                                                                                             |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {"valid": false, "reason": "The JSON document location '#' failed requirement 'required' at JSON Schema location '#'", "schema-location": "#", "document-location": "#", "schema-failed-keyword": "required"} |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

When a document fails validation, a more extensive report is provided.

In this example, I removed the price from the JSON document and so the JSON document is no longer valid.

We therefore get "valid": false in the report, along with an explanation of why the document failed validation.

In this case, the reason the JSON document is not valid is because the schema states that the price field is required. In fact it states that all three fields are required. Given I removed the price field, it is no longer valid against the schema.

But even when a field exists in the JSON document, it still needs to adhere to any rules set by the schema.

In the following example I put the price field back into the document, but I give it a value that doesn’t adhere to the schema:

SET @schema = '{
  "$schema": "https://json-schema.org/draft/2020-12/schema",
  "$id": "https://example.com/product.schema.json",
  "title": "Product",
  "description": "A product from the catalog",
  "type": "object",
  "properties": {
    "productId": {
      "description": "The unique identifier for a product",
      "type": "integer"
    },
    "productName": {
      "description": "Name of the product",
      "type": "string"
    },
    "price": {
      "description": "The price of the product",
      "type": "number",
      "exclusiveMinimum": 0
    }
  },
  "required": [ "productId", "productName", "price" ]
}';

SET @document = '{
    "productId": 1,
    "productName": "Left-handed screwdriver",
    "price": "Two Hundred and Fifty"
    }';

SELECT JSON_SCHEMA_VALIDATION_REPORT(@schema, @document);

Result:

+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| JSON_SCHEMA_VALIDATION_REPORT(@schema, @document)                                                                                                                                                                                                   |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {"valid": false, "reason": "The JSON document location '#/price' failed requirement 'type' at JSON Schema location '#/properties/price'", "schema-location": "#/properties/price", "document-location": "#/price", "schema-failed-keyword": "type"} |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Once again the validation failed. This is because the price field contains a string value instead of a number. The JSON schema specifies that the field must contain a number, and so the JSON document is invalid. The validation report explains this by stating that the price field failed the type requirement (i.e. it’s the wrong type).

Report Fields

As mentioned, the fields returned in the validation report depend on whether or not the document passed validation. These are outlined below.

Successful Validation

If the document is valid, then only one field is provided in the report:

Key	Value
valid	Always true for a successful schema validation.

Failed Validation

When a document fails validation, the following fields are provided in the report:

Key	Value
valid	Always false for a failed schema validation.
reason	A human-readable string containing the reason for the failure.
schema-location	A JSON pointer URI fragment identifier indicating where in the JSON schema the validation failed.
document-location	A JSON pointer URI fragment identifier indicating where in the JSON document the validation failed.
schema-failed-keyword	A string containing the name of the keyword or property in the JSON schema that was violated.