Rasika Dangamuwa

Posted on Sep 27

Why JSON-to-SQL Conversion Fails in Production: 5 Schema Traps That Corrupt Data Migrations

#database #sql #javascript #programming

Every engineering team eventually runs into the migration script: a one-off Python script, a Node CLI, or a shell pipeline tasked with converting hundreds of thousands of JSON records—exported from MongoDB, Stripe webhooks, or an internal event log—into relational tables.

On paper, JSON-to-SQL conversion seems trivial: parse the JSON, infer column types from the first record, generate a CREATE TABLE statement, and format a batch of INSERT INTO queries.

In reality, naive converters consistently corrupt production datasets or crash halfway through multi-gigabyte ingestion jobs. Below are five real-world edge cases where JSON-to-SQL conversion silently fails, and the engineering strategies required to prevent them.

1. The IEEE 754 64-Bit Integer Truncation (The Snowflake ID Bug)

JSON specification (RFC 8259) does not distinguish between integers and floating-point numbers; all numbers are arbitrary-precision decimal numbers. However, standard parsers in JavaScript (JSON.parse) and many dynamic languages evaluate numeric fields as standard IEEE 754 double-precision floats.

This creates a dangerous threshold at Number.MAX_SAFE_INTEGER ($2^{53} - 1$, or 9,007,199,254,740,991). If your JSON includes modern 64-bit distributed identifiers—such as Twitter/Discord Snowflake IDs, high-volume database sequences, or ledger IDs—the lower bits get silently rounded:

{
  "transaction_id": 9007199254740993,
  "account_id": 9007199254740995
}

When parsed in standard JavaScript before generating SQL, transaction_id becomes 9007199254740992. When inserted into a PostgreSQL or MySQL BIGINT column, you have silently corrupted primary keys and broken relational joins.

Solution: Treat all large ID fields as strings during JSON parsing (using lossless parsers like json-bigint in Node or json.loads(..., parse_float=Decimal) in Python), or quote large numeric IDs prior to conversion.

2. Heterogeneous Nullability and Sparse Fields

Unlike relational tables with rigid column definitions, JSON documents in document stores and event streams are inherently schema-less. Consider this payload:

[
  { "id": 101, "user_id": "u_891", "discount": 15 },
  { "id": 102, "user_id": "u_892" },
  { "id": 103, "user_id": "u_893", "discount": null }
]

If your conversion tool inspects only the first row to derive schema, it will declare discount INT NOT NULL. When the parser hits row 2, it will either throw an Undefined key exception or generate an invalid INSERT statement that omits the column entirely.

Furthermore, an omitted key in JSON usually carries semantic distinction from an explicit null. In an update payload, an omitted key means "do not touch", whereas null means "clear existing value".

Solution: Always scan all rows in the dataset (or a large statistical sample) to calculate the union of all keys. Mark any column that is missing or explicitly null in any record as nullable in your DDL.

3. Quote Escaping and Literal Injection

A common anti-pattern in ad-hoc migration scripts is string interpolation:

# Dangerous: breaks on apostrophes and opens syntax errors
sql = f"INSERT INTO users (id, name, bio) VALUES ({row['id']}, '{row['name']}', '{row['bio']}');"

The moment customer input contains an apostrophe—such as Bob O'Connor or Chef's Special—the SQL engine interprets the apostrophe as a closing string delimiter, immediately terminating the transaction with a syntax error. If the payload contains raw Windows file paths (C:\Program Files\App), unescaped backslashes can trigger unintended escape sequences in MySQL or PostgreSQL.

When transforming raw API payloads or rapid mock datasets, using a client-side utility like Nutilz JSON to SQL handles dialect-specific quote doubling and escaping automatically across PostgreSQL, MySQL, SQLite, and MSSQL directly in your browser without transmitting sensitive customer data across third-party networks.

Solution: For automated scripts, always use parameterized queries (cursor.executemany()) or dialect-aware escaping where single quotes are doubled ('') rather than backslash-escaped.

4. Timestamp Offsets vs. Local Server Clocks

JSON dates are serialized as ISO-8601 strings, such as:

{
  "event": "checkout_completed",
  "timestamp": "2026-03-10T14:30:00+05:30"
}

When migrating to relational databases, dialect differences become a minefield:

PostgreSQL: Differentiating between TIMESTAMP (without time zone) and TIMESTAMPTZ (with time zone). If mapped to TIMESTAMP, PostgreSQL discards the offset and stores the literal 14:30:00, shifting event history by 5.5 hours.
MySQL: The DATETIME type does not store time zone offsets. If you insert a timezone-offset string, MySQL either truncates it or converts it based on the current session time zone.
SQLite: Stores dates as plain TEXT without built-in timezone conversion unless using SQLite date helper functions.

Solution: Explicitly map all ISO-8601 strings to TIMESTAMPTZ in PostgreSQL or normalize all timestamps to UTC (Z) before executing batch INSERT operations.

5. Nested Objects: Normalization vs. JSONB Storage

When JSON contains nested objects or arrays, naive converters face an architectural dilemma:

{
  "order_id": "ord_901",
  "customer": { "name": "Sarah", "email": "sarah@example.com" },
  "items": [
    { "sku": "SKU-10", "qty": 2, "price": 19.99 },
    { "sku": "SKU-25", "qty": 1, "price": 49.00 }
  ]
}

Engineers often take the path of least resistance: flatten 1-to-1 objects into prefixed columns (customer_name, customer_email) and dump arrays directly into a JSON or JSONB column.

While modern PostgreSQL handles JSONB queries well, storing transactional line items inside an unindexed JSON array eliminates foreign key constraints, prevents database-level cascade deletions, and makes SQL aggregations (like calculating monthly SKU sales) significantly slower and more complex.

Solution: Follow a clear decision rule:

Flatten 1-to-1 metadata into the parent table if fields are fixed.
Normalize 1-to-many collections (like items) into separate relational child tables with foreign keys.
Reserve JSONB strictly for truly unstructured metadata that varies wildly between records.

Conclusion

Translating JSON into relational SQL is rarely a matter of simple syntax formatting. Safe migrations require accounting for numeric precision boundaries, dynamic schema sparsity, character escaping, and timezone normalization.

For day-to-day schema prototyping, inspecting webhook payloads, or converting API dumps into clean DDL and INSERT statements, keeping Nutilz's free JSON to SQL converter in your browser toolkit gives you instant, privacy-focused SQL generation across all major database engines.

DEV Community