Convert JSON to SQL

The output is a run of INSERT statements and nothing else. That is a deliberate limit rather than a missing feature. JSON tells you a value is a number; it does not tell you whether the column is an integer or a decimal with two places, whether it is nullable, which field is the primary key, what the foreign keys are or how long the text can be. Those are the decisions a schema exists to record, and a converter that guessed at them would produce a CREATE TABLE you had to read line by line before trusting — which is more work than writing it.

What that means in practice is that the table already exists before you run the file. Convert once, read the column list off the first statement, write the DDL to match, then run. It is a two-minute loop and it puts the schema decisions where they belong, in a migration you can review, rather than in a generated file that happens to work on the first sample.

There is nowhere else it could come from. A JSON document has no name for itself, so the uploaded file supplies one: orders.json produces INSERT INTO orders, and export-2024.json produces INSERT INTO export_2024, because a hyphen is not legal in an unquoted identifier and a leading digit is not either.

The consequence is that renaming the file before converting is the cheapest way to control the output. Doing it afterwards means a search and replace across every statement in the file, and the replace has to be careful not to hit a value that happens to contain the same word. Thirty seconds in a file browser beats a regular expression over a hundred thousand lines.

A relational table is flat and JSON is not, so a nested object is flattened into the column name: a record with a customer object holding a city produces a column called customer_city. The path is preserved, the dot becomes an underscore because a dot is not legal in an identifier, and nothing is discarded.

One level of nesting produces a table anybody would be happy to declare. Two is usually still reasonable. Beyond that, the generated column list is a signal rather than a result: a JSON structure four levels deep is describing relationships, and the schema that fits it is several tables with keys between them. Loading it into one wide table works and makes every subsequent query harder than it needed to be.

A record with three tags produces tags_0, tags_1 and tags_2. Every value survives and the shape is wrong in a way that gets worse: the next record with five tags widens the table by two more columns, and there is no sensible query over "the second tag".

A relational database has a normal answer to this and it is a second table — one row per tag, with the parent identifier beside it. Getting there from a JSON export means converting twice with the array extracted in between, or loading the wide table as a staging step and normalising with a query. Both are more work than a single conversion and both produce a schema you can actually query. If the target database has a native JSON column type, storing the array in one column is the third option, and it is the right one when the array is carried rather than queried.

JSON records in one export are not obliged to agree. An API omits fields that have no value, so a thousand records can present a dozen different key sets. The conversion reconciles them by collecting the union of every key in the file and writing NULL where a record has nothing.

That uniformity is what makes the output safe to run as a batch: the column list is identical in every statement, so a table that accepts the first row accepts all of them. It also explains a difference people notice — converting ten records for a test can produce fewer columns than converting the whole file, because the sample did not happen to contain the rarer fields. Write the DDL from a conversion of the whole file, never from a sample.

Numbers are written bare, booleans as TRUE and FALSE, a JSON null as NULL, and everything else as a single-quoted string with internal single quotes doubled. Doubling is the SQL standard form and it is what every engine understands, so a surname like O’Brien loads correctly everywhere.

Backslashes are written through untouched, which is correct under the standard and is not how MySQL reads a string by default: there, a backslash begins an escape sequence unless NO_BACKSLASH_ESCAPES is set. Data containing Windows paths, regular expressions or LaTeX will therefore load differently into MySQL than into Postgres. If that describes your export, set the mode for the session before running the file, and check one affected row afterwards rather than assuming.

This is where a JSON source is better than a delimited one. A product code of "007" is a JSON string, so it is written as a quoted SQL string and arrives in the database with its leading zeros. A CSV of the same data has no types at all, and every tool in the chain gets a chance to decide that 007 is the number seven.

The same protection covers long numeric identifiers that were exported as strings. Where they were exported as JSON numbers, though, the usual floating-point ceiling applies — anything past sixteen digits has already lost precision before this conversion sees it, and no amount of care in the writer can put it back. If the export is yours to shape, exporting identifiers as strings is the fix, and it is a fix at the source rather than here.

The output is one statement per record with no transaction wrapper and no multi-row VALUES clause. Running fifty thousand separate statements through a client is slow, because each one is a round trip. Wrapping the whole file in BEGIN and COMMIT is one line at each end and usually the largest single improvement available.

The statements themselves are deliberately plain and portable: no engine-specific quoting, no ON CONFLICT clause, no schema prefix. Adding what your engine wants is a search and replace on INSERT INTO — a schema name, an ON CONFLICT DO NOTHING for a re-runnable load — and starting from the plainest possible form is what makes those edits predictable.

For a seed file, a fixture or a few thousand rows, statements are the convenient shape: they are readable, they can be committed to a repository, and they run anywhere a client can connect. That is the job this page is written for.

Past a certain size the arithmetic changes. Every engine has a bulk path — COPY in Postgres, LOAD DATA in MySQL, an import in a client tool — that reads a delimited file far faster than it executes individual inserts, and for a load of a million rows the difference is minutes against hours. Convert the same export to CSV or TSV in that case, or to NDJSON if the destination is a warehouse rather than a relational database.

The statements are generated by JavaScript in this tab. Nothing is uploaded, there is no account or queue, and the free tier accepts up to 100 MB, with memory as the real ceiling because the whole document is parsed before anything is written.

That is not an incidental benefit for this pair. Whatever is about to be inserted into a database is, by definition, data somebody has decided to keep — customers, orders, transactions, accounts. Passing it through a third-party converter to have quotation marks doubled would be a poor trade, and here there is no trade to make.