JSON to SQL
Convert a JSON array of objects into SQL INSERT statements with a CREATE TABLE. Every key becomes a column, its type is inferred from the values, and nested objects or arrays are stored as JSON text in a JSON, JSONB or text column. Output is written for MySQL, PostgreSQL, SQLite or SQL Server.
Frequently asked questions
What JSON can I convert?
An array of objects, such as an API response with a list of records, or a single object, which becomes one row. Arrays of plain values cannot be turned into rows, because they have no column names.
What if objects have different keys?
The columns are all keys found in any object, in order of first appearance. A record that lacks a key gets NULL in that column, and the column is created without NOT NULL.
How are nested objects and arrays stored?
They are written as JSON text. The column type is JSON for MySQL, JSONB for PostgreSQL, TEXT for SQLite and NVARCHAR(MAX) for SQL Server, so you can query them with the database's JSON functions.
How are types inferred?
JSON numbers become INTEGER, BIGINT or a floating point type, booleans become BOOLEAN or BIT, and strings become text, or DATE and DATETIME when every value is an ISO date or date-time. A column with mixed kinds of values becomes text.
What happens to null values?
null is inserted as NULL. A column that contains null or is missing in some records is created as nullable; all other columns get NOT NULL.
Can I insert into an existing table?
Yes. Turn off CREATE TABLE and enter the table name, and only the INSERT statements are generated. The column names are the JSON keys, so they must match your table.