Why does '01234' stay a string in the JSON?

Only unquoted values get a type. '123', '01234', 'true' and 'NULL' in quotes are strings, so a ZIP code keeps its leading zero, while unquoted 123, true and NULL become 123, true and null.

Can I convert a query result instead of INSERT statements?

Switch to Query Result and paste the table from psql, the MySQL client with its +---+ borders, a Markdown table or tab-separated text. Cells are text except canonical numbers, true/false and NULL, so a psql column holding 01234 and 98765 gives "01234" and 98765.

What SQL is not supported?

Anything that needs a database to work out: function calls such as NOW(), DEFAULT, sub-selects, INSERT ... SELECT, X'..' literals and casts such as ::int. Each is reported with its statement, row and value, never guessed. Other statements, such as CREATE TABLE, are skipped and counted.

What if my INSERT has no column list?

The keys become column1, column2 and so on, with a note: INSERT INTO t VALUES (1, 'x') gives [{"column1":1,"column2":"x"}].

Does it read MySQL dumps with backslash escapes?

Turn on “Backslash is an escape”. Then 'it\'s' reads as it's. With it off, as standard SQL reads strings, that string is never closed and the error says to turn the option on. MySQL # comments are skipped either way.

What happens with INSERT statements into several tables?

The output is an object with one array per table: INSERT INTO a (x) VALUES (1); INSERT INTO b (y) VALUES (2); gives {"a":[{"x":1}],"b":[{"y":2}]}.

SQL to JSON

Turn SQL INSERT statements, or a result table pasted from psql, MySQL or Markdown, into JSON. Quoted values stay strings; numbers, true and NULL get types. How it works

JSON indent

Backslash is an escape in '...' (MySQL)

SQL Input

Up to 2,000,000 characters; the SQL is read, never run, and nothing is uploaded.

JSON Output

[
  {
    "id": 1,
    "name": "Widget",
    "price": 9.99,
    "in_stock": true
  },
  {
    "id": 2,
    "name": "Gadget",
    "price": null,
    "in_stock": false
  },
  {
    "id": 3,
    "name": "Doohickey",
    "price": 14.95,
    "in_stock": true
  }
]

3 records parsed across 4 columns

Reverse: Convert JSON back to SQL

What is read

Values in quotes stay text ('123' and '01234' are strings). Only unquoted numbers, true, false and NULL get a type.
Names can be bare, quoted or schema-qualified (public.users). Several INSERT statements are read with their own column lists.
Not supported, and reported by name: function calls such as NOW(), DEFAULT, sub-selects, INSERT ... SELECT, X'..' literals, casts. A row with the wrong number of values is an error, not skipped.
Query Result reads Markdown, psql, MySQL box and tab-separated tables; cells are text except canonical numbers, true/false and NULL.

About the SQL to JSON

INSERT INTO products (id, name, price, in_stock) VALUES (1, 'Widget', 9.99, true), followed by two more rows, becomes a JSON array of 3 objects, the first {"id": 1, "name": "Widget", "price": 9.99, "in_stock": true}. The converter reads INSERT statements, or a result table pasted from psql, the MySQL client or Markdown. The SQL is read, never run.

SQL values and the JSON they become

SQL value	JSON value
123	123
'123'	"123"
'01234'	"01234"
9.990	9.99
12345678901234567890	12345678901234567890
true	true
'true'	"true"
NULL	null
'NULL'	"NULL"
''	""
'It''s'	"It's"
N'café'	"café"

Only unquoted values get a type. An unquoted number keeps every digit, even past what a JavaScript number can hold.

SQL the converter refuses, and its message

SQL	Message
INSERT INTO t (a, b) VALUES (1);	Statement 1 (INSERT INTO t), row 1 has 1 value but the column list names 2.
INSERT INTO t (a) VALUES (NOW());	Statement 1 (INSERT INTO t), row 1, value 1: NOW(...) is a function call, which cannot be evaluated here. Replace it with a literal value.
INSERT INTO t (a) VALUES (DEFAULT);	Statement 1 (INSERT INTO t), row 1, value 1: DEFAULT is not a literal value (only quoted text, numbers, true, false and NULL are supported). Replace it with a literal.
INSERT INTO t (a) SELECT 1;	Statement 1 (INSERT INTO t): INSERT ... SELECT is not supported; only INSERT ... VALUES.

