What the converter produces
For the sample above with the MySQL dialect, the output is a table definition followed by one multi-row insert:
CREATE TABLE `users` (
`id` INT NOT NULL,
`name` VARCHAR(16) NOT NULL,
`email` VARCHAR(16),
`age` INT NOT NULL,
`active` TINYINT(1) NOT NULL,
`joined` DATE NOT NULL,
`address_city` VARCHAR(8) NOT NULL,
`address_pin` VARCHAR(8) NOT NULL,
`tags` JSON,
PRIMARY KEY (`id`)
);
INSERT INTO `users` (`id`, `name`, `email`, `age`, `active`, `joined`, `address_city`, `address_pin`, `tags`) VALUES
(1, 'Asha Verma', 'asha@example.com', 29, 1, '2024-03-18', 'Pune', '411001', NULL),
(2, 'Ravi O''Neil', NULL, 34, 0, '2023-11-02', 'Delhi', '110001', '["admin","beta"]');
Three details are worth noticing. The apostrophe in O'Neil is doubled, which is the SQL-standard escape. email is nullable because one row has null, and tags is nullable because the first row does not have the key at all. And pin stays a string, because it was a string in the JSON; the converter never guesses that "411001" should be a number, since that would silently drop the leading zero from a value like "012345".
How column types are inferred
The converter looks at every value in a column across all rows, then picks the narrowest type that fits all of them. null and missing keys do not vote; they only make the column nullable.
| JSON values in the column | MySQL | PostgreSQL | SQLite | SQL Server |
|---|---|---|---|---|
| Whole numbers within ±2,147,483,647 | INT | INTEGER | INTEGER | INT |
| Whole numbers beyond that | BIGINT | BIGINT | INTEGER | BIGINT |
| Any value with a fraction or exponent | DOUBLE | DOUBLE PRECISION | REAL | FLOAT |
true / false | TINYINT(1) | BOOLEAN | INTEGER (0/1) | BIT |
Strings like 2024-03-18 | DATE | DATE | TEXT | DATE |
Strings like 2024-03-18T09:30:00Z | DATETIME | TIMESTAMPTZ / TIMESTAMP | TEXT | DATETIME2 |
| Other strings up to 255 characters | VARCHAR(n) | VARCHAR(n) | TEXT | NVARCHAR(n) |
| Longer strings | TEXT | TEXT | TEXT | NVARCHAR(MAX) |
| Arrays, or objects in JSON-column mode | JSON | JSONB | TEXT | NVARCHAR(MAX) |
| A mix of the above | falls back to text, and the notes under the output name the column so you can fix the data or the type | |||
VARCHAR lengths are rounded up to the next power of two (minimum 8) above the longest value seen, so a sample with a 13-character name gets VARCHAR(16). That is a starting point, not a schema decision: if the real data can be longer, widen the column before you load production rows.
SQLite uses type affinity instead of strict types, so it only needs INTEGER, REAL and TEXT. Dates are kept as ISO-8601 text, which is the format SQLite's own date functions read.
Escaping rules by dialect
- All dialects: a single quote inside a string is doubled:
'It''s'. This is the only escape the SQL standard defines, and every database understands it. - MySQL and MariaDB: backslash is an escape character inside string literals unless the server runs with
NO_BACKSLASH_ESCAPES, so backslashes are doubled too. A Windows pathC:\tempis written as'C:\\temp'. Identifiers are quoted with backticks. - PostgreSQL: with
standard_conforming_stringson (the default since 9.1) a backslash is an ordinary character, so only quotes are doubled. Identifiers use double quotes, which also preserves mixed-case column names like"createdAt". - SQL Server (MS SQL): strings get the
N'...'prefix so Unicode text such as Hindi or emoji is not converted to the database code page. Identifiers use square brackets, and multi-row inserts are split at 1,000 rows, the most a singleVALUESlist allows. - SQLite: double-quoted identifiers and standard string literals; booleans are written as
1and0.
Column names are taken from the JSON keys. Characters that are not letters, digits or underscores are replaced with _, so "first name" becomes first_name and "user.id" becomes user_id. If two keys collapse to the same name the second gets a numeric suffix.
Input shapes it accepts
- An array of objects, the normal case: one row per object, columns are the union of every key.
- A single object is treated as a one-row table.
- An API envelope such as
{"data": [...]},{"results": [...]}or{"items": [...]}: when the top level is an object with exactly one array of objects inside, that array is used and the notes say so. - JSON Lines (one object per line) is detected and read row by row. To convert the other way, from
.jsonlto a regular array, use the JSONL to JSON converter.
Loading the result
Download the .sql file and run it with your database client:
# MySQL / MariaDB
mysql -u root -p mydb < users.sql
# PostgreSQL
psql -d mydb -f users.sql
# SQLite
sqlite3 app.db < users.sql
# SQL Server
sqlcmd -S localhost -d mydb -i users.sql
For more than a few hundred thousand rows, generated INSERTs are slower than the database's bulk loader. Convert the JSON to CSV with the JSON to CSV converter and use LOAD DATA INFILE (MySQL), \copy (PostgreSQL) or .import (SQLite) instead. PostgreSQL and MySQL can also ingest JSON directly with json_populate_recordset and JSON_TABLE if you would rather keep the transformation in SQL.
Questions
How are SQL column types chosen?
From every row, not just the first. Whole numbers become INT or BIGINT, fractions a floating type, booleans the dialect's boolean, ISO dates DATE or a timestamp type, other strings a sized VARCHAR. Mixed columns fall back to text and are listed in the notes.
How are quotes inside strings escaped?
Single quotes are doubled for every dialect. MySQL also gets doubled backslashes, and SQL Server strings get the N prefix for Unicode.
What happens to nested objects and arrays?
Objects are flattened into parent_child columns, or kept whole in a JSON column if you choose. Arrays are always stored as JSON text, because one SQL cell cannot hold a list. If you need a child table instead, extract the array with the JSONPath tester and convert it separately.
Do long IDs lose precision?
No. Integers above 253 are read from the original text, so 1453729827150749696 is written exactly, not rounded the way JSON.parse would round it.
My JSON will not convert. What now?
The status line shows the parser's error. If the JSON has comments, trailing commas or single quotes, run it through the JSON formatter's Repair mode first, or use the JSON validator to find the exact line and column.
Is my data uploaded?
No. Everything happens in your browser tab, including the download.