Theme

SQL INSERT to JSON

Paste INSERT statements and get one JSON object per row, keyed by column name. Escapes are decoded the way your database does it, and the dump never leaves your browser.

SQL → JSON

Input

Settings

History

Load from URL

Why turn INSERT statements into JSON

Database dumps, seed files and migration scripts store data as INSERT INTO … VALUES (…) statements. That is fine for replaying into a database, but awkward when you want the rows elsewhere: as fixtures for a unit test, a mock API response, input for a script, or simply to read a few records without spinning up MySQL or PostgreSQL. This converter reads the statements directly and returns a JSON array such as [{"id": 1, "name": "Ada"}], which you can then pass to JSON to CSV or the JSON formatter.

What it reads

  • INSERT INTO table (columns) VALUES (…), (…) with any number of rows per statement, plus MySQL REPLACE INTO and INSERT … SET col = value, SQLite INSERT OR REPLACE, and Oracle INSERT ALL … SELECT * FROM dual.
  • An INSERT without a column list takes its column names from a CREATE TABLE for the same table earlier in the input; constraints such as PRIMARY KEY (id) are skipped. Without either, columns are named column_1, column_2 … and a warning says so.
  • Trailing clauses like ON CONFLICT DO NOTHING, ON DUPLICATE KEY UPDATE and RETURNING are ignored.
  • Everything else (SET NAMES, LOCK TABLES, DROP TABLE, T-SQL GO, comments and MySQL /*!…*/ hints) is skipped, and an info note counts what was skipped.

Pick the right dialect

String escaping differs between databases, and guessing wrong silently changes data, so the Dialect option matters. In standard SQL, PostgreSQL, SQLite, SQL Server and Oracle, a quote inside a string is doubled ('O''Brien') and a backslash is an ordinary character. MySQL, MariaDB, BigQuery, Snowflake and Spark also treat a backslash as an escape: 'It\'s', \n for a newline, \0 for NUL. If a MySQL dump is read as standard SQL, \' ends the string early; the error then suggests switching to MySQL. PostgreSQL E'…' strings, U&'…' Unicode strings, $$…$$ dollar quoting, Oracle q'[…]' and T-SQL N'…' are decoded in every dialect that supports them.

How values map to JSON

  • Numbers keep every digit. 9007199254740993 and 1520.50 are written exactly as in the SQL, not rounded through a floating-point double, so 64-bit IDs and money values stay intact.
  • TRUE/FALSE become booleans and NULL becomes null. SQL Server stores booleans as BIT, so its 1 and 0 stay numbers.
  • Typed literals and casts are read through: DATE '2026-01-01', '{"a":1}'::jsonb and CAST('42' AS int) give their string or number. Concatenations such as 'a' || CHAR(10) || 'b' are evaluated.
  • Hex and bit literals (X'CAFE', 0xCAFE, B'0101') become strings like "0xCAFE".
  • Anything that is not a literal, such as NOW(), DEFAULT or price * 2, is kept as its source text in a string, with a warning at its line and column. Nothing is dropped without telling you.

Column types are not carried over: a DATE column gives date strings, and JSON stored as text stays a string. This mirrors JSON to SQL, and converting there and back returns the same rows.

Several tables

A dump usually covers more than one table, and a single JSON array cannot say which row came from where. By default the converter stops at the first INSERT for a second table and reports its position. Turn on Group rows by table name to get an object instead, for example {"customers": [...], "orders": [...]}. Table names are compared case-insensitively, and schema-qualified names such as public.orders keep their schema. To tidy the SQL itself, use the SQL formatter.

Examples

mysqldump extract

With the MySQL dialect, ' and \n are decoded and LOCK/UNLOCK TABLES are skipped. MySQL booleans are TINYINT, so active stays 1 or 0.

Input
LOCK TABLES `users` WRITE;
INSERT INTO `users` (`id`, `name`, `bio`, `active`) VALUES (1,'Ada','Writes \'clean\' code\nand tests',1),(2,'Linus','Kernel \\ git',0);
UNLOCK TABLES;
Output
[
  {
    "id": 1,
    "name": "Ada",
    "bio": "Writes 'clean' code\nand tests",
    "active": 1
  },
  {
    "id": 2,
    "name": "Linus",
    "bio": "Kernel \\ git",
    "active": 0
  }
]
Open this example in the tool

PostgreSQL with casts and big IDs

Integers beyond 2^53 keep every digit, the jsonb cast and typed timestamp are read through, and NOW() is kept as text with a warning.

Input
INSERT INTO public.events (id, kind, payload, created_at) VALUES
  (9007199254740993, 'signup', '{"plan": "pro"}'::jsonb, TIMESTAMP '2026-03-01 09:30:00'),
  (9007199254740994, 'login', NULL, NOW())
ON CONFLICT (id) DO NOTHING;
Output
[
  {
    "id": 9007199254740993,
    "kind": "signup",
    "payload": "{\"plan\": \"pro\"}",
    "created_at": "2026-03-01 09:30:00"
  },
  {
    "id": 9007199254740994,
    "kind": "login",
    "payload": null,
    "created_at": "NOW()"
  }
]
Open this example in the tool

Two tables, grouped

With grouping on, the output is an object with one array per table instead of an error at the second table.

Input
INSERT INTO customers (id, name) VALUES (1, 'Aisha Tan'), (2, 'Ben Okafor');
INSERT INTO orders (id, customer_id, total) VALUES (100, 1, 129.90);
Output
{
  "customers": [
    {
      "id": 1,
      "name": "Aisha Tan"
    },
    {
      "id": 2,
      "name": "Ben Okafor"
    }
  ],
  "orders": [
    {
      "id": 100,
      "customer_id": 1,
      "total": 129.90
    }
  ]
}
Open this example in the tool

Common errors and how to fix them

ErrorCauseFix
This string is never closed — the ' has no matching '
Explained		The dump uses backslash escapes (MySQL style ‘It's’) but the dialect is set to one where backslash is an ordinary character.Set Dialect to MySQL or MariaDB, as the error hint suggests.
This row has 3 values, but the column list has 4 columns (id, name, email, city)A row in VALUES has more or fewer values than the INSERT names columns.Add or remove values in the reported row so each column gets exactly one.
These rows are for table orders, but earlier rows are for customers; one JSON array can only hold one tableThe input contains INSERTs for more than one table.Turn on “Group rows by table name”, or paste one table at a time.
No INSERT … VALUES statements were foundThe input has only SELECT, CREATE or other statements, or INSERT … SELECT, which has no literal rows.Paste the INSERT statements, for example from mysqldump or pg_dump --inserts.

Frequently asked questions

Which dumps work?

Output from mysqldump, MariaDB, pg_dump --inserts or --column-inserts, SQLite .dump, SQL Server “Generate Scripts” and most hand-written seed files. Use the matching dialect for string escapes.

Why are some values strings with a warning?

They are expressions rather than literals, for example NOW() or DEFAULT. Their value is only known inside the database, so the source text is kept so you can decide what to put there.

Are DECIMAL and BIGINT values rounded?

No. Number literals are copied digit for digit into the JSON, so 0.10 stays 0.10 and 64-bit integers stay exact.

Can it convert a multi-gigabyte dump?

It streams through the statements in a background worker, so tens of megabytes are fine. Very large dumps are better split per table first.

Related tools