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 called user 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.

JSON and SQL, in brief

JSON

Full name	JavaScript Object Notation
Extension	.json
Format type	Nested text data, six value kinds
MIME type	application/json

SQL

Full name	Structured Query Language
Extension	.sql
Format type	Plain text statements, not a data format
MIME type	application/sql

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.

A new table also needs a stable key, and that key should be assigned before arrays are split so the child rows can carry it as their foreign key. The UUID generator supplies batch v4 or time-sortable v7 values when the source records do not already own identifiers. Generating keys cannot decide the relationship for you, but it prevents row position from becoming an accidental identity.

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 like data_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 become NULL rather than empty strings, because a database treats those as different things and only one of them means we do not know.

If the path it found is not the one you wanted, the fastest way to see why is to look at the shape of the document rather than at the conversion. Indenting the response makes the nesting obvious in a glance, and it is also where a paste that will not convert at all gets diagnosed — a trailing comma or a single-quoted key stops this page before the naming ever begins.

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.

Worth knowing if the JSON did not start as JSON: YAML makes the opposite choice. Its parsers guess aggressively at what a bare value is meant to be, and they do it before you get a chance to see the result, which is how a two-letter country code arrives in your database as a boolean. Converting through YAML to JSON first shows you every value that was guessed at, so the column types you build here rest on something you checked.

The same rule bites harder on the way back out, and for a reason specific to the format. A JSON number is an IEEE double, so an integer past about fifteen digits cannot be represented at all and every parser rounds it without comment — which is why SQL to JSON writes every value as a string rather than handing modern nineteen-digit identifiers to a type that cannot hold them.

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.