JSON to SQL converter
The result appears here.
Convert
Your document never leaves the browser.
JSON to CREATE TABLE and INSERT for PostgreSQL, MySQL or SQLite — in your browser.
Paste an array of records and get a table definition and the INSERT statements that fill it, written for the database you pick and quoted so it loads as written.
The rows are the ones the Table view opens on: the page steps into a single wrapping key, so {"orders": [...]} becomes a table of orders. An array gives one row per item. An object gives one row, not a row per key. A lone string or number has no rows, and the page says where it is.
The JSON
{
"orders": [
{ "id": 1, "total": 9.5 },
{ "id": 2, "total": 12 }
]
}The SQL it gives, at the default settings
CREATE TABLE "orders" (
"id" INTEGER NOT NULL,
"total" NUMERIC NOT NULL
);
INSERT INTO "orders" ("id", "total") VALUES
(1, 9.5),
(2, 12);Every key in any row becomes a column, in the order keys first appear, with no cap. A row without the key gets NULL. Array items that are not objects go in a column named value.
Users with a big number, a boolean, a mixed column and a key one row lacks
{
"users": [
{ "id": 1, "visits": 12345678901, "vip": true, "code": 7 },
{ "id": 2, "visits": 5, "vip": false, "code": "B2", "note": "new" }
]
}The SQL it gives, at the default settings
CREATE TABLE "users" (
"id" INTEGER NOT NULL,
"visits" BIGINT NOT NULL,
"vip" BOOLEAN NOT NULL,
"code" TEXT NOT NULL,
"note" TEXT
);
INSERT INTO "users" ("id", "visits", "vip", "code", "note") VALUES
(1, 12345678901, TRUE, '7', NULL),
(2, 5, FALSE, 'B2', 'new');Type
Whole numbers are INTEGER, or BIGINT past 32 bits. Wider ones are NUMERIC in PostgreSQL and DECIMAL in MySQL; SQLite has no exact type that wide, so they are TEXT, and the result says so.
Type
Decimals are NUMERIC, or DECIMAL with the precision the values need in MySQL. Numbers are copied as written.
Type
Booleans are BOOLEAN, and 1 or 0 in an INTEGER column in SQLite. Strings, and a column mixing kinds of value, are TEXT.
Type
NOT NULL is added only when every row has a non-null value. No primary key is guessed: a wrong one would reject rows.
An object or array inside a row is not flattened. It goes in one column, as its JSON text: JSONB in PostgreSQL, JSON in MySQL, TEXT in SQLite. MySQL refuses JSON nested deeper than 100 levels, so a column holding such a value is TEXT instead, with a notice.
A row holding an array and an object
{
"users": [
{ "id": 1, "tags": ["a", "b"], "meta": { "vip": true } }
]
}The SQL it gives, at the default settings
CREATE TABLE "users" (
"id" INTEGER NOT NULL,
"tags" JSONB NOT NULL,
"meta" JSONB NOT NULL
);
INSERT INTO "users" ("id", "tags", "meta") VALUES
(1, '["a","b"]', '{"vip":true}');Table and column names are always quoted, so a key keeps its exact spelling and a reserved word needs no special case. Names past PostgreSQL’s 63 bytes or MySQL’s 64 characters are shortened, and repeats get a _2 suffix. MySQL strings escape backslashes, and a SQLite string holding a U+0000 character is joined with char(0). PostgreSQL cannot store U+0000 at all, so that is refused with its position.
Statements picks CREATE TABLE and INSERT, CREATE TABLE only, or INSERT only. INSERTs carry 500 rows each, a size all three databases accept. The table is named after the wrapping key, else the file name, else data. A name you type is used until a new document arrives, and is never remembered; the dialect and statements are.
When the output had to change something — a name shortened, suffixed or filled in, a column made TEXT, a lone surrogate replaced — a line under the result counts it.
The first example’s JSON, with Dialect MySQL
CREATE TABLE `orders` (
`id` INTEGER NOT NULL,
`total` DECIMAL(3,1) NOT NULL
);
INSERT INTO `orders` (`id`, `total`) VALUES
(1, 9.5),
(2, 12);The conversion runs in your browser, in the same engine the editor uses. The document is never uploaded, never logged and never leaves your machine — there is no server to send it to. Once the page has loaded it keeps working with the connection off.
Nothing in JSON says which field is unique. A guessed key that repeats makes the INSERTs fail, so add one once the data is in.
No. A nested object or array is one JSON column holding its text, so nothing is lost and the columns stay the keys you see.
That is what it is written for. While it was built, the reference outputs were loaded into SQLite and MySQL 9.5, and the PostgreSQL output was checked in PGlite, an in-browser build of PostgreSQL.