CONCEPT

Postgres JSONB vs MySQL JSON

Postgres has two JSON types: JSON (text storage) and JSONB (binary storage, indexable, more compact). MySQL has one: JSON. At the capability layer the two engines are close; at the engine layer Postgres's JSONB is typically more storage-efficient.

What each engine gives you

• Postgres JSONB — binary in-engine representation. Supports:
• GIN indexes over the whole document.
• Key-path operators (->, ->>, #>, #>>).
• Containment and existence (@>, ?, ?&, ?|).
• Inline modification (jsonb_set).
• MySQL JSON — binary in-engine representation. Supports:
• Functional indexes on extracted keys (see concepts/functional-index-mysql).
• Key-path extraction (->, ->>).
• Inline modification (JSON_SET, JSON_INSERT, JSON_REPLACE, JSON_REMOVE).
• Multi-valued indexes on JSON arrays (MySQL 8.0.17+).

Migration implication

In a Postgres → MySQL audit:

the products column, on the other hand, is of type jsonb, or binary JSON. While MySQL at the moment does not have support for the jsonb data type, it does have support for json, so if we set the data type for the products column as json, we'll get a very similar experience.

—

The drop-in answer is Postgres JSONB → MySQL JSON. The feature gap at the query layer is small: a JSON-heavy workload will generally port. For how far MySQL JSON goes at the indexing layer see .

Where the gap still bites

• Storage efficiency — Postgres's JSONB is typically more compact on disk because it shares key-name storage across rows. MySQL's JSON encoding is similar in spirit but not bit-identical, and measured sizes diverge.
• GIN index over the whole document — Postgres supports a single GIN index that answers arbitrary containment queries; MySQL requires a per-path functional index.
• Operator ecosystem — Postgres's @>, ?, ?&, ?| have no one-to-one MySQL equivalents; they rewrite to JSON_CONTAINS, JSON_CONTAINS_PATH, JSON_OVERLAPS.

PlanetScale-specific caveats

• Per-document ceiling — PlanetScale caps MySQL JSON documents at 67 MB (vs MySQL's 1 GB theoretical). Postgres JSONB under PlanetScale for Postgres has its own policy, not equated here.
• JSON_TABLE gap — PlanetScale supports every MySQL JSON function except JSON_TABLE (the one that pivots a JSON array into relational rows). A Postgres jsonb_to_recordset port needs a client- side loop or a stored-procedure unnesting.

(Source: .)

Related

• concepts/cross-engine-sql-data-type-equivalence
• concepts/mysql-json-data-type — canonical wiki page for the MySQL side of this comparison.
• concepts/json-data-type-storage-overhead — the 4–10 bytes/string rule-of-thumb that extends the "storage efficiency" bullet above with a quantitative rule.
• concepts/mysql-json-extraction-operator — the -> / ->> operators that provide the MySQL side of the key-path-extraction equivalence.
• concepts/functional-index-mysql — the MySQL indexing mechanism that approximates Postgres's GIN over a single JSON path.
• patterns/cross-engine-database-migration-audit