Skip to content
Tutorials Center
My learning

Topics

Lesson 36 of 75 ยท JSON and Modern Features

The JSON Data Type

Store and query JSON in MySQL: the JSON column type, the -> and ->> operators, JSON_EXTRACT, JSON_SET, JSON_INSERT, JSON_REPLACE, JSON_REMOVE and validation.

Relational tables are great when every row has the same columns. Real applications also hold data that is irregular: settings that differ per user, the raw payload of a webhook, product attributes that vary by category. MySQL's JSON column type lets you keep such documents next to your relational data, validate them on the way in, and reach into them with SQL.

This lesson covers the type itself, the path syntax, the functions that read, build and modify documents, and how to decide when JSON is the right tool. Indexing JSON and turning arrays into rows come next in JSON_TABLE, Multi-Valued Indexes and JSON Performance. The server's JSON functions are MySQL-specific, but SQLite has a close cousin of almost every one of them, so the examples below run live with the SQLite names and the MySQL spellings are shown beside them.

What a JSON column gives you

Validation Invalid JSON is rejected on insert.
Binary format Stored parsed, so lookups skip re-parsing.
Path queries Reach inside with $.a.b[0].
Partial edits Change one key and keep the rest.

One column, a whole document.

1 / 2

Creating a JSON column

A JSON column is declared like any other type:

SQL
CREATE TABLE customers (
  id      INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name    VARCHAR(100) NOT NULL,
  profile JSON NOT NULL
);

INSERT INTO customers (name, profile) VALUES
  ('Ana', '{"age": 34, "city": "Lisbon", "tags": ["vip", "newsletter"]}');

MySQL parses the text when it is inserted. If it is not valid JSON, the statement fails with an error such as ERROR 3140 (22032): Invalid JSON text. That is a big difference from storing JSON in a TEXT column, where anything goes in. The parsed document is kept in a binary format that allows direct access to a key or array element without reading the whole value. Some details you should know:

  • The stored form is normalized: whitespace is removed or standardized, the order of object keys is not preserved, and a duplicate key keeps only one value. Do not rely on key order.
  • Numbers keep their kind: integers, decimals and doubles are different JSON types inside MySQL.
  • A JSON column cannot have a plain literal default. Since 8.0.13 you can use an expression default such as DEFAULT (JSON_OBJECT()).
  • A JSON column cannot be indexed directly. You index values extracted from it (next lesson).
  • Documents are limited by max_allowed_packet (64 MB by default in recent versions), but very large documents are slow to ship around, so keep them small.

SQLite has no JSON type. It stores JSON as TEXT, and the functions parse it each time. You can still get validation with a CHECK (json_valid(col)) constraint, which is what the live examples do.

Reading values: paths, -> and ->>

A path starts at $, the whole document, and walks down: .key for an object member, [n] for an array element, [*] for all elements, and ** for a wildcard search at any depth. A key that is not an identifier is quoted: $."first name". In MySQL, [last] and [last-1] count from the end of an array.

There are two operators for reading, and the difference matters:

Expression Returns
col -> '$.city' The value as JSON, so a string comes back with quotes: "Lisbon".
col ->> '$.city' The value as a plain SQL string, with quotes removed: Lisbon.

-> is shorthand for JSON_EXTRACT(col, '$.city'). ->> is shorthand for JSON_UNQUOTE(JSON_EXTRACT(col, '$.city')). If the path does not exist, both return SQL NULL. Use ->> whenever you compare, sort or display a value, and -> when you want to hand a nested object or array on as JSON.

SQL Reading a JSON document with -> and ->>

Press Run (or Ctrl + Enter) to see the result here.

