Convert JSON to SQL

Converting JSON to SQL is a naming problem before it is anything else

Hierarchies do not fit in tables, so something has to invent names. Given{ "user": { "address": { "city": "Austin" } } } the obvious column name is user.address.city, and that name is a small trap. A dot in a SQL identifier already means something — it separates schema from table and table from column — so a column genuinely containing one has to be quoted every time anybody mentions it. Forget the quotes once and the engine reads it as a table calleduser with a column called address, which fails in a way that does not sound like a naming problem at all.

The names here use underscores instead. user_address_city can be typed, joined and aliased by anyone who later inherits the table without knowing how it was made. The CSV converter on this site defaults to dots for the same data, and that is not an inconsistency: a spreadsheet has no identifier rules and dots read more clearly there. The target decides.

Arrays get an answer rather than a setting

A repeating list inside a record — tags, order lines, comments — is the part of the shape that genuinely has no flat equivalent. There are three ways to force one, and in a database two of them are actively harmful.

One row per element multiplies your rows: five tags on a record become five copies of that record, and any primary key you were planning is now wrong. Numbered columns give you tags_1 through tags_40, a schema built around whatever the longest list happened to be on the day you converted, and one that breaks when somebody adds a forty-first. So arrays are joined into a single cell here, and the page says the thing a setting cannot: a repeating list belongs in a second table with a foreign key back to this one. That is more work than a dropdown and it is the correct answer.

Where the records actually are

Almost nothing real arrives as a bare array. An API response looks like{ "status": "ok", "page": 2, "data": [ … ] }, and flattening the outer object produces exactly one row, a thousand columns wide, with names likedata_0_user_name. It is the most common way this conversion goes wrong and it produces output that looks superficially plausible.

The records array is located and the path is printed above the output, so the decision is visible instead of implied. Fields missing from some records becomeNULL rather than empty strings, because a database treats those as different things and only one of them means we do not know.

Types, and the values JSON is careless with

JSON does have types, which sounds like it should make this easier than converting a spreadsheet. It helps less than you would hope, because the values that matter most are usually strings that look like numbers: an ID from another system, a phone number, a product code with a leading zero. Every column is shown with the type it was given before you run anything, and each one can be changed on its own.

The inference stays timid for the same reason it does everywhere on this site. A value with a leading zero is never a number. Notations that mean different things to different engines stay quoted. When in doubt, text — because text that should have been a number is an annoyance, and a number that should have been text is data loss.

Nothing is uploaded, which matters more for JSON than for most formats

The parser, the flattener and the statement builder are JavaScript in this tab. Pasted JSON is the highest-risk material on this site by a clear margin: it is usually a raw API response, and those carry bearer tokens, internal identifiers, email addresses and whatever else the endpoint returns alongside the three fields somebody actually wanted. Pasting that into a server-side converter publishes all of it to someone you have never met.

Large documents are not refused. Work that grows with the file runs off the main thread, so a big response does not freeze the page while it is read.