JSON to SQL: INSERT Statements, Table Design, and Type Mapping Traps

Bottom line: decide what business entity one row represents before designing tables, then write explicit multi-row VALUES inside a single transaction; settle field naming and type mapping before the import instead of patching them in SQL.

JSON describes shape; SQL also describes constraints. When you move the former into the latter, every constraint you did not write down comes back later as dirty data.

1. Decide first: one table or one record per JSON row

Look at the outermost layer of the data before designing any table. Get this decision wrong and every INSERT after it has to be rewritten.

There is only one criterion: what business entity does one row represent? If a row is an order, the line items must live in their own table. If a row is a user, tags belong in a child table or a JSON column, not in a comma-joined string.

2. From an array to INSERT: the smallest runnable template

The safest form always lists the column names explicitly and never relies on column order:

复制CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT UNIQUE
);

INSERT INTO users (id, name, email) VALUES
  (1, 'Ada', 'ada@example.com'),
  (2, 'Linus', NULL);

Three details matter:

• Write every column name. When the schema later gains a column, nothing shifts into the wrong field.
• Multi-row VALUES works in SQLite, PostgreSQL, and MySQL, and saves a large number of round trips compared with a loop of single-row inserts.
• NULL is a keyword; do not quote it. Writing 'NULL' stores a four-character string.

If the JSON keys do not match the column names (userId versus user_id), add a mapping layer. Do not guess with COALESCE inside the SQL. Fixing field names in the data layer is much cheaper than patching them in queries.

3. Nested objects: a parent table plus a child table

Orders are the classic one-to-many case:

复制INSERT INTO orders (id, customer_name, total_cents) VALUES
  (1001, 'Ada', 12900);

INSERT INTO order_items (order_id, sku, quantity) VALUES
  (1001, 'A-1', 2),
  (1001, 'B-7', 1);

This corresponds to the JSON {"id":1001,"customer":"Ada","total":129.00,"items":[{"sku":"A-1","quantity":2},{"sku":"B-7","quantity":1}]}.

Key points:

• The length of the items array determines the number of child rows; the parent table gets exactly one row.
• Store money as integer cents (total_cents), never as FLOAT.
• Give the child table an order_id foreign key and commit both inserts in one transaction, otherwise the parent can exist while its items are missing.
• If the data is read-only and nobody filters by line item, a JSON column is acceptable (PostgreSQL jsonb, MySQL JSON, or SQLite TEXT with the json1 functions), but accept the cost: no indexing and awkward queries.

Flatten fixed nested objects such as user.address into columns (address_city, address_zip). Split into a table only when the nesting can keep growing; splitting a fixed shape just adds JOINs.

4. NULL versus missing fields: SQL has no "absent key"

JSON lets a key be absent entirely. SQL columns are fixed. After an import both cases look identical — NULL — even though they can mean different things:

Two common mistakes:

1. Using an empty string instead of NULL. Later, WHERE email IS NULL misses those rows, and your "users without an email" metric silently undercounts.
2. Testing with col = NULL. In SQL's three-valued logic, NULL = NULL evaluates to NULL, not TRUE. You must write IS NULL:

复制SELECT COUNT(*) FROM users WHERE email IS NULL;

If you need to preserve whether a key was present at all, add a boolean marker column rather than inferring it from NULL. The API contract should state this too; see JSON API design and field naming.

5. Quoting and escaping: never concatenate SQL strings

JSON strings can contain single quotes, double quotes, backslashes, newlines, and even \u0000. With hand-built strings, one missed escape shifts the rest of the statement, and in the worst case becomes SQL injection.

In standard SQL, a single quote is escaped by doubling it:

复制INSERT INTO notes (id, content) VALUES (1, 'It''s a ''quoted'' value');

Production code should use parameter binding and let the driver handle escaping and types:

复制// PostgreSQL uses $1/$2, MySQL uses ?; this example uses node-postgres
await client.query('INSERT INTO users (id, name) VALUES ($1, $2)', [row.id, row.name]);

Only hand-escape when you must export a .sql file, and then at minimum:

