--- title: "JSON to SQL Converter" description: "Convert a JSON array, NDJSON or mongoexport file to CREATE TABLE and INSERT statements for SQL Server, PostgreSQL, MySQL, Oracle or SQLite in your browser." url: "https://jamsql.com/tools/json-to-sql/" html_url: "https://jamsql.com/tools/json-to-sql/" generated: "2026-09-24T15:18:13.002Z" --- # JSON to SQL Converter Paste JSON or open a `.json` / `.ndjson` file and get a `CREATE TABLE` statement plus `INSERT` statements for SQL Server, PostgreSQL, MySQL, Oracle or SQLite. A JSON array, a single object, NDJSON and `mongoexport` output all work. Column types are inferred from every value by the same code Jam SQL Studio's Data Import runs. Nothing is uploaded. JSON file Load sample: JSON array Load sample: mongoexport Clear A JSON array of objects, one object, or NDJSON (one object per line). Up to 5 MiB and 20,000 rows. Or paste JSON Engine SQL ServerPostgreSQLMySQLOracleSQLite Table Schema CREATE TABLE INSERT Rows per INSERT 1101005001,000 Oracle: one row per INSERT (multi-row VALUES needs Oracle 23ai). Unwrap MongoDB Extended JSON ## Inferred columns Include Column SQL type Null Notes ## Generated SQL Copy SQL Download .sql ## Import the JSON file straight into a table Jam SQL Studio's Data Import reads a `.json` or `.ndjson` file with the same type inference as this page and creates the table, or appends to an existing one, on SQL Server, PostgreSQL, MySQL, Oracle or SQLite. Press `Ctrl/Cmd+I` or drop the file onto the app window. [Get Jam SQL Studio — free](/#download) [Data Import docs](/docs/import-data/) Paste JSON, open a file or click one of the **Load sample** buttons. The inferred columns and the SQL appear here. ## What the converter does It turns JSON rows into a table: every top-level key becomes a column, every object becomes one row, and each column gets a SQL type chosen from all of its values. The output is one `CREATE TABLE` and the `INSERT` statements for the rows, written for the engine you pick. It is meant for loading an API response, a `mongoexport` dump or a JSON fixture into a relational table without writing the DDL by hand, and for checking what types a JSON feed would need before you build the real schema. The page's script contains Jam SQL Studio's own Data Import code: the type inference, the column-name rules, the `CREATE TABLE` writer and the literal writer behind the wizard's *Generate script instead of executing* option. With **Rows per INSERT** set to 1, the `INSERT` lines are the text the desktop app writes for the same file, except for these deliberate differences (as of September 2026): - MongoDB Extended JSON wrappers are unwrapped (you can turn this off); the desktop app keeps them as JSON. - Columns follow the order in which keys first appear. The desktop app reads each object's keys in JavaScript order, which puts integer-like keys such as `"7"` or `"2024"` first, in numeric order. - A key longer than the engine's identifier limit is cut to that limit; the desktop app keeps the whole key. - Numbers keep every digit. The desktop app reads decimals and numbers inside nested JSON through a double, and on SQL Server, Oracle and SQLite it also writes `BIGINT` and `DECIMAL` values above 253 through one, so `7212481950938146817` becomes `7212481950938147000`. - Oracle date and time literals are written from the text. The desktop app builds them through a JavaScript `Date` in the computer's time zone, so west of UTC a date moves to the day before, a time inside a daylight-saving gap moves forward by the gap, and a time of day comes out as `TO_TIMESTAMP('NaN-NaN-NaN…')`. - Oracle `timestamp with time zone` values are written as `FROM_TZ(TO_TIMESTAMP(…), 'UTC')`. The desktop app's script writes the `TO_TIMESTAMP` alone, which Oracle reads in the session time zone, so a session at +02:00 stores 08:21:45 UTC as 06:21:45 UTC. - The Oracle `-- WARNING` comment appears for a text value over 4,000 bytes, the unit of ORA-01704. The desktop app counts characters, so it does not warn about text of 4,000 characters or fewer that accented or non-Latin letters take past 4,000 bytes. - On SQL Server, an ISO date-time written without zero padding or with slashes, such as `2026/3/5 10:15`, gets a valid literal; the desktop app cannot parse it. ## Getting JSON out of your database or API **MongoDB.** `mongoexport` writes one document per line (JSON Lines) in relaxed Extended JSON. Add `--jsonArray` for a single array, or `--jsonFormat=canonical` to wrap every number as `$numberInt` / `$numberLong` / `$numberDouble`. The converter reads all three. mongoexport --uri="mongodb://localhost:27017/shop" --collection=customers --out=customers.ndjson mongoexport --uri="mongodb://localhost:27017/shop" --collection=customers --jsonArray --out=customers.json **SQL Server.** `FOR JSON PATH` returns an array of objects. It leaves out properties whose value is `NULL` unless you add `INCLUDE_NULL_VALUES`; a missing key becomes `NULL` here anyway, but a column that is `NULL` in every row would otherwise disappear. Client libraries receive a long `FOR JSON` result split across several rows, so wrap it in a subquery to get one value. SELECT ( SELECT order\_id, customer, total, placed\_at FROM dbo.Orders FOR JSON PATH, INCLUDE\_NULL\_VALUES ) AS orders\_json; **PostgreSQL.** `row_to_json` gives one object per row; `psql -At` prints each on its own line without headers or padding, which is NDJSON. `json_agg` builds a single array instead. Avoid `\copy ... TO` for this: its text format doubles every backslash, which breaks JSON strings that contain escapes. psql -At -d shop -c "SELECT row\_to\_json(c) FROM customers c" > customers.ndjson psql -At -d shop -c "SELECT json\_agg(c) FROM customers c" > customers.json **MySQL.** Build each row with `JSON_OBJECT` and run the client in batch mode with `--raw`; without it, batch output escapes backslashes and the JSON no longer parses. mysql -N -B --raw -e "SELECT JSON\_OBJECT('id', id, 'name', name, 'email', email) FROM shop.customers" > customers.ndjson **SQLite.** The `sqlite3` shell prints a JSON array with `-json` (SQLite 3.33 and later). sqlite3 -json app.db "SELECT \* FROM customers;" > customers.json **REST APIs.** Most responses wrap the rows in an envelope such as `{"data": [...], "page": 1}`. Paste the whole response and the converter offers a **Use “data” as the rows** button, or cut the array out first with `jq`: curl -s "https://api.example.com/v1/orders?limit=100" | jq '.data' > orders.json curl -s "https://api.example.com/v1/orders?limit=100" | jq -c '.data\[\]' > orders.ndjson ## How column types are inferred Every non-null value of a column is tested against each candidate type, and the column gets the first candidate in this order that *all* of its values pass. These are the rules in Jam SQL Studio's Data Import (`typeInference.ts` and `valueTests.ts`), unchanged: 1. **Boolean**: JSON `true`/`false`, or the strings `"true"`/`"false"` in any case. `0`/`1` and `"yes"`/`"no"` are not booleans. 2. **32-bit integer**: whole numbers from −2,147,483,648 to 2,147,483,647. 3. **64-bit integer**: whole numbers in the `BIGINT` range. 4. **Decimal**: plain decimals with at most 38 digits. Precision is the widest integer part plus the widest fraction (capped at 38), scale is the widest fraction, so `149.95`, `32.5` and `1200` give `DECIMAL(6,2)`. Whole numbers beyond the 64-bit range also land here as `DECIMAL(38,0)`. 5. **Double**: a non-zero number smaller than 0.000001 or at least 1021 in absolute value, which JavaScript prints with an exponent (`0.0000001` is `1e-7`), and text such as `"1.5e-7"`. A JSON number is typed by its value, not its spelling: `1e5` is the integer 100000. 6. **Date**: `YYYY-MM-DD` (or `/`), and `DD/MM/YYYY` or `MM/DD/YYYY` with `/`, `-` or `.`. The data decides the order: `13/04/2024` can only be day/month. When every value fits both orders, as `03/04/2024` does, no order is assumed and the columns table asks you to choose before any `INSERT` is written. 7. **Date-time**: a date followed by `HH:MM`, optional seconds and fraction. 8. **Date-time with time zone**: an ISO date-time ending in `Z` or an offset such as `+01:00`. 9. **Time**: `HH:MM` or `HH:MM:SS`. 10. **UUID**: 8-4-4-4-12 hexadecimal. 11. **JSON**: any nested object or array, and any string that itself parses as a JSON object or array. 12. **Text**: everything else, sized to the next bucket above the longest value: 50, 100, 255, 1,000 or 4,000 characters, then unbounded. A value with a leading zero (`007`) or a leading `+` is never a number, so ZIP codes and phone numbers stay text. A column is `NOT NULL` only when every row has a non-null value for it; a missing key counts as `NULL`. An empty string `""` is a value, not `NULL`, so a numeric column with one `""` becomes text. Text with any non-ASCII character becomes a Unicode type on SQL Server (`nvarchar`). Column names are the keys with surrounding spaces trimmed, inner spaces turned into `_`, a blank key named after its position (`column_2` when it is the second key), duplicates (ignoring case) suffixed `_2`, and long names cut to the engine's limit (63 characters on PostgreSQL, 64 on MySQL, 128 on SQL Server and Oracle). Inferred kind and the type written for each engine Values SQL Server PostgreSQL MySQL Oracle SQLite true / false bit boolean tinyint(1) number(1) BOOLEAN 32-bit integers int integer int number(9) INTEGER 64-bit integers bigint bigint bigint number(19) BIGINT decimals decimal(p,s) numeric(p,s) decimal(p,s) number(p,s) NUMERIC(p,s) doubles (1e-7, 1e21) float double precision double binary\_double REAL dates date date date timestamp DATE date-times datetime2 timestamp datetime timestamp DATETIME date-times with Z / offset datetimeoffset timestamp with time zone timestamp timestamp with time zone DATETIME times time time time timestamp TIME UUIDs uniqueidentifier uuid char(36) raw(16) TEXT nested objects / arrays json jsonb json json JSON ASCII text up to 4,000 chars varchar(n) character varying(n) varchar(n) varchar2(n) VARCHAR(n) non-ASCII text up to 4,000 chars nvarchar(n) character varying(n) varchar(n) varchar2(n) NVARCHAR(n) longer text varchar(max) / nvarchar(max) text TEXT, MEDIUMTEXT or LONGTEXT clob TEXT / NTEXT The **SQL type** list in each row of the columns table offers the inferred type plus the common types the desktop wizard offers for that engine. Picking one changes both the `CREATE TABLE` and how the values are written. JSON strings are then checked against a number, date, time, boolean, UUID or JSON type with the test the desktop app runs when it skips bad rows: if one does not parse (`"Ana"` in an `INTEGER` column, say), no `INSERT`s are written until you pick a wider type. JSON numbers and booleans are not checked, and neither is text length, so `1.5` in an `INTEGER` column or a 300-character value in `nvarchar(255)` still gets its `INSERT` and the database decides what happens to it. ## Nested objects and arrays become JSON columns Only top-level keys become columns. A nested object or array is not flattened into `address_city`, `address_zip` and so on; it is written as compact JSON text into one column, exactly as Jam SQL Studio's Data Import stores it. The column type is `json` on SQL Server, `jsonb` on PostgreSQL, `json` on MySQL and Oracle, and `JSON` on SQLite (where it is a declared type over `TEXT`). SQL Server's native `json` type needs SQL Server 2025 or Azure SQL Database, and Oracle's needs Oracle Database 21c or later; on older versions pick `nvarchar(max)` or `CLOB` in the SQL type list. The notes column lists the JSON paths found in the values, for example `$.city, $.zip` or `$[*].sku`. A path without `[*]` works as written in `JSON_VALUE` on SQL Server, MySQL and Oracle, in `json_extract` on SQLite and in `jsonb_path_query_first` on PostgreSQL, whose `->>` operator takes a key name instead (`address->>'city'`). A `[*]` path matches every element of an array: `JSON_VALUE` returns `NULL` for it and SQLite's `json_extract` rejects it, so use `JSON_QUERY` with an array wrapper (SQL Server 2025, Oracle), `JSON_EXTRACT` (MySQL) or `jsonb_path_query` (PostgreSQL). The [SQL JSON Query Builder](/tools/sql-json-query-builder/) writes these expressions for each engine. ## MongoDB Extended JSON: $oid, $date, $numberLong `mongoexport` writes BSON types as wrapper objects. With **Unwrap MongoDB Extended JSON** ticked (the default), the converter replaces them with plain values before inference, anywhere in the document, and lists how many it unwrapped: - `{"$oid": "66f1…"}` becomes the 24-character hex string. - `{"$date": "2025-11-03T08:21:45.120Z"}` (relaxed), `{"$date": {"$numberLong": "1705311000000"}}` (canonical) and `{"$date": 1705311000000}` become an ISO-8601 UTC timestamp. - `$numberLong`, `$numberInt` and `$numberDouble` become numbers; `$numberLong` keeps every digit. `Infinity` and `NaN` stay text because no SQL numeric type holds them. - `$numberDecimal` keeps its exact digits and scale, so `"1249.50"` counts as two decimal places. - `$uuid` and `$binary` with subtype `04` become a UUID string. Other binaries, `$timestamp`, `$regularExpression` and anything that is not an exact wrapper stay JSON. This step exists only in the browser tool. The desktop app's Data Import reads the same file without it, so there each wrapper is a nested object and becomes a JSON column; untick the option to see that result here. ## Large integers and exact decimals JavaScript's `JSON.parse` stores every number as a double, which holds integers exactly only up to 253 (9,007,199,254,740,992). A 64-bit ID such as `7212481950938146817` comes out as `7212481950938147000`, and a converter built on `JSON.parse` writes that wrong ID into your `INSERT`. This page reads numbers from the raw text instead, the way the desktop app's JSON reader does for top-level integers, and writes the exact digits of any number a double cannot hold, including long decimals and numbers inside nested JSON text. When a `BIGINT` or `DECIMAL` column holds such numbers, a note above the columns table names the column and shows what `JSON.parse` would make of the first one. A column typed as a double rounds them anyway, so it gets no note. In the SQL, SQL Server, Oracle and SQLite get the digits as a bare number and PostgreSQL and MySQL as quoted text that the server casts, so a `BIGINT` or `DECIMAL` column on SQL Server, PostgreSQL, MySQL and Oracle receives the value you exported; SQLite's `NUMERIC` storage rounds some of them (see Limitations). Inside a JSON column the digits are part of the text, but MySQL's `JSON` type stores a number with a fraction or beyond the 64-bit range as a double. Numbers a double does hold exactly are read as numbers, so trailing zeros are dropped: `10.50` is read as `10.5` and counts as one decimal place. Quote such values in the JSON, or export them as `$numberDecimal`, when the scale matters. ## Example: a JSON array to SQL Server The **Load sample: JSON array** input, three orders shaped like a REST API response (synthetic data): \[ { "order\_id": 1001, "customer": "Ana Souza", "email": "ana@example.com", "total": 149.95, "paid": true, "placed\_at": "2026-03-02T09:14:00Z", "ship\_by": "2026-03-04", "items": \[{ "sku": "KB-104", "qty": 1 }, { "sku": "MS-220", "qty": 2 }\], "coupon": null }, { "order\_id": 1002, "customer": "Jörg Müller", "email": "jorg@example.com", "total": 32.5, "paid": false, "placed\_at": "2026-03-02T11:40:12Z", "ship\_by": "2026-03-06", "items": \[{ "sku": "CB-310", "qty": 3 }\], "coupon": "SPRING10" }, { "order\_id": 1003, "customer": "Liam O'Connor", "email": "liam@example.com", "total": 1200, "paid": true, "placed\_at": "2026-03-03T16:05:47Z", "ship\_by": "2026-03-05", "items": \[\], "coupon": null } \] The output for SQL Server with the default options, unedited: \-- Generated in your browser by the free JSON to SQL converter on jamsql.com -- SQL Server · 3 rows · 9 columns CREATE TABLE \[orders\] ( \[order\_id\] int NOT NULL, \[customer\] nvarchar(50) NOT NULL, \[email\] varchar(50) NOT NULL, \[total\] decimal(6,2) NOT NULL, \[paid\] bit NOT NULL, \[placed\_at\] datetimeoffset NOT NULL, \[ship\_by\] date NOT NULL, \[items\] json NOT NULL, \[coupon\] varchar(50) ); INSERT INTO \[orders\] (\[order\_id\], \[customer\], \[email\], \[total\], \[paid\], \[placed\_at\], \[ship\_by\], \[items\], \[coupon\]) VALUES (1001, N'Ana Souza', N'ana@example.com', 149.95, 1, N'2026-03-02T09:14:00.000Z', N'2026-03-04T00:00:00.000Z', N'\[{"sku":"KB-104","qty":1},{"sku":"MS-220","qty":2}\]', NULL); INSERT INTO \[orders\] (\[order\_id\], \[customer\], \[email\], \[total\], \[paid\], \[placed\_at\], \[ship\_by\], \[items\], \[coupon\]) VALUES (1002, N'Jörg Müller', N'jorg@example.com', 32.5, 0, N'2026-03-02T11:40:12.000Z', N'2026-03-06T00:00:00.000Z', N'\[{"sku":"CB-310","qty":3}\]', N'SPRING10'); INSERT INTO \[orders\] (\[order\_id\], \[customer\], \[email\], \[total\], \[paid\], \[placed\_at\], \[ship\_by\], \[items\], \[coupon\]) VALUES (1003, N'Liam O''Connor', N'liam@example.com', 1200, 1, N'2026-03-03T16:05:47.000Z', N'2026-03-05T00:00:00.000Z', N'\[\]', NULL); What to notice: `customer` is `nvarchar` because of *Jörg Müller* while the all-ASCII `email` is `varchar`; `total` is `decimal(6,2)` from the widest integer part (`1200`) and fraction (`149.95`); the `items` array stays one `json` column; `coupon` is nullable because two rows have `null`; and the apostrophe in *O'Connor* is doubled. Every string literal carries the `N` prefix so Unicode survives the insert. ## Example: mongoexport output to PostgreSQL The **Load sample: mongoexport** input is `mongoexport`'s default format, one relaxed Extended JSON document per line. The third customer has no `device_id`. {"\_id":{"$oid":"66f1c2a4e13b7a5d9c8e4a01"},"name":"Ana Souza","email":"ana@example.com","signup":{"$date":"2025-11-03T08:21:45.120Z"},"plan":"pro","balance":{"$numberDecimal":"1249.50"},"device\_id":7212481950938146817,"address":{"city":"Lisboa","zip":"1100-148"},"tags":\["beta","newsletter"\]} {"\_id":{"$oid":"66f1c2a4e13b7a5d9c8e4a02"},"name":"Jörg Müller","email":"jorg@example.com","signup":{"$date":"2026-01-17T14:02:09.000Z"},"plan":"free","balance":{"$numberDecimal":"0.00"},"device\_id":7212481950938146818,"address":{"city":"München","zip":"80331"},"tags":\[\]} {"\_id":{"$oid":"66f1c2a4e13b7a5d9c8e4a03"},"name":"Liam O'Connor","email":"liam@example.com","signup":{"$date":"2026-02-28T19:45:00.500Z"},"plan":"pro","balance":{"$numberDecimal":"87.25"},"address":{"city":"Dublin"},"tags":\["newsletter"\]} The output for PostgreSQL with **Rows per INSERT** set to 100: \-- Generated in your browser by the free JSON to SQL converter on jamsql.com -- PostgreSQL · 3 rows · 9 columns CREATE TABLE "customers" ( "\_id" character varying(50) NOT NULL, "name" character varying(50) NOT NULL, "email" character varying(50) NOT NULL, "signup" timestamp with time zone NOT NULL, "plan" character varying(50) NOT NULL, "balance" numeric(6,2) NOT NULL, "device\_id" bigint, "address" jsonb NOT NULL, "tags" jsonb NOT NULL ); INSERT INTO "customers" ("\_id", "name", "email", "signup", "plan", "balance", "device\_id", "address", "tags") VALUES ('66f1c2a4e13b7a5d9c8e4a01', 'Ana Souza', 'ana@example.com', '2025-11-03T08:21:45.120Z', 'pro', '1249.50', '7212481950938146817', '{"city":"Lisboa","zip":"1100-148"}', '\["beta","newsletter"\]'), ('66f1c2a4e13b7a5d9c8e4a02', 'Jörg Müller', 'jorg@example.com', '2026-01-17T14:02:09.000Z', 'free', '0.00', '7212481950938146818', '{"city":"München","zip":"80331"}', '\[\]'), ('66f1c2a4e13b7a5d9c8e4a03', 'Liam O''Connor', 'liam@example.com', '2026-02-28T19:45:00.500Z', 'pro', '87.25', NULL, '{"city":"Dublin"}', '\["newsletter"\]'); The three `$oid`, `$date` and `$numberDecimal` wrappers are gone, `balance` keeps its two decimal places, `device_id` keeps all 19 digits and is nullable because the third document lacks it, and `address` and `tags` are `jsonb`. Before publishing, both examples and a set of edge cases (64-bit IDs, a 20-digit integer, a 19-digit decimal, UUIDs, times of day, day/month dates, date-times inside daylight-saving gaps and date-times with offsets, with Oracle's session time zone set to +02:00) were run, at 1 and 100 rows per `INSERT`, against SQL Server 2025, PostgreSQL 16, MySQL 8.0, Oracle AI Database 26ai Free and SQLite 3.46 on 23 September 2026. ## Supported input and limits - **Shapes:** a JSON array of objects; a single object; NDJSON / JSON Lines (one object per line); objects written one after another, even pretty-printed. Every row must be an object: an array of numbers or strings is rejected with the row number. A single object whose property holds an array of objects gets a button to use that array as the rows. A UTF-8 byte-order mark is ignored. - **Size:** 5 MiB of input and 20,000 rows per run. More rows are counted and reported, and only the first 20,000 are written. Nesting is limited to 512 levels. - **Rows per INSERT:** 1 (the desktop app's script format), 10, 100, 500 or 1,000. 1,000 is SQL Server's limit for a `VALUES` list (error 10738) and is used as the ceiling everywhere; a statement is also split before it passes 4,000,000 bytes. Oracle always gets one row per `INSERT`, because multi-row `VALUES` only arrived in Oracle Database 23ai. - **Oracle literals:** Oracle caps a string literal at 4,000 bytes (ORA-01704), which is fewer than 4,000 characters when the text has accented or non-Latin letters. A row with a longer text value still gets its `INSERT`, preceded by a `-- WARNING` comment. The converter counts UTF-8 bytes, the encoding of Oracle's default AL32UTF8 character set. - **Output:** the SQL pane shows at most the first 200,000 characters, cut at the end of a line; Copy and Download always carry the whole script. ## Everything stays in your browser The converter is one script, `/js/tools/json-to-sql.js`, and the JSON you paste or open is parsed and converted by it in memory. It is not sent to a server, not placed in the URL, and not written to `localStorage` or `sessionStorage`; **Download .sql** builds the file in the page. The page also loads what every jamsql.com page loads: small inline menu and image-zoom code, `/js/jam-events.js`, and the Umami and Google Ads tags, which see the page view but not your input. The analytics events are these, with no keys, values, table names or file names in them: - `tool-json-to-sql-load-sample` with `sample`: `array` or `mongoexport`. - `tool-json-to-sql-open-file` when you pick a file, with no properties. - `tool-json-to-sql-generate` once per new input, when its SQL is first generated, with `engine`, `form` (`array`, `object`, `ndjson` or `nested-array`), `rows` as a bucket (`1-10`, `11-100`, `101-1000`, `1001-10000` or `10001+`) and `extendedJson` (`yes` or `no`). - `tool_sql_export` when you copy or download the SQL, with `tool`: `json-to-sql`, `outcome`: `copy` or `download`, and the page path. - On the links: `download_page_click` and `tool_product_click` from the *Get Jam SQL Studio* and *Data Import docs* links (page, placement, target OS or tool and destination kind), and `tool-json-to-sql-download-cta` from the *Download Free* button. **Clear** empties the input, the notices, the columns table and the SQL, and resets a table name that came from a file name or a sample; a name you typed stays. ## Limitations - Types are inferred from the rows you paste, not from your real schema. Fifty numeric codes do not prove the next file has no letters in them. - No primary key, index or foreign key is guessed. Add them after the `CREATE TABLE`. - Nested values are not flattened into separate columns; query them with the engine's JSON functions. - Identifiers are always quoted and keep their case. On Oracle a lower-case table such as `"customers"` has to be quoted in every query, and on PostgreSQL so does a key like `customerId`. - The script has no transaction around it and no `GO` separators. For all or nothing, put `BEGIN;` before it and `COMMIT;` after it on PostgreSQL and SQLite, and `SET XACT_ABORT ON; BEGIN TRANSACTION;` … `COMMIT TRANSACTION;` on SQL Server (without `XACT_ABORT` an error does not roll the transaction back). MySQL and Oracle commit a `CREATE TABLE` as soon as it runs, so only the `INSERT`s can be undone: wrap them in `START TRANSACTION;` … `COMMIT;` on MySQL, and on Oracle run the script in SQL\*Plus after `WHENEVER SQLERROR EXIT ROLLBACK`, or `ROLLBACK` by hand after an error. - MySQL `datetime`, `timestamp` and `time` columns are created without fractional seconds, so MySQL rounds `19:45:00.500` to `19:45:01` and `08:30:15.600` to `08:30:16`. Date-times with `Z` or an offset go into a `timestamp` column, which only holds 1970-01-01 00:00:01 to 2038-01-19 03:14:07 UTC; a later value fails with error 1292. - Oracle stores an empty string as `NULL`, so a `NOT NULL` text column that has `""` in some rows fails with ORA-01400; remove `NOT NULL` from the `CREATE TABLE` for it. And `varchar2(n)` counts bytes, so text with accented or non-Latin letters can pass the limit (ORA-12899: thirty `é` are 60 bytes in a `varchar2(50)`); pick `VARCHAR2(255)` or `CLOB` in the SQL type list. - Oracle has no time-of-day type: times are stored as a `timestamp` on 1970-01-01. A date-time with an offset keeps its instant but not its offset: SQL Server stores it at `+00:00`, Oracle in UTC, PostgreSQL and MySQL convert it to UTC, and only SQLite keeps the text as written. - SQLite stores a `NUMERIC` value as an 8-byte integer or float, so it rounds whole numbers beyond the 64-bit range and decimals with more than about 15 significant digits on its own; declare the column `TEXT` if every digit matters. - `INSERT` scripts are the slow way to load big data. For recurring or large loads use `COPY`, `BULK INSERT`, `LOAD DATA`, SQL\*Loader, or the desktop app's Data Import. ## Import the JSON file straight into a table Jam SQL Studio's Data Import reads `.json` and `.ndjson` files with the same type inference as this page, in four steps — Source, Target, Columns, Import — on SQL Server, PostgreSQL, MySQL, Oracle and SQLite. Press `Ctrl/Cmd+I` or drop the file onto the app window. - The file is read from disk as a stream: JSON arrays up to 256 MB, NDJSON with no size limit. The free Personal licence imports up to 100,000 rows per run; Pro removes the cap. - Append to an existing table: file keys are matched to the table's columns, and a column the file does not have can take a fixed value. - Bad rows: stop and roll back, or skip them and get a report naming the row, column, value and reason. - Want the script anyway? *Generate script instead of executing* writes the DDL and `INSERT`s and runs nothing. [Get Jam SQL Studio — free](/#download) [Data Import docs](/docs/import-data/) ## Frequently asked questions ### How do I convert JSON to SQL INSERT statements? Paste a JSON array of objects, a single object or NDJSON into the converter above, or open a .json or .ndjson file. Pick SQL Server, PostgreSQL, MySQL, Oracle or SQLite, set the table name, then copy or download the script. Each top-level key becomes a column and each object becomes one row, with column types inferred from every value. ### What happens to nested objects and arrays? They are not flattened. Each nested object or array is written as compact JSON text into one column, typed json on SQL Server, jsonb on PostgreSQL and JSON on MySQL, Oracle and SQLite. Jam SQL Studio's Data Import maps JSON files the same way, and you can read single fields later with JSON\_VALUE, the ->> operator or json\_extract. ### Can I convert mongoexport output? Yes. mongoexport writes one document per line by default and a single array with --jsonArray; both are accepted. The converter unwraps MongoDB Extended JSON such as $oid, $date, $numberLong and $numberDecimal into plain values, so \_id becomes a text column and dates become timestamps. Untick the option to keep the wrappers as JSON, which is what the desktop app's Data Import does. ### Are large integer IDs kept exactly? Yes. The converter reads numbers from the raw text instead of through JavaScript's JSON.parse, which would turn 7212481950938146817 into 7212481950938147000. BIGINT and DECIMAL values keep every digit in the generated literals on all five engines, and the page tells you which columns held such values. ### Is my JSON uploaded anywhere? No. The JSON is parsed and converted by JavaScript running in this page, kept in memory only, and never sent to a server, put in the URL or written to browser storage. Analytics record which sample or engine you used, the input shape, a row-count bucket, whether Extended JSON was unwrapped, that you opened a file, and whether you copied or downloaded the SQL, never keys, values, table names or file names. ### How many rows can I convert at once? Up to 5 MiB of JSON and 20,000 rows per run. With more rows the converter writes the first 20,000 and reports the total, for example the first 20,000 of 35,000 rows. For bigger files, Jam SQL Studio's Data Import reads JSON and NDJSON from disk as a stream and inserts the rows directly; the free Personal licence imports up to 100,000 rows per run. ### Why are PostgreSQL and MySQL values written as quoted text? The converter uses the same literals Jam SQL Studio writes in its script mode: PostgreSQL and MySQL get quoted text such as '1001', and the server casts it to the column type. An integer column stores 1001, and BIGINT or NUMERIC values keep every digit because they never pass through a floating-point number. ## Loading JSON into a real database? Jam SQL Studio is a free desktop SQL client for SQL Server, PostgreSQL, MySQL, Oracle and SQLite. Its Data Import wizard loads JSON, NDJSON, CSV and Excel files into new or existing tables, and the grid lets you browse and filter the JSON columns afterwards. [Download Free](/#download) [Data Import Docs](/docs/import-data/) Free for personal use • No account required • Mac, Windows, Linux ## More free SQL tools [ ### CSV to SQL Converter Paste CSV or spreadsheet data and get INSERT statements or a CREATE TABLE script with inferred column types. ](/tools/csv-to-sql/)[ ### CSV to JSON Converter Paste or upload CSV and get a pretty-printed JSON array or NDJSON, with numbers and booleans inferred per column. ](/tools/csv-to-json/)[ ### Excel to SQL Converter Upload an .xlsx workbook and get INSERT statements or a CREATE TABLE script, with a sheet picker. ](/tools/excel-to-sql/)