Skip to content

CONCEPT Cited by 3 sources

MySQL JSON data type

The JSON data type in MySQL is an engine-level primitive for storing whole JSON documents in a single column with (a) automatic content validation on insert and update, (b) a compact binary storage format for fast random-access reads, and (c) a family of JSON_* functions for extraction, mutation, and type introspection. Introduced in MySQL 5.7.8 (mid-2015) and extended continuously since.

Why it exists

Classical relational tables enforce a predetermined column list with fixed data types. The JSON type is a deliberate escape hatch from that rigour for workloads where the value shape is either variable or not worth modelling as multiple tables:

  • Application / server log output.
  • REST API responses stored verbatim.
  • Configuration data with open-ended keys.
  • Entities whose attribute set varies by instance.
  • Collapsing multi-table relations into a single document when the access pattern is "fetch the whole thing" — a form of denormalisation.

The JSON data type is a bit of an anti-pattern to the rigourousness nature of such a schema. It allows you to break out of it, to gain flexibility when you need it.

— Mike Stojan, The MySQL JSON data type

Storage shape

Internally similar to LONGBLOB/LONGTEXT in that the value is a variable-length byte string, but with a parsed binary encoding + per-document metadata + a key- name dictionary that speeds repeated key lookups. See concepts/json-data-type-storage-overhead for the 4–10 bytes/string rule-of-thumb + no-direct-indexing consequence.

Function set

Highlights from the MySQL 8 catalogue (not exhaustive):

  • Construction — JSON_ARRAY(...), JSON_OBJECT(...).
  • Extraction — JSON_EXTRACT(col, path) and its shorthand operator ->; JSON_UNQUOTE(JSON_EXTRACT(...)) and its shorthand ->>. See concepts/mysql-json-extraction-operator.
  • Introspection — JSON_TYPE(value) reports ARRAY, OBJECT, STRING, etc.
  • Mutation — JSON_SET, JSON_INSERT, JSON_REPLACE, JSON_REMOVE, JSON_ARRAY_APPEND, JSON_ARRAY_INSERT. Partial updates modify the binary representation in place rather than rewriting the full document.
  • Containment / existence — JSON_CONTAINS, JSON_CONTAINS_PATH, JSON_OVERLAPS. The rough MySQL equivalents of Postgres's @>, ?, ?&, ?| operators (see concepts/postgres-jsonb-vs-mysql-json).
  • Aggregation — JSON_ARRAYAGG, JSON_OBJECTAGG.
  • Table pivot — JSON_TABLE. Turns a JSON array into a relational result set. PlanetScale does not support JSON_TABLE (the one named gap in PlanetScale's JSON-function coverage).

Ceiling vs practical size

  • MySQL theoretical — up to 1 GB per document.
  • PlanetScale — up to 67 MB per document (platform policy, not a configuration knob).
  • Practical recommendation — "only a few MB" per document regardless of engine ceiling, per Stojan.

(Source: sources/2026-04-21-planetscale-the-mysql-json-data-type.)

Indexing

JSON columns cannot be indexed directly — the binary encoding + variable length are incompatible with a B-tree secondary index. The supported path is:

  1. Extract the key of interest with ->> (unquoted) or JSON_UNQUOTE(JSON_EXTRACT(...)).
  2. Either (a) materialise the extraction in a named generated column and index the column, or (b) declare a functional index (MySQL 8.0.13+) directly on the expression.
  3. Either shape produces the same on-disk index; the optimiser rewrites a query using the raw expression to use the index if the expressions match byte-for- byte.

Canonicalised as patterns/index-json-key-via-generated-column in Aaron Francis's Indexing JSON in MySQL. MySQL 8.0.17+ also adds multi-valued indexes over JSON arrays — a narrow path to indexing all array elements of one key — not covered in the present concept.

Cross-engine context

Postgres has two JSON types (JSON text-stored and JSONB binary) plus a GIN index that can cover an entire document. MySQL has one (JSON, binary) and requires per-path functional indexes. For the full comparison see concepts/postgres-jsonb-vs-mysql-json.

Seen in

Last updated · 470 distilled / 1,213 read