Skip to content

PLANETSCALE 2022-09-23 Tier 3

Read original ↗

PlanetScale — The MySQL JSON data type

Summary

Mike Stojan's short pedagogical PlanetScale post (originally 2022-09-23, re-surfaced via the 2026-04-21 RSS snapshot) walks the MySQL JSON data type introduced in MySQL 5.7.8 (mid-2015) — when to use it, what it costs, what it can and cannot index, and which built-in functions operate on it. The post is tutorial altitude but canonicalises three durable wiki primitives that prior JSON ingests had referenced without dedicated homes: (1) the MySQL JSON data type as an engine- level primitive with binary storage, automatic content validation, and in-place partial update; (2) the storage-overhead + no-direct-indexing caveat that governs when JSON is the right tool vs classical columns; (3) the -> and ->> JSON extraction operators as shorthand for JSON_EXTRACT / JSON_UNQUOTE(JSON_EXTRACT(...)). One load-bearing operational number: PlanetScale caps JSON documents at 67 MB versus MySQL's theoretical 1 GB ceiling, with the vendor's own recommendation that documents stay "to only a few MB in size."

Key takeaways

  1. MySQL added JSON in 5.7.8 (mid-2015) — seven years of subsequent function additions, automatic content validation, in-place partial updates, and binary storage format for performance. (Source: sources/2026-04-21-planetscale-the-mysql-json-data-type)

  2. JSON is an anti-pattern to schema rigour — by design. "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." Valid use cases: application / server log output, REST API responses stored verbatim, configuration data, entities with variable attributes, and denormalisation to collapse multi-table relations into a single document when the access pattern warrants. (Source: sources/2026-04-21-planetscale-the-mysql-json-data-type)

  3. Storage overhead: roughly 4–10 bytes per string vs LONGBLOB/LONGTEXT. MySQL's JSON storage shape resembles LONGBLOB/LONGTEXT plus metadata + key- name dictionaries to speed reads. "A good rule of thumb is that a string stored in JSON uses roughly 4 to 10 bytes of additional storage compared to a LONGBLOB or LONGTEXT column." Classical data types (CHAR, VARCHAR, INT) remain strictly more storage-efficient. (Source: sources/2026-04-21-planetscale-the-mysql-json-data-type)

  4. JSON columns cannot be indexed directly. Binary encoding + variable document size mean "querying a JSON column often uses more buffer space and returns larger result sets, leading to more data exchange." The indexable escape hatch is generated-column or functional index over an extracted key — the subject of Aaron Francis's companion post Indexing JSON in MySQL. (Source: sources/2026-04-21-planetscale-the-mysql-json-data-type)

  5. PlanetScale caps JSON documents at 67 MB vs MySQL's 1 GB theoretical ceiling. "While a JSON document stored in MySQL can be up to 1 GB, in theory, it is recommended to keep JSON documents to only a few MB in size. On PlanetScale, we support JSON documents up to 67 MB." First wiki disclosure of the specific number — operational constraint surfaced as platform-level policy, not a soft guide. (Source: sources/2026-04-21-planetscale-the-mysql-json-data-type)

  6. Extraction operators -> and ->>. -> is shorthand for JSON_EXTRACT(col, path); ->> is shorthand for JSON_UNQUOTE(JSON_EXTRACT(col, path)). Worked example on an array column: SELECT songs->'$[3]' FROM songs returns "Ghost" (quoted); SELECT songs->>'$[3]' FROM songs returns Ghost (unquoted). The ->> form is the one whose output is typically what you want to index via a functional index or compare in a WHERE clause. (Source: sources/2026-04-21-planetscale-the-mysql-json-data-type)

  7. Mutation functions: JSON_ARRAY_APPEND, JSON_ARRAY_INSERT (and sibling JSON_SET, JSON_INSERT, JSON_REPLACE, JSON_REMOVE not walked in this post). Partial in-place updates avoid full-document rewrites — one of the MySQL 8 additions over 5.7. (Source: sources/2026-04-21-planetscale-the-mysql-json-data-type)

  8. PlanetScale supports all JSON functions except JSON_TABLE. Canonical wiki disclosure of the single gap — relevant when porting queries that pivot a JSON array into relational rows. (Source: sources/2026-04-21-planetscale-the-mysql-json-data-type)

Systems

  • systems/mysql — engine that introduced JSON in 5.7.8 (mid-2015) and has extended the function set + binary storage format over seven years.
  • systems/innodb — storage engine under which JSON columns live; inherits the storage-efficiency caveat vs LONGBLOB/LONGTEXT.
  • systems/planetscale — managed MySQL platform with the 67 MB per-document cap and no-JSON_TABLE constraint canonicalised here.

Concepts

Patterns

Operational numbers

  • MySQL 5.7.8: initial JSON type release date (mid-2015).
  • ~4–10 bytes: per-string additional storage compared to LONGBLOB/LONGTEXT.
  • 1 GB: MySQL theoretical per-document ceiling.
  • 67 MB: PlanetScale per-document cap.
  • "a few MB": vendor's practical recommendation regardless of engine ceiling.

Caveats

  • Short pedagogical post (~800 words, pre-Metal era); mechanism detail (binary encoding format, key-name dictionary layout, per-path update complexity) is elided.
  • No benchmark data — the 4–10 bytes/string overhead is rule-of-thumb, not a measured distribution over real documents.
  • Worked example is a flat string array — the object-containing-nested-arrays case at the top of the post is shown but the extraction operators are demonstrated only on the simpler array case.
  • Indexing discussion is deferred — the post points forward to Aaron Francis's Indexing JSON in MySQL (already wiki-ingested as sources/2026-04-21-planetscale-indexing-json-in-mysql) for the indexable-via-generated-column mechanism.
  • Postgres comparison is one-paragraph and one-side only — no JSONB performance or operator-set walk; concepts/postgres-jsonb-vs-mysql-json hosts the canonical cross-engine comparison.
  • JSON_TABLE gap stated without the workaround (client-side iteration or stored-procedure unnesting).
  • 2022-era article — surfaced 3.5 years after original publication; MySQL 8 has added further JSON functions since (JSON_VALUE, JSON_OVERLAPS, multi-valued indexes on JSON arrays in 8.0.17+) not covered in the original text.
  • Mike Stojan's second wiki ingest (first was 2023-01-11 Support's notes from the field); PlanetScale support-team voice, not a database-internals default- include byline.

Source

Last updated · 470 distilled / 1,213 read