paulserban.eu

Writing Edition

Paul Serban

Writing, snippets & book notes

Vol. 2026

From JSON Documents to SQL Tables: Patterns, Practices, and Pitfalls of Relational Migration

A practical guide to moving schema-less JSON stores into normalized relational databases without losing data integrity, performance, or your team's sanity

Introduction

Every engineering team that starts with a JSON-based store - whether that's MongoDB, a Firestore collection, DynamoDB, or even just a Postgres table with a single jsonb column holding "everything" - eventually reaches a point where the schema-less flexibility that made the early days fast becomes the exact thing slowing them down. Queries that once took a single find() call now require application-side joins across three collections. Reports that used to be "just export the collection" now demand aggregation pipelines nobody wants to maintain. At some point, someone on the team says the quiet phrase: "maybe we should just move this to a relational database."

This article is a practical walkthrough of that migration: the reasoning behind it, the technical patterns that make it tractable, the anti-patterns that quietly sabotage projects, and the pitfalls that catch even experienced teams off guard. It is not a blanket argument that SQL is superior to JSON-document storage - both have legitimate use cases - but a guide for the specific and common scenario where a team has outgrown the flexibility-first model and needs the guarantees, tooling, and query power that a normalized relational schema provides.

Context and Problem Overview

JSON-document databases are attractive early in a project's life because they remove a class of upfront design decisions. You don't need to know your full schema before you start writing code; you just serialize your application objects and store them. This is genuinely useful during early product discovery, when the shape of the data is still shifting weekly. The cost of this flexibility is deferred, not eliminated - it shows up later as data inconsistency, duplicated fields with slightly different names across documents, and application code that has to defensively check for the presence of fields that may or may not exist depending on when the document was written.

The technical symptoms that typically precede a JSON-to-SQL migration are fairly consistent across organizations. Query patterns shift from "fetch this one entity" to "aggregate across many entities with filters and joins," and document databases handle that class of query poorly compared to a query planner built for exactly that purpose. Analytics and reporting teams start asking for data that spans collections, and building that view means either denormalizing further (which multiplies write complexity) or reaching for a data warehouse that itself expects tabular input. Data integrity issues also accumulate: a document store generally does not enforce foreign-key relationships, uniqueness constraints, or cross-document consistency, so orphaned references and duplicate records creep in over time without anyone forcing a fix.

There is also an organizational dimension to this problem that is easy to underestimate. As a team grows, more people write to the same collections, and without a schema acting as a contract, different services or different engineers on the same service start writing structurally different documents for what is supposed to be the same entity. A relational schema, enforced by the database itself, becomes a form of documentation and validation that scales better across a growing team than tribal knowledge about "what fields this collection is supposed to have."

Deep Technical Explanation: Mapping JSON Structures to Relational Schemas

The core technical challenge in this migration is translating a nested, self-describing document model into a set of flat, strongly-typed tables connected by explicit relationships. This process is, at its heart, an application of classic database normalization theory - the same ideas Edgar F. Codd formalized for relational databases decades ago - applied retroactively to data that was never designed with normal forms in mind.

The first step is almost always identifying entities and their natural keys. A JSON document representing an "order" might embed the customer's name, address, and a list of line items directly inside it. In a relational model, the customer becomes its own table with a primary key, the order references that key as a foreign key, and the line items become a separate child table referencing the order. This is first normal form and beyond in practice: eliminating repeating groups (arrays of sub-objects) by moving them into their own tables, and eliminating duplicated attribute data (the same customer's address appearing in every one of their orders) by moving it into a single authoritative row.

A second technical consideration is handling the genuinely variable or sparse parts of the original JSON - fields that only some documents had, or free-form key-value data that doesn't map cleanly to fixed columns. Teams often reach for one of three patterns here: an Entity-Attribute-Value (EAV) table for truly dynamic attributes, a jsonb column in Postgres to retain a bounded amount of semi-structured data alongside the relational core, or simply accepting a wider table with nullable columns if the variability is limited and enumerable. Each has trade-offs in query performance and schema clarity, discussed further in the pitfalls section below.

