JSON
What the domain tests
The JSON domain tests the JSON data type, path expressions, modification functions, validation, and querying JSON with SQL. MySQL 8.0 adds JSON_TABLE, JSON aggregation, and generated columns for indexing JSON values.
Core rules
- JSON columns store valid JSON documents in a binary format. Invalid JSON is rejected. JSON null is distinct from SQL NULL.
- The -> operator returns a JSON value, including quotes for strings. ->> returns an unquoted SQL string or number. JSON_EXTRACT is the function form.
- JSON_SET inserts or replaces values. JSON_INSERT only adds missing paths. JSON_REPLACE only changes existing paths. JSON_REMOVE deletes paths.
- Paths use $ for the document root, .key for object members, and [n] for array elements.
- JSON_CONTAINS, JSON_CONTAINS_PATH, JSON_KEYS, JSON_LENGTH, JSON_TYPE, and JSON_VALID inspect documents.
- JSON_ARRAYAGG and JSON_OBJECTAGG aggregate rows into JSON. JSON_TABLE shreds JSON into relational rows.
- A JSON column cannot be indexed directly. Define a generated column with an expression such as JSON_EXTRACT(doc, '$.id') and index that column.
Worked example
For doc = '{"name":"Ana","age":30}', SELECT doc->'$.name' returns the string with JSON quotes, while doc->>'$.name' returns Ana as an unquoted SQL string. SELECT JSON_SET(doc, '$.age', 31) changes the age; JSON_INSERT(doc, '$.age', 31) leaves it unchanged because the path already exists.
Traps
- Confusing JSON null with SQL NULL in predicates and IS NULL tests.
- Assuming -> and ->> return the same type.
- Using JSON_INSERT to overwrite an existing value.
- Trying to create an index directly on a JSON column.
- Forgetting that path expressions are case-sensitive for object keys.