Skip to content

CONCEPT Cited by 2 sources

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.

— sources/2026-04-21-planetscale-migrating-from-postgres-to-mysql

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 sources/2026-04-21-planetscale-indexing-json-in-mysql.

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.
Last updated · 378 distilled / 1,213 read