Convert SQL INSERT to JSON!

Input MySQL or MariaDB INSERT statements to convert them into a JSON array!
SQL
Result
 

Convert SQL INSERT to JSON!

Reads MySQL/MariaDB INSERT statements and turns each row into an object in a JSON array. If a CREATE TABLE statement is included in the same input, its column names and types are used, so TINYINT(1) values become true/false and JSON columns are expanded into objects.

Made for moving part of a mysqldump file, a migration seed, or INSERT statements attached to a bug report into test fixtures or API mocks.

How to use

  1. Paste INSERT statements into the SQL box. For the INSERT INTO t VALUES ... form without a column list, include that table's CREATE TABLE statement too.
  2. Dump-file comments (--, #, /* */) and other statements like SET or LOCK TABLES can stay; they are skipped.
  3. Copy the JSON under Result. If no INSERT is found, you get No INSERT statement found.

Example

Input (SQL)
CREATE TABLE `products` (
  `id` INT NOT NULL,
  `name` VARCHAR(100),
  `on_sale` TINYINT(1),
  `attrs` JSON
);
INSERT INTO `products` VALUES
  (1, 'Desk lamp', 1, '{"color":"white"}'),
  (2, 'Kid''s chair', 0, NULL);
Output (JSON)
[
  {
    "id": 1,
    "name": "Desk lamp",
    "on_sale": true,
    "attrs": {
      "color": "white"
    }
  },
  {
    "id": 2,
    "name": "Kid's chair",
    "on_sale": false,
    "attrs": null
  }
]

The INSERT has no column list, so the column order comes from the CREATE TABLE before it. 1/0 in the TINYINT(1) column on_sale became booleans, the string in the JSON column attrs became an object, and the quote escaped as '' was read correctly.

Supported features

  • Reads INSERT, INSERT IGNORE, REPLACE INTO, and multi-row VALUES (...), (...).
  • Values: numbers → numbers, 'text' and "text" → strings, NULL → null, TRUE/FALSE → booleans. Function calls like NOW() are kept as the raw text "NOW()".
  • MySQL string escapes ('', \', \n, \t and so on) are decoded.
  • With a CREATE TABLE, its column order is used, 0/1 in TINYINT(1), BOOL or BOOLEAN columns become booleans, and JSON column strings become objects.
  • With neither a column list nor a CREATE TABLE, keys are named column1, column2 and so on.
  • One table gives an array; INSERTs for several tables give an object { "table": [...] }. db.table and [dbo].[table] are reduced to the table name.

Use cases

Dump excerpts as test fixtures

Cut the few rows needed to reproduce a bug out of a production dump, convert them, and use the JSON as unit-test input or Storybook mock data.

Reviewing seed SQL

In a long single-line INSERT ... VALUES, counting which value lands in which column is error-prone. As JSON, every value sits next to its column name, so ordering mistakes stand out.

Notes and limitations

  • INSERT ... SELECT, UPDATE and DELETE are not read.
  • Numbers become JavaScript numbers, so trailing zeros in DECIMAL (12.10 → 12.1) and BIGINT values beyond 2^53 lose precision.
  • Dates and times stay as the strings written in the SQL.
  • SQL Server's N'...' Unicode prefix and PostgreSQL's E'...' escape strings are read as ordinary strings, so N'서울' becomes "서울".
  • Without a CREATE TABLE, 0/1 in TINYINT(1) columns stay numbers.

FAQ

Can I paste a whole mysqldump file?

Yes. Only INSERT and CREATE TABLE statements are read and everything else is skipped. Very large files can slow the browser, though, so pasting just the tables you need is better.

Does it handle PostgreSQL INSERT statements?

Yes for the usual dump format. E'...' strings are understood, and :: casts are read and applied: '{"a":1}'::jsonb becomes an object, '5'::int a number, 't'::boolean true. Other casts such as ::timestamp or ::uuid just keep the string value.

How do I turn the result back into INSERT statements?

Feed it to the JSON to MySQL tool, which infers types and regenerates CREATE TABLE and INSERT. It won't necessarily match the original table definition, so review the types.