Turn JSON into SQL INSERT Statements (Without Writing a Script)

Last updated on

You have a JSON array and you need it as rows in a database. Here’s how to turn JSON into SQL INSERT statements — the quick path, the scripted path, and the gotchas that corrupt your data.

The shape that maps cleanly

A flat array of objects maps 1:1 to table rows:

[
  {"id": 1, "email": "ada@x.com", "active": true},
  {"id": 2, "email": "alan@x.com", "active": false}
]
INSERT INTO users (id, email, active) VALUES
  (1, 'ada@x.com', true),
  (2, 'alan@x.com', false);

Keys become columns; each object becomes a row. The JSON to SQL converter generates this in your browser — paste JSON, get the INSERT.

Doing it in code

For a pipeline, generate the SQL yourself so you control quoting and batching — but use parameterized queries for anything touching real input. String-built INSERTs are fine for a one-time seed, dangerous for user data.

The gotchas that corrupt data

  • Quotes in strings. O'Brien breaks a naive '...' wrap. Escape single quotes by doubling them (''), or parameterize.
  • NULL vs empty string. JSON null should become SQL NULL, not the text 'null' or ''.
  • Booleans. Postgres takes true/false; MySQL wants 1/0.
  • Nested objects/arrays. A JSON value that’s itself an object doesn’t fit a scalar column — store it in a JSON/JSONB column or flatten it first.
  • Big batches. Thousands of rows in one statement can hit packet limits; chunk into batches of ~500.

Rule of thumb

For a one-off seed or migration, generate the INSERTs and eyeball the output. For anything recurring or user-facing, load via a parameterized query in your app’s database driver — never hand-built SQL strings.

Related articles