A third consideration, often underestimated, is type coercion. JSON's type system is loose - numbers, strings, booleans, null, and untyped objects - while SQL columns are strict. Migrating from JSON to SQL forces decisions that were previously implicit: should a "price" field that sometimes arrived as "19.99" (string) and sometimes as 19.99 (number) become a DECIMAL(10,2) or a NUMERIC? Should a date stored as an ISO string become a native DATE or TIMESTAMP type, and in what timezone? These decisions should be made deliberately and validated against the actual historical data, not assumed from the current application code's expectations, because production data almost always contains more variation than the current schema suggests.

Finally, referential integrity itself needs to be designed, not just assumed. Document databases frequently store references by ID without any enforcement that the referenced document exists. When those references become SQL foreign keys, the migration will surface every broken reference that has quietly accumulated - a orphaned order pointing to a deleted customer, for instance - and the team needs a strategy for these before constraints can be turned on: either data cleanup, soft-deletion patterns, or ON DELETE SET NULL semantics where appropriate.

Implementation and Practical Migration Patterns

In practice, migrations tend to follow one of two broad strategies: a "big bang" cutover, where the JSON store is fully transformed and the application switches over at a single point in time, or an incremental "dual-write" migration, where both systems are kept in sync during a transition period. The dual-write approach is safer for systems that cannot tolerate downtime, but it requires careful reconciliation logic and is more engineering effort overall. For most production systems handling live traffic, the incremental approach is worth the extra cost.

A typical migration script extracts documents in batches, transforms each into its relational representation, and inserts rows within a transaction so that a partially-transformed entity never lands in an inconsistent state. The following Python example illustrates the shape of this logic for migrating "order" documents from a MongoDB-style collection into normalized Postgres tables:

import psycopg2
from psycopg2.extras import execute_values
from pymongo import MongoClient
from decimal import Decimal, InvalidOperation

def coerce_price(value):
    """JSON prices arrive as str, int, or float; normalize to Decimal."""
    try:
        return Decimal(str(value)).quantize(Decimal("0.01"))
    except (InvalidOperation, TypeError):
        return None

def migrate_orders_batch(mongo_db, pg_conn, batch_size=500, last_id=None):
    query = {"_id": {"$gt": last_id}} if last_id else {}
    cursor = mongo_db.orders.find(query).sort("_id", 1).limit(batch_size)
    documents = list(cursor)
    if not documents:
        return None  # migration complete

    with pg_conn.cursor() as cur:
        for doc in documents:
            # Upsert the customer, returning the relational primary key
            cur.execute(
                """
                INSERT INTO customers (external_id, name, address)
                VALUES (%s, %s, %s)
                ON CONFLICT (external_id) DO UPDATE
                    SET name = EXCLUDED.name, address = EXCLUDED.address
                RETURNING id
                """,
                (
                    str(doc["customer"]["_id"]),
                    doc["customer"].get("name", "").strip(),
                    doc["customer"].get("address"),
                ),
            )
            customer_id = cur.fetchone()[0]

            cur.execute(
                """
                INSERT INTO orders (external_id, customer_id, placed_at, status)
                VALUES (%s, %s, %s, %s)
                ON CONFLICT (external_id) DO NOTHING
                RETURNING id
                """,
                (str(doc["_id"]), customer_id, doc.get("createdAt"), doc.get("status", "unknown")),
            )
            row = cur.fetchone()
            if row is None:
                continue  # already migrated
            order_id = row[0]

            line_items = [
                (order_id, item.get("sku"), item.get("qty", 0), coerce_price(item.get("price")))
                for item in doc.get("items", [])
                if item.get("sku")
            ]
            if line_items:
                execute_values(
                    cur,
                    "INSERT INTO order_items (order_id, sku, quantity, unit_price) VALUES %s",
                    line_items,
                )

    pg_conn.commit()
    return documents[-1]["_id"]

