How are column types inferred from JSON?

Every value in a column votes: whole numbers give INTEGER (or BIGINT past 32 bits), any decimal makes it DOUBLE PRECISION, true/false give BOOLEAN, ISO-dated strings give TIMESTAMP, and any conflict — or any real string — settles on TEXT. All rows are scanned, so a late float cannot corrupt an integer column.

What changes between the SQL dialects?

Identifier quoting (double quotes, `backticks`, [brackets]), type spellings (BOOLEAN vs TINYINT(1) vs BIT, DOUBLE PRECISION vs DOUBLE vs FLOAT vs REAL), boolean literals (TRUE vs 1), and the N'' prefix SQL Server wants on Unicode text.

How are objects with different keys handled?

Columns are the union of keys across all rows, in first-seen order; rows missing a key insert NULL there. That matches how heterogeneous API data usually needs to land in a table.

What happens to nested objects and arrays?

They serialise to their JSON text and store in a TEXT column — queryable later with the database's own JSON functions, castable to jsonb in PostgreSQL. Flatten the structure first if you want them as real columns.

Is the generated script production-ready?

It is import-ready: syntactically safe quoting and sane types. Production tables still deserve a primary key, NOT NULL constraints and indexes, which no inference can guess from data alone — review the type list, then harden.

JSON to SQL

Turn a JSON array into a CREATE TABLE with inferred column types and batched INSERT statements — for PostgreSQL, MySQL, SQLite or SQL Server.

JSON to SQL runs entirely in your browser. Your data is converted on your device and never uploaded.

Open the CSV to SQL converter

About JSON to SQL

JSON to SQL takes an array of objects — an API response, an export, a fixture file — and produces the two statements that get it into a database: a CREATE TABLE whose column types are inferred from every value in each column, and INSERT statements batched into multi-row VALUES lists. Type inference is done properly: integers that exceed 32 bits become BIGINT, ISO-formatted strings become TIMESTAMP, mixed columns degrade safely to TEXT, and each canonical type is rendered in the target dialect's spelling — BOOLEAN arrives as TINYINT(1) in MySQL and BIT in SQL Server. The details that break hand-written scripts are exactly the point: identifiers are quoted per dialect so a column named order never collides with the keyword, single quotes in values double correctly so O'Brien survives, missing keys become NULL, and nested objects serialise as JSON text.

Features

CREATE TABLE with types inferred from all rows, not just the first
Multi-row INSERTs with a configurable batch size
PostgreSQL, MySQL, SQLite and SQL Server dialects
Identifier quoting per dialect — reserved words are safe
Value escaping: quotes doubled, NULLs explicit, booleans per dialect
INTEGER/BIGINT/DOUBLE/BOOLEAN/TIMESTAMP/TEXT inference
Missing keys become NULL; nested values become JSON text
Everything converted on your device

How to use the JSON to SQL

Paste a JSON array of objects (or one object)
Name the table and pick the dialect
Adjust the rows-per-INSERT batch size if needed
Copy the SQL — types are listed for review first

Example

Input

[{"id":1,"name":"O'Brien","active":true}]

Output

CREATE TABLE "users" ("id" INTEGER, "name" TEXT, "active" BOOLEAN);
INSERT INTO "users" ("id", "name", "active") VALUES (1, 'O''Brien', TRUE);

The doubled quote in O''Brien is what keeps the script valid.

Common errors & troubleshooting

A numeric-looking column came out as TEXT. — Some value in that column is a string — "42" with quotes, an empty string, or an N/A. Inference scans every row and degrades to TEXT on any mismatch; clean the outliers and reconvert.
The script fails on a column named order or user. — It should not — identifiers are quoted for the chosen dialect precisely for this. If it fails, check the dialect matches the database: backticks are MySQL, brackets are SQL Server, double quotes the rest.
Dates imported as plain text. — Only ISO 8601 shapes (2024-01-15, with optional time) infer as TIMESTAMP. Localised formats like 15/01/2024 are ambiguous and stay TEXT deliberately — normalise them to ISO first.
A huge array produced one gigantic INSERT. — Lower the batch size — 100–500 rows per statement is comfortable for most servers, and some tooling dislikes multi-megabyte statements. Batches also make partial-failure recovery saner.

Frequently asked questions

How are column types inferred from JSON?: Every value in a column votes: whole numbers give INTEGER (or BIGINT past 32 bits), any decimal makes it DOUBLE PRECISION, true/false give BOOLEAN, ISO-dated strings give TIMESTAMP, and any conflict — or any real string — settles on TEXT. All rows are scanned, so a late float cannot corrupt an integer column.
What changes between the SQL dialects?: Identifier quoting (double quotes, `backticks`, [brackets]), type spellings (BOOLEAN vs TINYINT(1) vs BIT, DOUBLE PRECISION vs DOUBLE vs FLOAT vs REAL), boolean literals (TRUE vs 1), and the N'' prefix SQL Server wants on Unicode text.
How are objects with different keys handled?: Columns are the union of keys across all rows, in first-seen order; rows missing a key insert NULL there. That matches how heterogeneous API data usually needs to land in a table.
What happens to nested objects and arrays?: They serialise to their JSON text and store in a TEXT column — queryable later with the database's own JSON functions, castable to jsonb in PostgreSQL. Flatten the structure first if you want them as real columns.
Is the generated script production-ready?: It is import-ready: syntactically safe quoting and sane types. Production tables still deserve a primary key, NOT NULL constraints and indexes, which no inference can guess from data alone — review the type list, then harden.

Related tools

CSV to SQL — Convert CSV to SQL INSERT and CREATE TABLE statements for MySQL, PostgreSQL and SQLite.
JSON to CSV — Convert an array of flat JSON objects to CSV.
JSON Formatter — Beautify, minify and validate JSON with error locations.
JSON to TypeScript — Generate TypeScript interfaces from a JSON sample.
JSON Flatten / Unflatten — Flatten nested JSON to dot-notation keys, or unflatten it back.
Sort JSON Keys — Recursively sort JSON object keys alphabetically, with pretty-print and reverse options.