How to Convert SQL INSERT Statements to JSON

SQL INSERT statements are a perfectly good way to store data and an awkward one to read from application code. When you need the same rows as JSON — to seed a test fixture, feed a front-end mock, or compare against an API response — the conversion is simple in principle: each statement becomes an object, each column name becomes a key. The edge cases are where it goes wrong.

The basic mapping

One INSERT statement becomes one JSON object:

INSERT INTO users (id, name, active, deleted_at, note)
  VALUES (1, 'Ravi', TRUE, NULL, 'O''Brien''s friend');
INSERT INTO users (id, name, active, deleted_at, note)
  VALUES (2, 'Priya', FALSE, NULL, '42');

becomes a JSON array with one object per row:

[
  {
    "id": 1,
    "name": "Ravi",
    "active": true,
    "deleted_at": null,
    "note": "O'Brien's friend"
  },
  {
    "id": 2,
    "name": "Priya",
    "active": false,
    "deleted_at": null,
    "note": "42"
  }
]

That is exactly what the SQL to JSON tool produces for these statements. The rest of this guide is about the cases where the mapping isn't obvious.

How values map to JSON types

SQL literals don't carry a type the way JSON values do, so a converter has to infer one from how the value is written:

• Bare numbers (42, 3.14) become JSON numbers.
• Quoted values are always strings — including '42'. The quotes are the signal, so a number stored as text stays text. Look at the last row in the example above: "note": "42".
• TRUE and FALSE become JSON true and false.
• NULL becomes JSON null, not the string "NULL" — an easy thing to get wrong in a hand-rolled script.

Things that trip people up

Multi-row INSERTs. Dumps from tools such as mysqldump often pack many rows into one statement: INSERT INTO t (a, b) VALUES (1, 'x'), (2, 'y');. Each parenthesised tuple is its own row, so that statement should produce two objects, not one. A simple hand-rolled converter that stops at the first closing parenthesis will silently return only the first row, so whichever tool you use, check that the number of objects in the JSON matches the number of rows you expect. The SQL to JSON tool reads every tuple in a multi-row statement.

A column list is required. JSON keys come from the column names, so INSERT INTO users VALUES (1, 'Ravi') — with no column list — gives the converter nothing to name the keys with. Add the column list, matching the table's column order.

Escaped quotes. Standard SQL escapes a single quote by doubling it, so 'O''Brien' is the string O'Brien. That is handled. A backslash escape like 'O\'Brien', which MySQL accepts in some configurations, is not standard SQL and may be misread — rewrite it with a doubled quote.

Dates and timestamps. A value such as '2026-01-15 10:30:00' is a quoted literal, so it stays a string in the JSON. JSON has no date type, so that is correct — but whatever reads the JSON needs to parse it.

Check the result before you rely on it

• Count the objects in the output against the rows you expected.
• Spot-check a row with a NULL, a quoted number, and an apostrophe — those are the three that break first.
• Remember that SQL NULL and an empty string '' are different values; they come out as null and "" respectively.

Going the other way

If you need the reverse — JSON to INSERT statements — see importing JSON API responses into a SQL database. For a spreadsheet-friendly format instead of JSON, the SQL to CSV tool takes the same input.

Try it on your own statements

Paste your INSERT statements into SQL to JSON to get a JSON array instantly. It runs entirely in your browser, so nothing in your data is uploaded.