This script demonstrates several practices worth calling out: it processes data in bounded batches rather than loading an entire collection into memory, it uses ON CONFLICT upserts so the migration is idempotent and safely re-runnable after a partial failure, and it isolates type coercion (coerce_price) as a testable function rather than inlining ad-hoc parsing logic into the loop. Idempotency in particular is not optional for any migration expected to run more than once - and almost every real migration runs more than once, because something will fail partway through the first attempt.

On the application side, once the relational schema exists, the data access layer typically shifts from ad-hoc document queries to either raw SQL or an ORM/query builder. A TypeScript example using a typed query builder such as Kysely illustrates how the previous "fetch one document with everything nested" pattern becomes an explicit join:

import { Kysely } from "kysely";
import type { Database } from "./schema";

async function getOrderWithItems(db: Kysely<Database>, orderId: number) {
  const order = await db
    .selectFrom("orders")
    .innerJoin("customers", "customers.id", "orders.customer_id")
    .select([
      "orders.id",
      "orders.status",
      "orders.placed_at",
      "customers.name as customer_name",
    ])
    .where("orders.id", "=", orderId)
    .executeTakeFirst();

  if (!order) return null;

  const items = await db
    .selectFrom("order_items")
    .selectAll()
    .where("order_id", "=", orderId)
    .execute();

  return { ...order, items };
}

This shift is not purely mechanical - it changes how engineers think about data access. Instead of retrieving a self-contained blob and trusting its internal consistency, the application now composes explicit, typed queries and relies on the database to enforce the relationships between them.

Trade-offs and Common Pitfalls

The most consequential anti-pattern in these migrations is designing the target schema by mechanically flattening the existing JSON shape rather than by modeling the actual business domain. If a document has a nested shippingAddress object, the instinct is often to create a shipping_address table with the same fields, in the same nesting, without asking whether that structure reflects how the data is actually used or queried. This produces a relational schema that inherits all of the document model's ad-hoc history while gaining none of the benefits of a schema designed with hindsight. A better approach treats the migration as an opportunity to model the domain properly - using entity-relationship modeling based on how the data is queried and constrained today, not how it happened to be nested when someone first wrote the serialization code three years ago.

A second common pitfall is over-normalization, sometimes called "normalization for its own sake." Teams coming from a document background, newly excited about relational integrity, occasionally split every nested object into its own table even when the data has no independent existence or query pattern that benefits from separation. A color and size pair embedded in a product variant, for instance, does not necessarily need to become a separate product_attributes table if the application never queries across products by attribute value - sometimes a couple of nullable columns are the pragmatic choice. Excessive normalization increases the number of joins required for common queries and can hurt read performance without a corresponding integrity benefit.

The EAV (Entity-Attribute-Value) pattern deserves particular caution. It's frequently proposed as a way to preserve JSON-like flexibility inside a relational database - a table of (entity_id, attribute_name, attribute_value) rows that can represent arbitrary key-value pairs. While it solves the flexibility problem, it reintroduces most of the weaknesses of the document model (no meaningful type safety per attribute, difficult-to-write queries, poor query planner statistics) while adding relational overhead on top. EAV is occasionally the right tool for genuinely unbounded, rarely-queried metadata, but teams should treat it as an escape hatch for a small minority of fields, not a general migration strategy.

A more subtle pitfall involves silent data loss during type coercion. JSON's permissive typing means production documents almost always contain values that don't cleanly fit the target column type - a null where a NOT NULL constraint is planned, a string where a number is expected, a date format that varies by client version. Migration scripts that coerce these values with a fallback of "just insert null" or "just skip the row" without logging and reviewing every coercion failure will lose real data silently, and the loss often isn't noticed until a customer complains that historical records are missing months later. Any migration should treat coercion failures as first-class events, logged to a dead-letter table for manual review rather than swallowed.

