Fun Data Playground json → sql
Field types Account← Generator

Home JSON to SQL

JSON to SQL converter

Turn a JSON array or an NDJSON log into a table definition and a batched insert. Nested objects become columns. Everything runs in this tab.

Flattening before loading

A relational table has no room for a nested object, so the structure has to go somewhere before the insert. Nested objects flatten to a path, and because a dot is not something a column name should carry into a database, the path is joined with underscores in the SQL: 

{"user": {"name": "Ada", "email": "ada@example.test"}}

CREATE TABLE "records" (
  "user_name" VARCHAR(32),
  "user_email" VARCHAR(48)
);

Arrays are not flattened. {"tags":["a","b"]} becomes one column holding ["a","b"] as text — which is honest, and on PostgreSQL is one ALTER TABLE … TYPE jsonb USING … away from being queryable. Splitting an array into tags_0 and tags_1 would make the table's column count depend on the longest array in the file, and the second file would not fit the first file's schema.

When flattening is the wrong answer

If the nesting is a real one-to-many — orders with line items, posts with comments — flattening produces a wide table with repeated parent values, and you wanted two tables with a foreign key. This converter will not invent that split for you. Convert the arrays separately, or model the tables first and use the database guide.

Column types

JSON already carries types, but a converted column still has to satisfy every row, so types are decided from the values actually present: booleans become BOOLEAN, whole numbers BIGINT, decimals DECIMAL(18,6), ISO dates DATE, ISO timestamps TIMESTAMP, UUID strings UUID, and everything else VARCHAR(n) or TEXT. Nulls are ignored when deciding — a key that is null in some rows still gets a useful type from the rest.

NDJSON and log files

One JSON object per line is the shape of most application logs, BigQuery exports and Elasticsearch bulk data. Paste it as-is: if the whole input is not valid JSON but every line parses on its own, it is read as NDJSON. That makes this the shortest path from a log file to a queryable table, which is usually what you actually wanted when you opened the log.

Missing keys

The column set is the union of every key across every object. A row that lacks a key gets NULL — which is exactly right here, because in JSON an absent key genuinely means "not present" rather than "empty".

Common questions

How are nested objects turned into columns?

They flatten to a path joined with underscores: {"user":{"name":"Ada"}} becomes a column called user_name. Arrays are kept as text in a single column rather than split across numbered columns.

Can it read NDJSON?

Yes. If the input is not valid JSON as a whole but each line parses on its own, it is read as one object per line — the shape of most log files and BigQuery exports.

What happens to a key that is missing from some objects?

It becomes a column, and the rows without it get NULL. In JSON an absent key means "not present", so NULL is the accurate translation.

Which dialects does it target?

PostgreSQL and MySQL, differing in identifier quoting. Both outputs run on SQLite and SQL Server with minor edits.

Should I use this for a one-to-many relationship?

No. If the nesting represents orders with line items, flattening gives you a wide table with repeated parent values when you wanted two tables and a foreign key. Convert the arrays separately instead.

Last updated