• replace ' with '';
• if newlines are preserved, confirm the target database accepts multi-line string literals;
• strip \u0000, since most databases reject NUL inside strings;
• MySQL treats backslash as an escape character by default, so \ must become \\, or switch to standard mode.

Double quotes from JSON need no escaping inside a SQL single-quoted string. Adding an extra layer of escaping is how stray backslashes end up in your data.

6. Type mapping: where the six JSON values land in SQL

JSON has only string, number, boolean, null, object, and array. SQL has a much finer type system. The mistakes usually happen in precision and semantics rather than syntax:

Booleans hide the subtlest bug: JSON false becomes 0 in MySQL TINYINT(1), round-trips look fine, and nobody notices. But the moment some code does if (value) on the string "false", it evaluates as truthy. Conversion scripts should call Boolean(value) explicitly.

PostgreSQL JSONB parses and deduplicates keys, keeping the last occurrence, which suits querying; JSON preserves the original text and key order. SQLite JSON is just TEXT plus a set of functions, with no real type constraint. Before choosing, ask whether this table needs filtering on fields inside the JSON.

7. Bulk inserts and transactions: batch, commit, make it rerunnable

As volume grows, three things decide whether an import stays controllable:

1. Batch. A loop of N single-row INSERT statements costs N network round trips. Combine 500 to 1000 rows into one multi-row VALUES, or use the database's own bulk loader (PostgreSQL COPY, MySQL LOAD DATA, multi-row inserts inside a SQLite transaction).
2. Use a transaction. All or nothing, so a half-loaded table never survives an error:

复制BEGIN;
INSERT INTO users (id, name) VALUES (1, 'Ada'), (2, 'Linus');
INSERT INTO order_items (order_id, sku, quantity) VALUES (1, 'A-1', 2);
COMMIT;

3. Make it idempotent. Import scripts get rerun constantly. Use an upsert instead of delete-then-insert:

ON CONFLICT requires a unique constraint or primary key, otherwise it never fires. When there is no natural key, build a unique index on a business key such as email. When there is no key at all, add a stable id on the JSON side — never rely on an auto-increment id for idempotency, because a rerun will insert duplicates.

8. Dialect differences at a glance

To keep one SQL file working across all three, at least avoid ON CONFLICT, backticks, and SERIAL. For cross-database projects, generate schema and upsert statements separately; do not expect one script to fit all.

9. Three spot checks after the import

Before deleting the source file, verify three things:

1. Row count. SELECT COUNT(*) FROM users; should equal the JSON array length. One missing row usually means the final record was truncated.
2. The column with the highest NULL rate. That points to inconsistent key names or unexpanded nesting. Go back and check whether the source uses both userId and user_id.
3. Money and long identifiers. Pick the maximum and minimum values and confirm neither floating point nor integer conversion truncated them.

If you need to understand the tabular shape first, use the flattening approach in JSON, CSV, and tabular data conversion. If the audience is colleagues who just want to read the data, the four ways to convert JSON to Excel are less work than standing up a database.

Why do numbers lose precision after importing JSON into SQL?

Integers above 2^53 have already lost precision during JavaScript's JSON.parse, and writing them into BIGINT cannot restore it. Such ids should be strings in JSON and converted to BIGINT or TEXT during the import.

What should I use as a primary key when the JSON has no id?

Let the database generate one, or create a stable UUID before importing. Never use the array index as an id: a rerun reorders the data and points the wrong rows at the wrong records. A unique index on a business key (email, order number) also enables upserts.

What do I do when a bulk INSERT hits a unique-key conflict?

Replace the plain INSERT with the matching upsert for your dialect, and confirm the conflicting column has a unique constraint. Deleting first and inserting afterwards also works, but it must happen inside one transaction and you accept a brief window with no rows.

Do nested arrays always need a child table?

No. For read-only display without querying by sub-item, a JSON column is simpler. As soon as you need to filter or aggregate by tag or SKU, a child table with indexes is the sustainable choice. The deciding factor is how you query, not how the data is shaped.