Frequently asked questions

Why does '01234' stay a string in the JSON?: Only unquoted values get a type. '123', '01234', 'true' and 'NULL' in quotes are strings, so a ZIP code keeps its leading zero, while unquoted 123, true and NULL become 123, true and null.
Can I convert a query result instead of INSERT statements?: Switch to Query Result and paste the table from psql, the MySQL client with its +---+ borders, a Markdown table or tab-separated text. Cells are text except canonical numbers, true/false and NULL, so a psql column holding 01234 and 98765 gives "01234" and 98765.
What SQL is not supported?: Anything that needs a database to work out: function calls such as NOW(), DEFAULT, sub-selects, INSERT ... SELECT, X'..' literals and casts such as ::int. Each is reported with its statement, row and value, never guessed. Other statements, such as CREATE TABLE, are skipped and counted.
What if my INSERT has no column list?: The keys become column1, column2 and so on, with a note: INSERT INTO t VALUES (1, 'x') gives [{"column1":1,"column2":"x"}].
Does it read MySQL dumps with backslash escapes?: Turn on “Backslash is an escape”. Then 'it\'s' reads as it's. With it off, as standard SQL reads strings, that string is never closed and the error says to turn the option on. MySQL # comments are skipped either way.
What happens with INSERT statements into several tables?: The output is an object with one array per table: INSERT INTO a (x) VALUES (1); INSERT INTO b (y) VALUES (2); gives {"a":[{"x":1}],"b":[{"y":2}]}.

Turning seed files and table dumps into JSON

Paste a seed file or a table dump to get JSON fixtures for tests, mock an API response from rows you already have, or move a few rows from a psql or MySQL session into a JavaScript app. JSON indent gives 2 spaces, 4 spaces or minified output on one line, to match your project's style. Download saves a .json file named after the table.

How INSERT statements are read

Each statement is tokenised and only its literal values are read; nothing is executed.

INSERT INTO table (col1, col2) VALUES (v1, v2), (v3, v4);

Each row in VALUES becomes one object, keyed by the column list, or by column1, column2 and so on when there is none.
Quoted text stays a string, so '01234' keeps its leading zero. Unquoted numbers keep every digit, and unquoted true, false and NULL become true, false and null.
Table names may be schema-qualified, such as public.users. Several INSERT statements are read with their own column lists; two tables give an object with one array each.
Comments starting with -- or # and block comments are skipped. ON CONFLICT, ON DUPLICATE KEY UPDATE and RETURNING are skipped with a note.

Example: the multi-row sample inserts Widget, Gadget and Doohickey and gives 3 records across 4 columns; Gadget's NULL price becomes null.

A row with the wrong number of values is an error that names the statement and the row. Limit: 2,000,000 characters.

Related Tools

View all tools

Conversion

CSV to JSON

Convert CSV to JSON: the first row becomes the keys, quoted commas and line breaks are kept, and numbers and booleans are detected only if you ask.

Conversion

JSON to CSV

Turn JSON into CSV: an array of objects, one object or a list of values. Flatten nested objects, expand arrays into rows, then copy or download.

Dev

XML to JSON Converter

Convert XML to JSON and JSON back to XML: attributes go under @attributes, repeated elements become arrays, and an XML error shows its line and column.

Dev

YAML to JSON Converter

Convert YAML to JSON with anchors and merge keys resolved, YAML 1.2 or 1.1 typing for yes/no, and a warning for numbers JSON cannot hold.

Dev

JSON to YAML

Convert JSON to YAML in block or flow style. Text such as yes or 017 is quoted so it stays a string, and a JSON error shows its line and column.

Dev

JSON to SQL

Generate SQL INSERT statements, and CREATE TABLE if you want it, from JSON for PostgreSQL, MySQL, SQLite or SQL Server, with types inferred from every row.