The first query shows the quotes around "Lisbon" with -> and none with ->>. Missing values come back empty (NULL): Ben has no zip and his empty tags array has no first element, while Cara has no age and no address. The tag_count column is 2, 0 and 1. The two filters find Ben (city Oslo) and Ana (the only one older than 30; Cara's NULL age fails the comparison).

One difference to be aware of: in MySQL, ->> always returns a string (with the utf8mb4_bin collation, so comparisons are case-sensitive), while SQLite's ->> returns a native number for a JSON number. When you compare two extracted numbers in MySQL, cast them first: CAST(profile ->> '$.age' AS UNSIGNED). Otherwise '9' sorts after '10'. Comparing against a numeric literal, as in the last query, converts the string to a number.

Building documents

JSON_OBJECT and JSON_ARRAY assemble documents from SQL values, which is how you return nested results from a query. JSON_ARRAYAGG and JSON_OBJECTAGG do the same across the rows of a group. SQLite spells the aggregates json_group_array and json_group_object:

SQL Building JSON, validating and inspecting types

Press Run (or Ctrl + Enter) to see the result here.

You get one document per order with its lines nested inside. The json_valid row is 1, 0, 0, 0: only the first string is valid JSON, since keys must be quoted, brackets must close and an empty string is not a document. MySQL has the same helpers: JSON_VALID(), JSON_TYPE() (which reports OBJECT, ARRAY, STRING, INTEGER, DOUBLE, BOOLEAN or NULL), JSON_LENGTH(), JSON_KEYS() and JSON_DEPTH(). SQLite's json_type names are lowercase and the numeric ones are integer and real.

A subtle point in both systems: NULL in SQL and null in JSON are different things. JSON_EXTRACT of a key whose value is JSON null returns the JSON null, while a missing key returns SQL NULL. Check with JSON_TYPE when the difference matters.

Changing documents

You can rewrite the whole column, but the dedicated functions change one part and leave the rest alone. Each takes a document and path/value pairs, and returns the new document:

Function Behavior
JSON_SET(doc, path, val, ...) Replaces the value if the path exists, adds it if it does not.
JSON_INSERT(doc, path, val, ...) Adds only when the path does not exist. Existing values are left alone.
JSON_REPLACE(doc, path, val, ...) Replaces only when the path does exist. Never adds.
JSON_REMOVE(doc, path, ...) Deletes the paths.
JSON_ARRAY_APPEND(doc, path, val) Appends to the array at the path.
JSON_MERGE_PATCH(doc1, doc2) RFC 7396 merge: keys in the second document overwrite, null deletes a key.

The pairs are applied from left to right. Setting a value that is itself a string, number or boolean works as you expect, but to store a nested document you must pass real JSON (for example JSON_OBJECT(...) or CAST('{"a":1}' AS JSON)), otherwise it is stored as a string. SQLite has json_set, json_insert, json_replace and json_remove with the same rules, and its json_patch is the counterpart of JSON_MERGE_PATCH:

SQL SET, INSERT, REPLACE, REMOVE and a merge patch

Press Run (or Ctrl + Enter) to see the result here.

In the first row, after_set updates the login count and adds last; after_insert keeps the name Ana and only adds plan; after_replace changes the name but does not add plan; after_remove drops stats and empties the tags array. The second query appends a tag by writing to index 1, and the merge patch deletes tags (because its value is null) and overwrites plan. Note that SQLite keeps the key order of the text you typed, while MySQL would return the keys in its own normalized order.

A typical MySQL update looks like this:

SQL
UPDATE customers
SET profile = JSON_SET(profile, '$.stats.logins', COALESCE(profile->>'$.stats.logins', 0) + 1)
WHERE id = 1;

In MySQL 8, when you update a JSON column with JSON_SET, JSON_REPLACE or JSON_REMOVE, the server can often change the document in place and write only the difference to the binary log, if the new value fits where the old one was. That makes small edits to large documents cheaper than rewriting them.

When to use JSON, and when not to

JSON shines for data that is read and written as a unit, is sparse or varies by row, or whose shape you do not control: user preferences, feature flags, third-party API responses, audit snapshots, optional product attributes.

It is a poor fit for data you filter, join, sort or aggregate on all the time, or that needs constraints. Putting a customer's email, an order's status or a price inside a JSON blob gives up types, foreign keys, NOT NULL, normal indexes and statistics. A good rule: if a key appears in a WHERE clause of a hot query, or in nearly every row, make it a real column (or a generated column, see Generated Columns), and keep the leftovers in JSON. More on that trade-off in Schema Design Patterns for MySQL.

What ran live here

Everything in the TryIts ran in SQLite's JSON functions, which mirror MySQL's names and semantics for extraction, building, SET/INSERT/REPLACE/REMOVE and merge patches. The JSON column type, JSON_EXTRACT, JSON_UNQUOTE, JSON_ARRAYAGG, error 3140 and the in-place update behavior are MySQL server features, shown as read-only code.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What is the difference between col -> '$.name' and col ->> '$.name' in MySQL?

    -> is JSON_EXTRACT and returns a JSON value. ->> wraps it in JSON_UNQUOTE and returns plain text, which is what you normally want for comparing and displaying.
  2. 2. A document is {"a": 1}. Which call adds the key b with value 2 and also changes a to 5 if it exists?

    JSON_SET both replaces existing paths and adds missing ones. JSON_INSERT never changes existing values, and JSON_REPLACE never adds new ones.
  3. 3. What happens when you insert the text {name: 'Ana'} into a MySQL JSON column?

    MySQL validates JSON on insert and rejects invalid documents with error 3140. A TEXT column would accept anything.
  4. 4. Which data is the best candidate for a real column instead of JSON?

    Values you filter, join or constrain constantly belong in typed, indexed columns. JSON suits sparse, variable or opaque data.
  5. 5. Does MySQL preserve the order of keys in a stored JSON object?

    The binary format normalizes objects, so never depend on key order. Array order is preserved.

Summary

  • A JSON column validates documents on insert and stores them in a binary format. It cannot be indexed directly and does not preserve object key order.
  • Paths start with $ and use .key, [n] and [*]. -> returns JSON, ->> returns an unquoted string.
  • JSON_SET replaces or adds, JSON_INSERT only adds, JSON_REPLACE only replaces and JSON_REMOVE deletes. JSON_MERGE_PATCH merges documents.
  • JSON_OBJECT, JSON_ARRAY, JSON_ARRAYAGG and JSON_OBJECTAGG build documents from rows.
  • Use JSON for variable, sparse or opaque data, and real columns for what you filter, join and constrain.

Next: JSON_TABLE, Multi-Valued Indexes and JSON Performance.