MySQL JSON vs PostgreSQL JSONB: A Migration Field Guide

I have run JSON columns in production on both engines: MySQL 5.7 and 8.x with the native JSON type, and PostgreSQL with JSONB since 9.4. Both are genuinely good. Neither is a document database, and neither pretends to be. But they take different positions on the same three questions: how do you query a document, how do you index inside it, and what actually happens on disk when you update one field.

If you are running MySQL today and evaluating PostgreSQL, the JSON column is one of the places where the migration is less mechanical than it looks. The data moves over cleanly. The query patterns and the indexing strategy do not. This article is the comparison I wish someone had handed me before my first migration.

Two binary formats, two philosophies

MySQL introduced the JSON type in 5.7. It validates documents on write and stores them in an internal binary format that allows the server to jump to a key without reparsing the whole document. That was a real improvement over the TEXT-plus-application-parsing era, and MySQL 8.x rounded it out with JSON_TABLE, multi-valued indexes, and partial update support.

PostgreSQL has two types. Plain json stores the literal text, preserving whitespace, key order, and duplicate keys. JSONB parses to a binary format on write: duplicate keys collapse to the last one, key order is not preserved, and in exchange you get fast key access, containment operators, and GIN indexing. In practice almost everyone uses JSONB; json survives for audit-style cases where the exact original bytes matter.

The philosophical difference shows up in indexing. MySQL asks you to declare which paths matter, then index those. PostgreSQL lets you index the whole document and ask containment questions later. Both positions are defensible; they just push the work to different moments.

Operator ergonomics day to day

Both engines support the -> and ->> arrow operators for path extraction, and they behave similarly: -> returns a JSON value, ->> returns unquoted text. MySQL treats them as shorthand for JSON_EXTRACT and JSON_UNQUOTE(JSON_EXTRACT(...)), and most of its JSON surface lives in functions: JSON_CONTAINS, JSON_SET, JSON_SEARCH, JSON_OVERLAPS, MEMBER OF.

PostgreSQL leans harder on operators. The one I miss most when I am back on MySQL is containment:

-- PostgreSQL: find events whose payload contains this shape
SELECT id, payload ->> 'user_id' AS user_id
FROM events
WHERE payload @> '{"type": "checkout", "status": "failed"}';

The @> operator asks whether the left document contains the right one, at any nesting level the shape implies. Combined with the ? operator for key existence and jsonpath queries via @? and @@, you can express most document predicates without leaving operator land. MySQL can answer the same question with JSON_CONTAINS, and since 8.0.17 JSON_OVERLAPS and MEMBER OF cover array membership, but the expressions get wordier and, more importantly, the indexing story behind them is narrower.

For shredding documents into rows, both engines now have JSON_TABLE: MySQL since 8.0, PostgreSQL since version 17. On PostgreSQL 16 you would use jsonb_to_recordset or jsonb_array_elements instead, which work fine but read less declaratively.

Indexing: declared paths vs whole-document GIN

You cannot index a MySQL JSON column directly. The supported patterns are a generated column over an extracted path with an ordinary index on it, a functional index (8.0.13+, which creates a hidden virtual column for you), or a multi-valued index (8.0.17+) over a CAST of a JSON array. The generated-column pattern looks like this:

-- MySQL: extract a path, index the extraction
ALTER TABLE events
  ADD COLUMN user_id BIGINT
    GENERATED ALWAYS AS (payload ->> '$.user_id') VIRTUAL,
  ADD INDEX idx_events_user_id (user_id);

SELECT id FROM events WHERE user_id = 42;

This works well when you know your hot paths, and the optimizer can even rewrite predicates on the raw expression to use the generated column. The limitation is that every indexed path is a schema decision. A new query shape means a new column and a new index.