Best Practices for a Safe Migration

The single most valuable practice is validating the target schema against real production data before writing any migration code, not against the application's current model of what the data should look like. This typically means running an analysis pass over the existing JSON collection - checking field presence rates, value type distributions, and cardinality of supposedly enumerable fields - before finalizing column types and constraints. It is far cheaper to discover that 2% of "phone number" fields contain non-numeric junk during this analysis phase than to discover it mid-migration when a CHECK constraint starts rejecting rows.

Migrations should also be built to run repeatedly and safely, using the idempotency techniques shown earlier (upserts keyed on a stable external identifier, transactional batches, and checkpointing progress so a failed run can resume rather than restart). Treating the migration script itself as production code - with tests, code review, and a staging run against a realistic data snapshot - pays for itself the first time an edge case in production data breaks an assumption that held in every test fixture.

Running both systems in parallel for a verification window, even in a "big bang" cutover, catches discrepancies that automated tests miss. A reconciliation job that periodically compares row counts, checksums of key fields, or spot-samples of full records between the old and new systems provides a safety net that catches subtle transformation bugs - an off-by-one in decimal rounding, a timezone conversion applied twice - before they become customer-facing incidents.

Analogies and Mental Models

A useful mental model for this migration is the difference between a filing cabinet of complete folders and a well-organized library card catalog. The JSON document store is the filing cabinet: each folder is self-contained, and you can add whatever you want to any folder without asking permission. It's fast to file things away, but finding all folders that share some property means opening every folder by hand. The relational schema is the card catalog: information is indexed and cross-referenced deliberately, which takes more upfront design work, but lets you answer "which folders share this property" by looking in one place instead of searching everything.

Another helpful frame is thinking of the JSON-to-SQL migration as similar to refactoring untyped code into a statically typed language. Just as adding types to a JavaScript codebase forces you to confront every place where a value's shape was assumed rather than guaranteed, mapping JSON documents onto SQL columns forces you to confront every place where a field's presence, type, or range was assumed by application code rather than enforced by the data layer. The migration effort is largely the cost of making those implicit assumptions explicit - and, often, discovering how many of them were wrong.

The 80/20 Insight

Most of the risk and most of the value in a JSON-to-SQL migration concentrate in a small number of activities. Getting the entity-relationship model right - correctly identifying which nested structures are truly independent entities versus which are attributes of a parent - determines more of the migration's long-term success than any amount of tooling sophistication. Similarly, rigorous type coercion and validation against real production data, rather than idealized schema assumptions, prevents the majority of silent data-loss incidents that damage trust in the new system.

Conversely, a large amount of engineering effort in these projects gets spent on secondary concerns - choosing the perfect ORM, optimizing migration script runtime, or debating table naming conventions - that have comparatively little impact on whether the migration succeeds. Teams that front-load their effort into domain modeling and data validation, and treat tooling choices as a secondary decision, consistently ship migrations with fewer post-launch surprises than teams that optimize the tooling first and discover schema problems in production.

Key Takeaways

Conclusion

Migrating from a JSON document model to a relational SQL schema is fundamentally a project of making implicit assumptions explicit - about data types, relationships, and integrity constraints that the original document store never forced anyone to state clearly. The mechanical work of writing extraction and transformation scripts is real, but it is rarely the hardest part. The hardest part is the modeling work: understanding what the data actually represents, how it is actually queried, and where the messy reality of years of accumulated documents diverges from what the application code assumes.

Done well, this migration produces more than just a faster database. It produces a shared, enforced contract for what the data means - one that scales across a growing engineering team far better than tribal knowledge about "what fields this collection usually has." Done poorly, by mechanically flattening JSON into tables without domain modeling, it produces a relational database that has all the historical mess of the document store with none of its original flexibility. The patterns and pitfalls in this article are aimed at helping teams land on the former outcome.

References

Resources