JSON Schema validation for columns

Now that your database has JSON column, it's tempting to store data that's complicated to model in a JSON column. You may not want to give up the safety of a defined schema, which is simplifying your application code so much. Indeed, the JSON column will be checked for structural validity whether it's conforming to the SQL standard. But you can also gain the security of a schema-definition for JSON columns by validating against JSON schema rules.

Usage

MySQL

ALTER TABLE products ADD CONSTRAINT CHECK(
  JSON_SCHEMA_VALID(
    '{
      "$schema": "http://json-schema.org/draft-04/schema#",
      "type": "object",
      "properties": {
        "tags": {
          "type": "array",
          "items": { "type": "string" }
        }
      },
      "additionalProperties": false
    }',
    attributes
  )
);

INSERT INTO products (..., attributes) VALUES (..., '{}');
-- Result: OK
INSERT INTO products (..., attributes) VALUES (..., '{ "tags":[] }');
-- Result: OK
INSERT INTO products (..., attributes) VALUES (..., '{ "tags":["test"] }');
-- Result: OK
INSERT INTO products (..., attributes) VALUES (..., '{ "tags":[2] }');
-- ERROR: Check constraint 'products_chk_1' is violated.

PostgreSQL (requires postgres-json-schema)

ALTER TABLE products ADD CONSTRAINT data_is_valid CHECK(
  validate_json_schema(
    '{
      "type": "object",
      "properties": {
        "tags": {
          "type": "array",
          "items": { "type": "string" }
        }
      },
      "additionalProperties": false
    }',
    attributes
  )
);

INSERT INTO products (..., attributes) VALUES (..., '{}');
-- Result: OK
INSERT INTO products (..., attributes) VALUES (..., '{ "tags":[] }');
-- Result: OK
INSERT INTO products (..., attributes) VALUES (..., '{ "tags":["test"] }');
-- Result: OK
INSERT INTO products (..., attributes) VALUES (..., '{ "tags":[2] }');
-- ERROR: new row for relation "products" violates check constraint "data_is_valid"
-- DETAIL: Failing row contains ({"tags": [2]}).

Detailed Explanation

JSON columns are available on all modern databases with automatic checking of structural JSON validity and saving in a storage-efficient way. But as data that is complicated to store in a relational model is now saved in JSON columns, a new set of problems emerges. It's been known for a long time that schema-less NoSQL database will just move the schema to the application. The schema and many versions of it need to be managed at numerous place throughout an application. But we want to avoid repeating that mistake when using JSON columns.

The problem of validating a (complex) JSON document to conform to a specific model is solved for a long time. The JSON schema standard enables to describe properties and data types, resulting in an effective solution to validate a JSON document. This process has been done in the application in the past, but there's no reason you couldn't or shouldn't do it in the database. Because whenever you need to change anything in the database manually, your application's validation logic will not be used. Adding JSON schema validation to your JSON columns combine's the power of a NoSQL and relational database:

• NoSQL benefit: You can model some data in JSON columns which would be complicated to do in a relational database.
• Relational database benefit: You get schema safety for your data and don't have to handle different data versions in your application.

Additional Resources

• JSON Schema: All details about how to write JSON validation schemas.
• Youtube: David Stokes presents MySQL JSON document validation on the Percona Live conference in 2021.
• MySQL Documentation: The JSON_SCHEMA_VALID function and it's usage.
• PostgreSQL Extension: A JSON schema validation function implemented in pure PL/pgSQL which can be added to any database.

Tobias Petry

SQL for Devs Founder