JSON to SQL Converter
Get an API response into a table you can query. Nested fields become dotted columns, JSON nulls become SQL NULLs, and the types come from reading whole columns.
To convert JSON to SQL, paste your array of objects above and choose a dialect. Nested objects flatten into dotted columns, then you get a CREATE TABLE whose types are inferred from all the values in each column, followed by INSERT statements batched at a size you set. A JSON null becomes a SQL NULL rather than an empty string.
Want to drop fields before you load? Open the app
The response exists and the question needs SQL
The trigger is nearly always a question that JSON cannot answer conveniently. "How many of these are in each status?" "Which customers appear more than twice?" "What is the total by city?" Those are three lines of SQL and a scripting exercise in anything else.
The other case is seeding. You have a realistic API response and you want a local table with the same shape to develop against, without standing up the whole upstream service.
Both want a CREATE TABLE that is right first time, because the alternative is loading everything as text and casting in every query afterwards.
Worked example, in Postgres
The order response:
[
{
"order_id": "ORD-00001",
"placed_at": "2024-05-13T01:17:00Z",
"status": "shipped",
"customer": { "id": 4188, "name": "Katherine Johnson", "city": "Osaka" },
"items": 8,
"total": 2151.83,
"gift": false
},
{
"order_id": "ORD-00002",
"placed_at": "2024-08-10T11:39:00Z",
"status": "delivered",
"customer": { "id": 3737, "name": "Ada Lovelace", "city": "Berlin" },
"items": 1,
"total": 1783.46,
"gift": true
}
]And the script:
CREATE TABLE "orders" (
"order_id" TEXT,
"placed_at" TEXT,
"status" TEXT,
"customer.id" NUMERIC,
"customer.name" TEXT,
"customer.city" TEXT,
"items" NUMERIC,
"total" DOUBLE PRECISION,
"gift" BOOLEAN
);
INSERT INTO "orders" ("order_id", "placed_at", "status", "customer.id", "customer.name", "customer.city", "items", "total", "gift") VALUES
('ORD-00001', '2024-05-13T01:17:00Z', 'shipped', 4188, 'Katherine Johnson', 'Osaka', 8, 2151.83, FALSE),
('ORD-00002', '2024-08-10T11:39:00Z', 'delivered', 3737, 'Ada Lovelace', 'Berlin', 1, 1783.46, TRUE);The dotted column names are quoted, which is why the identifier quoting has to be right for the dialect: "customer.id" without quotes would be read as a table qualifier and the statement would fail. gift is a real BOOLEAN because the source had real booleans, and total is a floating type because it has fractions in it while items does not.
What flattening does to a nested object
A nested object becomes dotted columns. "customer": { "name": "Ada" } becomes a column called customer.name. That is the one convention every tool in this space agrees on, and it is reversible enough that a person reading the header knows exactly where the value came from.
Nesting deeper than four levels is kept as JSON text in a single cell instead of exploding into columns nobody will use. Four is deep enough for every API response worth tabulating and shallow enough that the header row stays readable.
An array of objects is also kept as JSON text in one cell, rather than being spread across extra rows. Spreading it would silently change the row count, so a file of 200 orders would come back as 4,000 rows and the totals would all be wrong. If that is the shape you want, it is a join rather than a flatten, and the full editor does it explicitly.
Every one of those decisions is reported under the result, with a count. Nothing about the structure changes quietly.
Nulls, and the difference SQL cares about
A JSON null becomes a SQL NULL. A JSON empty string becomes ''. Those are different facts and the whole point of a nullable column is to keep them apart.
A key that is missing from one object but present in others also becomes NULL for that row, which is the honest reading: the record did not carry the field. The column exists because other records did.
A competing converter on this term writes '' for every absent value in every column type, which turns "we do not know" into "we know it is nothing" across the whole load and cannot be undone afterwards.
Columns from the union of keys
Not every object in a real response has the same keys. The columns are the union of all of them, in the order they were first seen, so the first record's shape drives the layout and later records contribute anything extra at the end.
That means no field is ever dropped because it was missing from the first record, which is the failure mode of anything that reads only the first object to build a schema. A field that appears in one row out of a thousand still gets a column and 999 NULLs.
Column types are still decided from every value, so a field that is a number in most records and a string in one becomes TEXT. That is conservative and it is what keeps the load from failing on row 700.
Frequently Asked Questions
How are nested objects turned into columns?
With dot notation, so a nested customer becomes customer.id, customer.name and customer.city. Those names are quoted in the output, which is why the dialect matters: unquoted, customer.id would be read as a table qualifier and the statement would fail.
Does a JSON null become NULL?
Yes, and an empty string stays an empty string. Those are different facts and keeping them apart is the entire purpose of a nullable column. A competing converter writes an empty string for both, which cannot be undone after the load.
What if some objects are missing a key?
The columns are the union of every key across every object, so nothing is dropped because it was absent from the first record. A record that lacks a field gets NULL in that column, which is the honest reading.
Which dialects are supported?
Postgres, MySQL, SQLite and SQL Server. The identifier quote, the boolean literal, the text type and the exact-number type all change with the choice, and getting any of them wrong fails the script on its first statement.
What happens to an array inside a record?
It is written as JSON text into a single text column. Spreading it across rows would change the row count, and most databases can query JSON text in a column anyway if you need to go further.
Why is my timestamp column TEXT?
Because a converter cannot guarantee every value parses the same way, and a TIMESTAMP column that rejects one row fails the whole statement. ISO 8601 strings load into a timestamp column cleanly, so changing the type in the CREATE TABLE is safe if you know the data.
Make the response queryable
Flattened columns, real types, real NULLs, batched INSERTs in four dialects.
Back to the converter