A path starts at $, descends with .key or [n], and accepts [last], ranges such as [0 to 1], the wildcards .* and [*], and ** for any depth. col->path is shorthand for JSON_EXTRACT() and returns JSON, so attributes->'$.format' prints "ebook" in quotes; col->>path also unquotes it to ebook, the form to display or compare. JSON_VALUE(attributes, '$.pages' RETURNING UNSIGNED) extracts one scalar with a type. For writing, JSON_INSERT() adds only missing paths, JSON_REPLACE() changes only existing ones, and JSON_SET() does both; each returns a new document.
SET @j = '{"a": 1, "b": [2, 3]}';
SELECT 'JSON_INSERT' AS fn, JSON_INSERT(@j, '$.a', 10, '$.c', 4) AS result
UNION ALL SELECT 'JSON_SET', JSON_SET(@j, '$.a', 10, '$.c', 4)
UNION ALL SELECT 'JSON_REPLACE', JSON_REPLACE(@j, '$.a', 10, '$.c', 4)
UNION ALL SELECT 'JSON_REMOVE', JSON_REMOVE(@j, '$.b[0]');Output
+--------------+--------------------------------+
| fn | result |
+--------------+--------------------------------+
| JSON_INSERT | {"a": 1, "b": [2, 3], "c": 4} |
| JSON_SET | {"a": 10, "b": [2, 3], "c": 4} |
| JSON_REPLACE | {"a": 10, "b": [2, 3]} |
| JSON_REMOVE | {"a": 1, "b": [3]} |
+--------------+--------------------------------+