RividTech

JSON to SQL

Generate CREATE TABLE and INSERT statements from a JSON array of objects.

Drop a file or click to browse

or paste data below

Need the reverse conversion? CSV to SQL

How JSON to SQL works

From objects to rows

The input must be a JSON array of objects, or a single object, which is treated as one row. The column list is every key that appears in any object, in the order keys are first seen. An object that lacks a key gets NULL in that column, so records with different fields can share one table.

Nested objects and arrays are not expanded into extra columns. They are written as their JSON text, so {"seats": 5} is inserted as the string '{"seats":5}'. That suits a PostgreSQL jsonb or MySQL JSON column. If you want each nested field in its own column, run the data through Flatten JSON first and convert the flattened CSV.

Types and values

JSON numbers and booleans are read as text first, then each column is typed the same way as in CSV to SQL: all whole numbers give INTEGER, all numbers give REAL or DOUBLE PRECISION, and true/false/0/1 gives a boolean column. Strings with leading zeros keep the column as text.

null values and missing keys both become NULL. With default values turned on they become '', 0, or false to match the column type, which helps when the target columns are NOT NULL.

What is rejected

An array of plain values such as [1, 2, 3], or an array that mixes objects with other values, is rejected with an error, because there are no keys to use as column names. JSON Lines files (one object per line) are not valid JSON as a whole; wrap the lines in [ ] and add commas between them first.

Example

The second object has no plan or meta, so those are NULL; the first has no trial key. The nested meta object is stored as JSON text.

JSON input
[
  { "id": 1, "email": "ana@example.com", "plan": "pro", "meta": { "seats": 5 } },
  { "id": 2, "email": "raj@example.com", "trial": true }
]
MySQL output
CREATE TABLE IF NOT EXISTS `accounts` (
  `id` INTEGER,
  `email` VARCHAR(15),
  `plan` VARCHAR(3),
  `meta` VARCHAR(11),
  `trial` INTEGER
);

INSERT INTO `accounts` (`id`, `email`, `plan`, `meta`, `trial`) VALUES (1, 'ana@example.com', 'pro', '{"seats":5}', NULL);
INSERT INTO `accounts` (`id`, `email`, `plan`, `meta`, `trial`) VALUES (2, 'raj@example.com', NULL, NULL, true);

How to use JSON to SQL

  1. 1

    Paste a JSON array of objects or drop a .json file.

  2. 2

    Set the table name and choose MySQL, PostgreSQL, or SQLite.

  3. 3

    Pick per-row or single INSERT, CREATE TABLE, and default values as needed.

  4. 4

    Copy the SQL or download inserts.sql.

When to use it

API responses into a database

Save a response from a REST endpoint and load it into a local table for querying or debugging.

Fixtures across environments

Keep test fixtures as JSON in a repo and generate matching SQL when a database needs seeding.

JSON to SQL questions

Can it read JSON Lines (NDJSON)?

Not directly. Wrap the lines in square brackets and separate them with commas so the input is one JSON array.

How do I get nested fields as separate columns?

Flatten the JSON first with Flatten JSON, which turns user.name into its own column, then convert the result with CSV to SQL.

Why is my JSON number stored as text?

If any value in that column is not a plain number, such as 'n/a' or a value with a leading zero, the whole column is typed as text to avoid failed inserts.

Questions about privacy, file size, or accounts are answered on the FAQ page.

Other tools in the same category, starting with the reverse conversion

Search all tools
CSVSQL

CSV to SQL

Generate CREATE TABLE and INSERT statements from CSV or TSV for MySQL, PostgreSQL, or SQLite

JSONTable

Flatten JSON

Flatten nested JSON into CSV columns with dotted names, or nest dotted columns back into JSON

JSONCSV

JSON to CSV

Convert a JSON array of objects into CSV, with one column for every key found

CSVSQL

DB in Browser

Load CSV files as SQLite tables and query them with SQL, including joins, without a server