Turning a JSON Array Into SQL INSERT Statements

Every developer hits this moment. You have a JSON array sitting in front of you, maybe from an API response, maybe exported from another table, and you need that exact data inside a SQL database. The data is right there. The only thing standing between you and a populated table is the conversion. Writing a one-off import script for fifty rows feels like overkill, and hand-typing the INSERT statements is how you end up with a stray quote that breaks the whole batch at 6pm.

This post walks through how the conversion actually works, where it goes wrong, and how to do it cleanly. I'll use the JSON to SQL Converter for the worked example, but the mechanics apply no matter how you do it.

Keys Become Columns, Values Become VALUES

The core idea is simple: each object in the array is one row, each key is a column, and each value goes into the VALUES list. The thing most people get wrong is the column list itself. If you grab the keys from the first object only, any row with an extra or missing field falls out of alignment, and the database either rejects the statement or quietly writes data into the wrong column.

The correct move is to take the union of every key across all rows. Object A has id, name, email. Object B has id, name, but no email. The column list is (id, name, email), and Object B gets NULL in the email slot. That way a ragged array still maps into one consistent table instead of a pile of mismatched statements.

So the shape of the output is:

INSERT INTO table_name (col1, col2, col3) VALUES (v1, v2, v3);

One row in, one statement out, with every column accounted for.

How Each Value Type Gets Written

JSON has loose types. SQL is strict about how literals are written, and getting this wrong is the second big trap. Here is the mapping that matters:

• Strings are wrapped in single quotes: 'Ada'.
• Numbers are written bare, no quotes: 42, 3.14.
• Booleans depend on the engine. PostgreSQL takes TRUE / FALSE. MySQL and SQLite have no real boolean, so they take 1 / 0.
• null becomes the keyword NULL, never the string 'null'.
• Nested objects and arrays can't sit in a single relational column, so they get serialized to a JSON string and stored in one column. A value like {"city":"London"} becomes the literal '{"city":"London"}', which loads fine into a JSON or JSONB column.

That boolean detail trips people up more than you'd think. Generate TRUE and try to load it into SQLite and it errors, because SQLite has no such keyword. Match the dialect to the target and the converter switches the boolean style for you.

The Single Quote Problem

This is the one thing that breaks naive JSON-to-SQL conversion, so it deserves its own section. A string value containing a single quote — a name like O'Brien, a phrase like it's done — will close the SQL string literal early if you paste it raw. Everything after the apostrophe becomes garbage the parser can't make sense of.

The SQL standard escape is doubling the quote: every inner ' becomes ''. So O'Brien is written as the literal 'O''Brien'. The doubled quote is two single-quote characters, not a double-quote character, and the database reads it as one apostrophe sitting inside the string. The literal never closes early.

What you should not do is reach for a backslash escape like \'. That works in some programming languages and even in MySQL's default mode, but it is not SQL standard and it breaks under strict modes like ANSI. Leave your raw values alone in the JSON and let the conversion double the quotes for you.

A Worked Example

Here is a small array with all the gotchas packed in — a missing field, an apostrophe, a boolean, and a number:

[
  {"id": 1, "name": "Ada", "active": true},
  {"id": 2, "name": "O'Brien", "active": false, "note": "needs review"}
]

Set the table name to users, pick PostgreSQL, and one INSERT per row. The output is:

INSERT INTO users (id, name, active, note) VALUES (1, 'Ada', TRUE, NULL);
INSERT INTO users (id, name, active, note) VALUES (2, 'O''Brien', FALSE, 'needs review');

Read it against the rules above. The column list is the union of every key, so note appears even though the first object lacks it, and that first row gets NULL. The apostrophe in O'Brien is doubled into O''Brien. The booleans came out as TRUE / FALSE because I chose PostgreSQL; switch to MySQL and they'd be 1 / 0, with the identifiers wrapped in backticks instead of double quotes.

One Statement Per Row, or One Bulk INSERT

There are two output shapes, and they serve different purposes.

One INSERT per row is what you saw above. It reads cleanly, it makes a tidy git diff when the data changes, and it's the right choice for a migration or seed file where a human will review the lines later.

A single multi-row VALUES statement merges everything into one:

INSERT INTO users (id, name, active, note) VALUES
  (1, 'Ada', TRUE, NULL),
  (2, 'O''Brien', FALSE, 'needs review');

This loads much faster for bulk data because the database parses and commits once instead of once per statement. When QA needs a few hundred fixture rows in a test database before a run, the multi-row form drops the whole batch in a single round trip. The trade-off is readability: a 300-row multi-row INSERT is not something you want to eye-review in a migration. Pick the shape based on whether a human or a database is the next reader.

Where I Reach for This

I keep this conversion in my back pocket for seed files. When a project needs reference data — a list of countries, subscription plans, feature flags — I keep the source as readable JSON in the repo and regenerate the SQL whenever it changes. Editing JSON and re-running the converter beats hand-patching INSERT lines every time, and it means the data has a single source of truth that isn't buried in a migration. The last time I moved a table between a MySQL service and a PostgreSQL one, I exported it as JSON, flipped the dialect, and the backticks turned into double quotes and the 1/0 booleans turned into TRUE/FALSE without me touching a single line by hand. That's the whole point — the mechanical drudgery is exactly what a tool should absorb.

A Few Notes Before You Run It

If your JSON has nested objects and you actually need address.city as its own column, the conversion won't unpack that for you — nested data lands in one column as a JSON string. Flatten the JSON first if you want real sub-columns.

And once you have a wall of generated SQL, a quick pass through a SQL formatter makes it readable before it goes into a migration file, lining up the keywords and indentation so a reviewer can actually scan it.

The conversion itself is mechanical, but every step has a way to go wrong: the wrong column list, an unescaped quote, the wrong boolean style for the engine. Get those three right and a JSON array becomes a working table in seconds.

---

Made by Toolora · Updated 2026-06-13