PostgreSQL has the same option: a B-tree expression index on (payload ->> 'user_id') behaves like the MySQL pattern without needing the column. But it also has GIN, which indexes every key and value in the document at once. A single GIN index with the default jsonb_ops operator class accelerates containment, key-existence, and jsonpath predicates for shapes you have not thought of yet. The jsonb_path_ops variant is smaller and faster for pure containment if you do not need key-existence support. GIN is not free: it is larger than a targeted B-tree, and write-heavy tables feel its maintenance cost, which is worth watching with proper PostgreSQL monitoring once you are live. But as a default for semi-structured search columns, it removes a whole category of "we did not index that path" incidents.

Update semantics: the part that surprises people

Here MySQL has a genuine edge. Since 8.0, JSON_SET, JSON_REPLACE, and JSON_REMOVE can perform partial in-place updates of the binary document when the new value fits in the existing space, and with binlog_row_value_options=PARTIAL_JSON the binary log can carry just the delta instead of the whole document. For workloads that repeatedly flip one flag inside a large document, that saves real I/O and replication bandwidth.

PostgreSQL offers jsonb_set and the || merge operator for expressing the change, but MVCC semantics mean every update writes a complete new row version, and a large JSONB value likely lives in TOAST, so the whole compressed document is rewritten. Updating one key in a 100 KB document costs roughly the same as replacing the document.

The honest guidance: if your access pattern is high-frequency small mutations inside big documents, that pattern is a poor fit for JSONB and was arguably a poor fit for a relational engine to begin with. On PostgreSQL you either keep hot mutable fields as ordinary columns and reserve JSONB for the stable payload, or you accept the rewrite cost. I wrote more about where that line sits in JSONB vs relational columns.

When MySQL JSON is enough

Being fair to the engine you are leaving: MySQL JSON is enough more often than PostgreSQL advocates admit. If your documents are read mostly whole, your filtered paths are few and stable, and you mutate small fields inside large documents, the generated-column pattern plus partial updates is a clean fit. Validation on write, JSON_TABLE for reporting, and multi-valued indexes for array membership cover a lot of ground.

The cases that push teams toward JSONB are ad hoc containment queries across unpredictable shapes, jsonpath filtering, GIN-backed search over heterogeneous payloads, and the wider ecosystem of JSONB-aware tooling. If your JSON column is really a lightweight search index over semi-structured data, PostgreSQL is playing a different sport.

Migration mapping notes

The mechanical mapping is straightforward. MySQL JSON columns become jsonb, not json, unless you have a documented need for byte-exact preservation. Remember that JSONB deduplicates keys and discards order, so any code that relied on key order or duplicate keys needs attention before cutover, not after.

Each generated-column index needs a decision: recreate it as a B-tree expression index for equality and range predicates on a scalar path, or fold it into a GIN index if the query is really containment. Rewrite JSON_CONTAINS calls as @> predicates, JSON_SEARCH as jsonpath or key-existence checks, and JSON_SET write paths as jsonb_set, keeping in mind the row-rewrite cost above. Validate with EXPLAIN on the PostgreSQL side early; the planner's choices around GIN versus sequential scans depend on statistics that only exist after a realistic data load.

One semantic edge deserves a test case of its own: null handling. MySQL distinguishes the SQL NULL of the column from a JSON null value inside a document, and PostgreSQL does the same with jsonb, but the functions that surface the difference are named and shaped differently. Any application logic that branches on "field is missing" versus "field is present but null" should be covered by tests that run against both engines during the transition window.

After the migration: watching JSONB in production with MonPG

JSONB workloads have distinctive failure modes: a GIN index that grows faster than the table, containment queries that flip to sequential scans after a data-shape change, TOAST churn from document rewrites, and autovacuum falling behind on update-heavy JSONB tables. None of that is visible from application logs.

MonPG monitors PostgreSQL only, so it will not watch your MySQL side during the transition. But once your workload lands on PostgreSQL, it keeps the evidence you need in one place: pg_stat_statements history to catch a JSONB query family regressing after a deploy, index usage and size trends for your GIN indexes, and vacuum and bloat signals for the tables absorbing document rewrites. The PostgreSQL monitoring guide covers the full baseline. Migrating the data is a weekend; understanding the new workload is the following quarter, and that part goes better with history.