In MySQL, the JSON_SCHEMA_VALID() function validates a JSON document against a JSON schema. It returns true (1) if the document is valid, and false (0) if it is not.

Syntax

The syntax goes like this:

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

Result:

1

In this case the JSON document is valid and we get a result of 1.

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

Result:

0

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

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.

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

Result:

0

Once again we get a return value of 0. 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.

Using JSON_SCHEMA_VALID() with CHECK Constraints

We can use the JSON_SCHEMA_VALID() function to enforce CHECK constraints.

If we have a JSON column in our database, we can create a CHECK constraint that validates any data that is to be stored in that column. In such cases, we can use the JSON_SCHEMA_VALID() function to check that the value adheres to the JSON schema.

Example:

CREATE TABLE stats (
    scores JSON,
    CHECK(
        JSON_SCHEMA_VALID(
            '{
                "type": "object",
                "properties": {
                "playerId": {
                "description": "The unique identifier for a player",
                "type": "integer"
                },
                "playerName": {
                "description": "Name of the player",
                "type": "string"
                },
                    "score": {
                    "description": "The price of the product",
                    "type": "integer",
                     "minimum": 0,
                     "maximum": 500
                    }
                },
                "required": [ "playerId", "playerName", "score" ]
            }',
            scores
        )
    )
);

That code created a table called stats with a JSON column called scores.

We created a CHECK constraint that uses JSON_SCHEMA_VALID() to check any JSON document that’s inserted into the column. Basically, we check that the JSON document contains all three fields, that their data type is correct, and that the score field contains a value between 0 and 500.

Now let’s insert data into the table:

INSERT INTO stats ( scores ) 
    VALUES (
        '{
            "playerId" : 1,
            "playerName" : "Bart",
            "score" : 427
        }'
    );

Now let’s take a look at the contents of our table:

SELECT * FROM stats;

Result:

+-----------------------------------------------------+
| scores                                              |
+-----------------------------------------------------+
| {"score": 427, "playerId": 1, "playerName": "Bart"} |
+-----------------------------------------------------+

In this case the JSON was valid against the schema, and it was therefore inserted into the table.

Let’s try to insert a JSON document that violates the schema:

INSERT INTO stats ( scores ) 
    VALUES (
        '{
            "playerId" : 2,
            "playerName" : "Homer",
            "score" : -500
        }'
    );

Result:

ERROR 3819 (HY000): Check constraint 'stats_chk_1' is violated.

In this case our JSON document violated the schema. Specifically, this player has a negative score, but negative scores aren’t allowed.