JSON Support

MySQL has a native JSON column type — it validates that whatever you insert is actually well-formed JSON, and stores it in a binary format that's faster to query than plain text.

Creating a table with a JSON column

SQL mysql shell

CREATE TABLE events (
  id INT AUTO_INCREMENT PRIMARY KEY,
  payload JSON
);

INSERT INTO events (payload) VALUES
  ('{"type": "signup", "user": {"name": "Priya", "plan": "pro"}}');

Output

Query OK, 1 row affected (0.01 sec)

If you tried inserting text that wasn't valid JSON, MySQL would reject it immediately with an error — the column enforces well-formedness for you, unlike storing JSON in a plain TEXT column.

Extracting a value with JSON_EXTRACT and ->

SQL mysql shell

SELECT
  payload->'$.type' AS quoted_type,
  payload->>'$.user.name' AS user_name
FROM events;

Output

+---------------+------------+
| quoted_type   | user_name  |
+---------------+------------+
| "signup"      | Priya      |
+---------------+------------+
1 row in set (0.00 sec)

payload->'$.type' is shorthand for JSON_EXTRACT(payload, '$.type'), and $.user.name walks into a nested object the same way you'd expect. ->> (the "unquoting" arrow) additionally strips the surrounding quotes a string result would otherwise keep.

-> vs ->>: quoted JSON vs plain text: -> returns a JSON value — a string result comes back wrapped in literal double quotes, as seen in quoted_type above. ->> returns the unquoted scalar text instead. Using -> where you meant ->> is a common source of "why does my string have quotes inside it" bugs, especially when comparing the result against a plain string in a WHERE clause.

PreviousStored Procedures NextBackups with mysqldump