Validate json column against schema in MYSQL 8

[goellner]

We can take this schema and add it as a validation rule to the column we want.

  "$schema": "http://json-schema.org/draft-04/schema#",
  "type": "object",
  "patternProperties" : {
    "^[a-z][a-z]$": {                                 
      "type": "string"
    }
  },
  "minProperties": 1,
  "additionalProperties": false
}

The SQL query (title is the affected column):

ALTER TABLE posts
ADD CONSTRAINT attributes_are_valid check(
 JSON_SCHEMA_VALID('{
  "$schema": "http://json-schema.org/draft-04/schema#",
  "type": "object",
  "patternProperties" : {
    "^[a-z][a-z]$": {                                 
      "type": "string"
    }
  },
  "minProperties": 1,
  "additionalProperties": false
}', title
)
);