JSON to SQL

JSON is how most APIs and exports deliver data, but analysis, joins and reports are easier in a SQL database. Getting a JSON array into a table usually means writing a script: deciding column types, quoting identifiers, escaping strings and splitting the inserts into batches. This converter writes that script for PostgreSQL, MySQL, SQLite or SQL Server.

What it reads

Paste an array of objects, the usual shape of an export or a list endpoint. If the array is wrapped, as in {"data": [...]}, the first array inside the object is used; a single object becomes one row. Every key found in any row becomes a column, so rows with extra keys are not lost.

How column types are chosen

Each column’s type is decided from all of its values, then mapped to the dialect you pick. Whole numbers become INTEGER, or BIGINT when any value exceeds the 32-bit range. Decimals become DOUBLE PRECISION in PostgreSQL, DOUBLE in MySQL, REAL in SQLite and FLOAT in SQL Server. Booleans become BOOLEAN, except in SQLite (INTEGER 0/1) and SQL Server (BIT). Strings in ISO 8601 date form become DATE, date-times become TIMESTAMPTZ, DATETIME or DATETIMEOFFSET, and UUIDs become UUID or the nearest equivalent. Everything else is text; in MySQL, short strings get a VARCHAR sized to the longest value, from 16 up to 255.

Nested data

Objects and arrays inside a row are stored as JSON: JSONB in PostgreSQL, JSON in MySQL, text in SQLite and NVARCHAR(MAX) in SQL Server. You can query them with each database’s JSON functions, or normalise them later into separate tables with their own keys.

Keys, nulls and names

A column gets NOT NULL when every row has a value for it. Name a primary key column, usually id, and it is declared PRIMARY KEY. Turn on snake_case to convert keys such as firstName into the conventional first_name. Identifiers are quoted the way each database expects — double quotes, backticks or square brackets — so keys with spaces or reserved words still work.

Escaping and batching

String values are written as SQL literals with every single quote doubled, so O’Neil becomes 'O''Neil'; MySQL also doubles backslashes, and SQL Server literals get the N prefix for Unicode. Rows are grouped into multi-row INSERT statements, 500 per statement by default, which is much faster than one statement per row and stays within statement-size limits.

A worked example

The sample has three users. In PostgreSQL the table comes out as "id" INTEGER PRIMARY KEY, "first_name" TEXT NOT NULL, "email" TEXT, "active" BOOLEAN NOT NULL, "balance" DOUBLE PRECISION NOT NULL, "joined_at" TIMESTAMPTZ NOT NULL and "prefs" JSONB. The email is nullable because one user has none, and the third user’s name is written 'O''Neil'.

When to use a bulk loader

Generated scripts suit thousands or tens of thousands of rows. For millions, export CSV and use the database’s loader — COPY in PostgreSQL, LOAD DATA INFILE in MySQL, .import in SQLite or BULK INSERT in SQL Server — which is far faster and lighter on memory.

Before running it

Read the column table under the output and adjust anything the sample could not reveal: a code that looks numeric but needs leading zeros should be text, and money is safer as NUMERIC(12,2) than as a floating-point type.

Related tools

Format the result with the SQL Formatter, convert spreadsheets with CSV to SQL, inspect the JSON first in the JSON Formatter, or turn CSV into JSON with CSV to JSON.