JSON Data Type
In this page:
JSON Columns
The native JSON column type stores a structured document directly in a single column and automatically validates that whatever you insert is syntactically valid JSON, rejecting malformed input outright.
Example: JSON Columns
CREATE TABLE settings (config JSON);
INSERT INTO settings VALUES ('{"theme": "dark"}');Extracting JSON Data
The -> operator (or the more verbose JSON_EXTRACT function) lets you pull a specific field out of a stored JSON document directly in a query, without reading and parsing the whole document in application code.
Example: Extracting JSON Data
CREATE TABLE settings (config JSON);
INSERT INTO settings VALUES ('{"theme": "dark"}');
SELECT config->'$.theme' FROM settings;Modifying JSON Documents
JSON_SET and JSON_INSERT let you update or add a single key inside a stored document in place, without needing to fetch, modify, and rewrite the entire JSON blob from your application.
Example: Modifying JSON Documents
CREATE TABLE settings (config JSON);
INSERT INTO settings VALUES ('{"theme": "dark"}');
UPDATE settings SET config = JSON_SET(config, '$.theme', 'light');JSON Validation
JSON_VALID() checks whether an arbitrary string is well-formed JSON, and JSON_CONTAINS_PATH() checks whether a specific path exists in a document — both are useful guards before trusting external input.
Example: JSON Validation
SELECT JSON_VALID('{"a": 1}') AS is_valid, JSON_CONTAINS_PATH('{"a": 1}', 'one', '$.a') AS has_path;JSON Arrays
JSON_ARRAY() and related functions let you build a JSON array directly inside a SELECT statement, which is handy for shaping query results into a format an API endpoint can return as-is.
Example: JSON Arrays
SELECT JSON_ARRAY('red', 'green', 'blue') AS colors;Chapter Quiz — Complete all 8 topics to unlock
0/8 topics done
Complete these topics first: