How are SQL column types chosen from JSON?

From every row, not just the first. A column holding 1 in one row and "a" in another is TEXT, and its numbers are written as text. Whole numbers up to 2,147,483,647 are INTEGER (INT in MySQL) and larger ones BIGINT. A column with 1 in one row and 2.5 in another takes the decimal type (DOUBLE PRECISION in PostgreSQL). true in one row and 1 in another is also TEXT, so true is written as 'true'. To use another type, pick it in the column table: values are still written as the JSON holds them, except that a text type such as TEXT or VARCHAR(255) quotes every value, so id 1 is written '1'.

How do I skip rows that already exist?

Tick “Skip rows that already exist”: PostgreSQL gets ON CONFLICT DO NOTHING, MySQL INSERT IGNORE and SQLite INSERT OR IGNORE. They skip only rows that break a unique constraint, such as the primary key column you can name for CREATE TABLE. In MySQL, IGNORE also turns data errors into warnings. SQL Server has no INSERT form that skips rows, so the box is off for it: create a unique index with IGNORE_DUP_KEY = ON, which Microsoft documents as leaving out only the duplicate rows with a warning, or write a MERGE on your own key.

What happens to nested JSON objects?

By default they are flattened: {"address": {"city": "London"}} becomes the column address_city. You can store them as JSON text instead (JSONB in PostgreSQL, JSON in MySQL, TEXT in SQLite) or skip them. Arrays are stored as JSON text unless you skip nested values.

How many rows go in one INSERT statement?

100 by default; you can pick 1, 50, 100, 500 or 1000. At the default, 250 rows give 3 INSERT statements. 1000 is also the most SQL Server accepts in one VALUES list; Microsoft documents error 10738 above it.

Why are backslashes doubled in the MySQL output?

MySQL reads a backslash in a string as an escape by default, so O'Brien \ x is written 'O''Brien \\ x'. If your server runs with NO_BACKSLASH_ESCAPES, change each doubled backslash back to one. PostgreSQL and SQLite keep the backslash as it is.

Yes, in this browser’s local storage, with the settings, so it is there on your next visit. Text over 100,000 characters is not saved. Clear Saved Values removes what is saved.

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. How it works

JSON Input

Up to 2,000,000 characters. Your JSON and these settings are saved in this browser (local storage) so they are here next visit; nothing is uploaded. Text longer than 100,000 characters is not saved. Use Clear Saved Values to remove what is saved.

SQL Output

INSERT INTO "users" ("id", "name", "active", "address_city") VALUES
  (1, 'Ada', TRUE, 'London'),
  (2, 'Alan', FALSE, 'Wilmslow');

Table Name

Dialect

Batch Size

Primary key column (optional)Used when Include CREATE TABLE is ticked.

Nested objects

Flatten (a_b columns)Store as JSON textSkip nested values

Output

Include CREATE TABLE

Skip rows that already exist (ON CONFLICT DO NOTHING)

Columns in table users

2 rows, 4 columns, 1 INSERT statement. Types are inferred from every row; pick another type from a column's list to override it.

Column	Type	NULLs
id		0
name		0
active		0
address_city		0

About the JSON to SQL

The users sample on this page becomes INSERT INTO "users" ("id", "name", "active", "address_city") VALUES with the rows (1, 'Ada', TRUE, 'London') and (2, 'Alan', FALSE, 'Wilmslow') for PostgreSQL. The converter writes INSERT statements, and CREATE TABLE when you tick it, for PostgreSQL, MySQL, SQLite or SQL Server, with column types inferred from every row.

Column types inferred in each dialect

JSON values in the column	PostgreSQL	MySQL	SQLite	SQL Server
true and false	BOOLEAN	TINYINT(1)	INTEGER	BIT
Whole numbers up to 2,147,483,647	INTEGER	INT	INTEGER	INT
A whole number above that, such as 3000000000	BIGINT	BIGINT	INTEGER	BIGINT
A whole number of about 9.22 × 10¹⁸ or more, such as 1e20	NUMERIC	DECIMAL(65,0)	NUMERIC	FLOAT
Decimals such as 9.99	DOUBLE PRECISION	DOUBLE	REAL	FLOAT
Text	TEXT	TEXT	TEXT	NVARCHAR(MAX)
Mixed kinds, such as numbers and text	TEXT	TEXT	TEXT	NVARCHAR(MAX)
Objects stored as JSON text, and arrays	JSONB	JSON	TEXT	NVARCHAR(MAX)

Types come from every row; null values are ignored. A type picked in the column table replaces the inferred one. Every column is nullable except a primary key you name, and no other keys or constraints are written. SQL Server FLOAT is the 53-bit floating-point type, so a whole number beyond the BIGINT range keeps about 15 significant digits there.

Quoting and skipping existing rows in each dialect

Dialect	The text O'Brien \ x is written	Skip rows that already exist
PostgreSQL	'O''Brien \ x'	INSERT INTO ... ON CONFLICT DO NOTHING
MySQL	'O''Brien \\ x'	INSERT IGNORE INTO
SQLite	'O''Brien \ x'	INSERT OR IGNORE INTO
SQL Server	N'O''Brien \ x'	Not available: the box is turned off

Frequently asked questions

How are SQL column types chosen from JSON?: From every row, not just the first. A column holding 1 in one row and "a" in another is TEXT, and its numbers are written as text. Whole numbers up to 2,147,483,647 are INTEGER (INT in MySQL) and larger ones BIGINT. A column with 1 in one row and 2.5 in another takes the decimal type (DOUBLE PRECISION in PostgreSQL). true in one row and 1 in another is also TEXT, so true is written as 'true'. To use another type, pick it in the column table: values are still written as the JSON holds them, except that a text type such as TEXT or VARCHAR(255) quotes every value, so id 1 is written '1'.
How do I skip rows that already exist?: Tick “Skip rows that already exist”: PostgreSQL gets ON CONFLICT DO NOTHING, MySQL INSERT IGNORE and SQLite INSERT OR IGNORE. They skip only rows that break a unique constraint, such as the primary key column you can name for CREATE TABLE. In MySQL, IGNORE also turns data errors into warnings. SQL Server has no INSERT form that skips rows, so the box is off for it: create a unique index with IGNORE_DUP_KEY = ON, which Microsoft documents as leaving out only the duplicate rows with a warning, or write a MERGE on your own key.
What happens to nested JSON objects?: By default they are flattened: {"address": {"city": "London"}} becomes the column address_city. You can store them as JSON text instead (JSONB in PostgreSQL, JSON in MySQL, TEXT in SQLite) or skip them. Arrays are stored as JSON text unless you skip nested values.
How many rows go in one INSERT statement?: 100 by default; you can pick 1, 50, 100, 500 or 1000. At the default, 250 rows give 3 INSERT statements. 1000 is also the most SQL Server accepts in one VALUES list; Microsoft documents error 10738 above it.
Why are backslashes doubled in the MySQL output?: MySQL reads a backslash in a string as an escape by default, so O'Brien \ x is written 'O''Brien \\ x'. If your server runs with NO_BACKSLASH_ESCAPES, change each doubled backslash back to one. PostgreSQL and SQLite keep the backslash as it is.
Is my JSON saved?: Yes, in this browser’s local storage, with the settings, so it is there on your next visit. Text over 100,000 characters is not saved. Clear Saved Values removes what is saved.

Important limitations

Up to 2,000,000 characters, 1,000 columns and 100 levels of nesting. At most one primary key column, written NOT NULL PRIMARY KEY in CREATE TABLE, and no other indexes or constraints. A text key becomes VARCHAR(255) in MySQL, which cannot key a TEXT column, and NVARCHAR(450) in SQL Server, where a primary key is limited to 900 bytes; a missing or repeated key value is refused with its row numbers. Names are always quoted (square brackets in SQL Server, with a ] in a name doubled), so they keep their case and may be reserved words. SQL Server text is written as N'...' Unicode literals.

Sources

Last reviewed: 2026-10-23

Loading JSON into a database

Load a JSON fixture into a development database, put an API export into a database to query it, or get a first CREATE TABLE for data whose shape you are still exploring. When the JSON is an object with one key holding an array, that key is the table name, so {"users": [...]} inserts into users. A bare array goes into the table typed in Table Name, or table_name.

Paste the JSON, or open or drop a .json file, which is read in the browser. Download .sql saves the statements in a file named after the table, such as users.sql.

How rows, types and quoting are written

The columns are every key seen in any row; a key missing from a row is NULL.

Text goes in single quotes with ' doubled; SQL Server writes it N'...'. MySQL also gets each backslash doubled and NUL escaped; the other three cannot take NUL in text, so such a value is refused.
Names are always quoted: double quotes in PostgreSQL and SQLite, backticks in MySQL, square brackets in SQL Server.
Types are inferred from every row: booleans, INTEGER up to 2,147,483,647 and BIGINT above it, a floating-point type for decimals, TEXT for text and for mixed columns. SQL Server gets BIT, INT, BIGINT, FLOAT and NVARCHAR(MAX).
Nested objects become a_b columns, JSON text, or are skipped. Arrays are stored as JSON text.
Each column's type can be changed in the column table. Values are still written as the JSON holds them, except that a text type quotes them all.
Primary key column (with CREATE TABLE) writes "id" INTEGER NOT NULL PRIMARY KEY. A text key becomes VARCHAR(255) in MySQL and NVARCHAR(450) in SQL Server; a missing or repeated key value is refused.

Example: the two-row users sample gives the PostgreSQL rows (1, 'Ada', TRUE, 'London') and (2, 'Alan', FALSE, 'Wilmslow') under the columns id, name, active and address_city. MySQL, SQLite and SQL Server write the booleans as 1 and 0.

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

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.