DevKitHub

JSON & Data

JSON to SQL Converter — CREATE TABLE and INSERT Statements

Paste a JSON array of objects to get a CREATE TABLE statement with inferred column types and INSERT statements for its rows. Choose the database, and every value is escaped by that database’s rules.

5 lines
13 lines
Table
3 rows × 6 columns, 1 INSERT
  • Column names are snake_case, so they work unquoted in every dialect: signedUpAt → signed_up_at. Turn on Keep key names to use the keys exactly as written.
  • 1 of 3 rows lacks at least one field, which is inserted as NULL.

Every value is escaped for the database you choose, and for MySQL that depends on the server’s sql_mode: turn on NO_BACKSLASH_ESCAPES only if SELECT @@sql_mode lists it. The table has no primary key or constraints; add them before loading real data. Need the rows as a spreadsheet instead? JSON to CSV flattens them into columns.

This tool runs entirely in your browser. Your input is never uploaded, stored or logged.

How it works

Each key becomes a column, collected from every row rather than the first, so a field only some records have still gets a column and is NULL elsewhere. The type comes from all of a column’s values. Integers are INTEGER, BIGINT past 32 bits and NUMERIC or DECIMAL past 64, and the digits are copied from the JSON text rather than a JavaScript number, so an id like 12345678901234567890 arrives intact. Decimals are NUMERIC in PostgreSQL, a DECIMAL sized to the data in MySQL and SQL Server, and REAL in SQLite. Booleans are BOOLEAN in PostgreSQL, TINYINT(1) in MySQL, 0 and 1 in SQLite and BIT in SQL Server. Nested objects and arrays go into one JSON column, JSONB, JSON, TEXT or NVARCHAR(MAX), unless Flatten turns them into columns such as address_city. A string column becomes DATE or a timestamp only when every value is an ISO 8601 date or date-time; one stray "yesterday" keeps it text, and when most values parse the result names the row that does not. MySQL’s DATETIME has no time zone and refuses a Z, so offsets are converted to UTC for it.

Every value is written into the SQL as a literal, so the escaping is what has to be right: a quote or backslash handled the wrong way for the database corrupts the value, or ends the string early and runs the rest as SQL. Quotes are doubled in all four, and the backslash is where they differ. PostgreSQL, SQLite and SQL Server treat it as an ordinary character, but a PostgreSQL string holding one is written E'…' with it doubled, which reads the same whatever standard_conforming_strings is set to. MySQL treats it as an escape by default, so it is doubled; if your server runs with NO_BACKSLASH_ESCAPES, say so and only quotes are doubled. SQL Server strings are N'…', since without the N non-ASCII text is converted to the database’s code page, and a line break straight after a backslash, which T-SQL reads as a line continuation and deletes, is written as NCHAR(10). No literal holds a raw carriage return, because the mysql and sqlite3 command-line clients drop one at the end of a line. A NUL character or an unpaired surrogate is refused.

Column names are snake_case by default, so userId becomes user_id: PostgreSQL folds unquoted names to lower case, and a column created as "userId" has to be quoted in every query from then on. Keep key names uses the keys as written. Names are always quoted, so a column called order or select works, and are cut to each database’s limit, 63 bytes in PostgreSQL, 64 characters in MySQL and 128 in SQL Server, then made unique ignoring case. The table name can be schema.table. Rows per INSERT is 100 by default and at most 1,000, SQL Server’s limit for one VALUES list. JSON Lines, one object per line, is read as rows, and a single object becomes one row. Not written: primary keys, NOT NULL, indexes, DROP TABLE and upserts, which depend on your schema.

Common problems

Every example below is run against this tool in our test suite, so what it says here is what the tool actually does.

Row 1 is a number, not an object.

[1, 2, 3]
Why:
An array of plain values has no field names to make columns from. An array of arrays fails the same way: it is shaped like rows, but nothing names the columns.
Fix:
Wrap each value in an object, as in [{"value": 1}, {"value": 2}], or paste the array of objects that holds your rows.

JSON keys must use double quotes; single quotes are JavaScript, not JSON.

[{'id': 1, 'active': True}]
Why:
This is how Python prints a list of dicts, not JSON: single quotes, and True, False and None where JSON has true, false and null.
Fix:
Produce the text with json.dumps(rows) in Python rather than print(rows) or str(rows).

The string at $[0].note contains a NUL character (U+0000).

[{"note": "a\u0000b"}]
Why:
A NUL in the data, usually from a C string or a binary field exported as text. PostgreSQL rejects it in text and JSONB, and the sqlite3 shell cuts the statement short at it, so no escaping makes it portable.
Fix:
Remove it before converting, for example by replacing "\u0000" with "" in the JSON, or store the field as binary.

PostgreSQL says column "userid" does not exist, though the table has userId.

Why:
The column was created with the quoted mixed-case name "userId". PostgreSQL folds unquoted names to lower case, so SELECT userId looks for userid, which is a different column.
Fix:
Leave Keep key names off so the column is user_id, or write "userId" in double quotes in every query.

Backslashes arrive doubled in MySQL: C:\temp is stored as C:\\temp.

Why:
The server runs with NO_BACKSLASH_ESCAPES in its sql_mode, where a backslash in a string is an ordinary character, so the \\ written for the default mode is kept as two.
Fix:
Run SELECT @@sql_mode; if it lists NO_BACKSLASH_ESCAPES, turn on that option here and convert again.

SQL Server: The number of row value expressions in the INSERT statement exceeds the maximum allowed number of 1000 row values.

Why:
SQL Server error 10738. One INSERT … VALUES can list at most 1,000 rows, however small they are, and a generator that puts every row in one statement hits it.
Fix:
Nothing to do here, since rows per INSERT is capped at 1,000. For large imports, BULK INSERT or bcp is much faster than INSERT statements.

Frequently asked questions

How do I convert a JSON array to SQL INSERT statements?
Paste the array, choose PostgreSQL, MySQL, SQLite or SQL Server, and set the table name. The output is a CREATE TABLE with a column for every key and INSERT statements with up to 100 rows each by default. Turn off CREATE TABLE when the table already exists.
Which column types does it infer from JSON?
INTEGER, BIGINT or NUMERIC for integers by size, a decimal type for numbers with a fraction, the database’s boolean type, DATE or a timestamp when every value is an ISO 8601 date or date-time, a JSON type for nested objects and arrays, and text for everything else, including columns with mixed types.
Is the generated SQL safe from SQL injection?
Every value is escaped by the chosen database’s own rules, including MySQL’s backslash escapes and SQL Server’s line continuation, so a value such as '); DROP TABLE data; -- is stored as text. For application code, use parameterised queries rather than generated literals; this is for importing a file.
Why is my date column TEXT?
Because at least one value is not a valid ISO 8601 date or date-time, or the column mixes values with and without a time zone offset. A timestamp column is used only when every value parses; when most do, the warning names the first row that does not.
Why are my column names in snake_case?
So they can be used unquoted in every database. PostgreSQL folds unquoted names to lower case, so a camelCase column must be quoted forever. Turn on Keep key names to use the JSON keys exactly as written; they are still quoted, so spaces and reserved words work.

Related tools

Last updated