MySQL 8.0.17 introduced a couple of functions that allow us to validate JSON documents against their schemas. These are listed below, with examples.

The JSON_SCHEMA_VALID() Function

The JSON_SCHEMA_VALID() function validates the given JSON document against its schema, then returns 0 or 1, depending on whether or not the JSON document conformed to its schema.

Example:

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_VALID(@schema, @document);

Result:

1

In this case the JSON document successfully validated against its schema and so 1 was returned. If it failed validation, then 0 would’ve been returned.

This function can also be used in CHECK constraints to ensure that any data being inserted into a JSON column conforms to a given schema (as provided in the CHECK constraint). See How to Create a CHECK Constraint Against a JSON Column in MySQL for an example.

The JSON_SCHEMA_VALIDATION_REPORT() Function

The JSON_SCHEMA_VALIDATION_REPORT() function also validates the given JSON document against its schema, but this function returns a report on the outcome of the validation.

Here’s an example of what happens when the JSON document fails validation:

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"} |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

In this case, the reason the JSON document is not valid is because the schema states that the price field is required, but it’s not included in the JSON that I provided in the second argument.

Of course, even when the field does exist in the JSON document, it still needs to adhere to any rules set by the schema.

The report is much shorter for valid documents. If the JSON document is valid, only one field is returned: "valid" : true.