JSON Columns

JSON Columns

Storing JSON Documents in a Column

A JSON column is not TEXT with a label. MySQL 524 parses each value on write, rejects invalid ones, and stores a binary format whose header indexes keys and offsets, so the server jumps to $.pages without scanning. Parsing normalizes too: whitespace goes, a duplicated key keeps its last value, and keys are sorted in an order the manual says may change. Product 1's 37 characters occupy 40 bytes (JSON_STORAGE_SIZE()). Since MySQL 8.0.17, JSON_SCHEMA_VALID() checks a document against a JSON Schema (draft 4), and inside a CHECK constraint it turns the column into a typed document store.

Validation, normalization, and a JSON Schema CHECK constraintSQL

INSERT INTO products (sku, title, category_id, price, attributes)
VALUES ('BK-BAD-01', 'Broken', 2, 1.00, '{"format": "ebook", pages: 10}');
SELECT CAST('{"pages": 412, "format": "paperback", "pages": 400}' AS JSON) AS normalized;
ALTER TABLE products ADD CONSTRAINT chk_attributes CHECK (JSON_SCHEMA_VALID('{
  "type": "object",
  "properties": {
    "format": {"enum": ["paperback", "hardcover", "ebook"]},
    "pages":  {"type": "integer", "minimum": 1}}}', attributes));
UPDATE products SET attributes = '{"format": "audiobook"}' WHERE id = 1;

Output

ERROR 3140 (22032): Invalid JSON text: "Missing a name for object member." at position 20 in
  value for column 'products.attributes'.
+---------------------------------------+
| normalized                            |
+---------------------------------------+
| {"pages": 400, "format": "paperback"} |
+---------------------------------------+
ERROR 3819 (HY000): Check constraint 'chk_attributes' is violated.

JSON_SCHEMA_VALIDATION_REPORT() names the failing rule (for {"pages": 0}, "schema-failed-keyword": "minimum"). With no required list, the mug's color still passes. Promote values that every row has, and that you join or sort on, to real columns.

MENU