JSON Data Type
In this page:
column_name JSON
SELECT column_name->'$.key' FROM table_name;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;- Inserting invalid JSON such as
{theme: dark}with single quotes inside, which theJSONcolumn rejects. - Using
config->'$.theme'and expecting plain text, when it returns a quoted JSON value (use->>to unquote). - Forgetting the
$at the start of a JSON path, likethemeinstead of'$.theme'.
- MySQL has numeric, string, and date and time data types for storing different kinds of values.
ENUM,SET, andBOOLEANhandle fixed choices and true or false values.BLOBand binary types store raw data, and theJSONtype stores structured JSON documents.
Chapter Quiz — Complete all 8 topics to unlock
0/8 topics done
Complete these topics first: