Convert JSON to SQL

Q: How do I convert JSON to SQL?

Drop your file on the converter at the top of this page and choose your database: PostgreSQL, MySQL, MariaDB, SQLite, SQL Server or Oracle. Each one gets the types it supports. Download the script and run it in your database.

Q: What shape does the JSON file need?

An array of objects, an object that holds that array under a key such as data, or one object per line (NDJSON). Each key becomes a column.

Q: Which types does it use in each database?

Each database gets its own: yes and no values are boolean in PostgreSQL, tinyint(1) in MySQL and MariaDB, bit in SQL Server and NUMBER(1) in Oracle. The table on this page lists every kind of value.

Q: Does it keep leading zeros?

Yes. A code such as 000481 stays text in every database, written as '000481', so no zero is lost.

Q: Can it load the data without a script?

Yes. The command line loads into SQLite for free, and with IntoSQL Pro the desktop app and the command line load into PostgreSQL, MySQL and MariaDB. For SQL Server and Oracle, run the script.

Q: How big can the JSON file be?

NDJSON, one object per line, is read as it streams in, so files of several gigabytes work in the browser. A JSON document is read whole, so its limit is the memory the browser gives this page. The desktop app and the command line take files of any size.

Drop your JSON file and pick your database to get a SQL script with CREATE TABLE and INSERT statements. Each column gets a type, and every fix is listed with its row and column.

OutputHeader rowDates

4 rows converted, 2 fixes, none rejected

Your fileSQL2 fixes

The first rows of pedidos_2024.json, with each fix marked
Row
2	000481	Peña Muñoz	2024-02-03T10:15:00Z	1234.5	true
3	000482	José Ángel Ruiz	2024-02-15T16:40:00Z	99.9	false	Entrega en la tarde
4	000483	Zoë Ortíz	2024-02-29T09:05:00Z	12	true
5	000484	Ñuño Castañeda	2024-03-11T13:30:00Z	980.75	sí	Factura con "RFC"
6
7
8
9
10
11

-- Written by IntoSQL for PostgreSQL.

SET client_encoding = 'UTF8';
BEGIN;

CREATE TABLE pedidos_2024 (
    pedido text NOT NULL,
    cliente text NOT NULL,
    fecha timestamptz NOT NULL,
    total numeric(6,2) NOT NULL,
    pagado boolean NOT NULL,
    notas text
);

INSERT INTO pedidos_2024 (pedido, cliente, fecha, total, pagado, notas) VALUES
('000481', 'Peña Muñoz', '2024-02-03 10:15:00+00:00', 1234.5, TRUE, NULL),
('000482', 'José Ángel Ruiz', '2024-02-15 16:40:00+00:00', 99.9, FALSE, 'Entrega en la tarde'),
('000483', 'Zoë Ortíz', '2024-02-29 09:05:00+00:00', 12, TRUE, NULL),
('000484', 'Ñuño Castañeda', '2024-03-11 13:30:00+00:00', 980.75, TRUE, 'Factura con "RFC"');

COMMIT;

Fixes

Leading zeros kept in "pedido": 000481 stays 000481, stored as text.
"pagado" holds yes and no words (sí), stored as boolean.

Your file is converted on this computer and never uploaded.

Other samples:

One JSON file, six databases

The sample above, pedidos_2024.json, and the type each of its columns gets in each database.

In the file	First value	PostgreSQL	MySQL	MariaDB	SQLite	SQL Server	Oracle
pedido	000481	text	varchar(8)	varchar(8)	TEXT	nvarchar(8)	VARCHAR2(8 CHAR)
cliente	Peña Muñoz	text	varchar(16)	varchar(16)	TEXT	nvarchar(16)	VARCHAR2(16 CHAR)
fecha	2024-02-03T10:15:00Z	timestamptz	datetime	datetime	DATETIME	datetimeoffset	TIMESTAMP WITH TIME ZONE
total	1234.5	numeric(6,2)	decimal(6,2)	decimal(6,2)	NUMERIC(6,2)	decimal(6,2)	NUMBER(6,2)
pagado	true	boolean	tinyint(1)	tinyint(1)	BOOLEAN	bit	NUMBER(1)
notas	-	text	varchar(32)	varchar(32)	TEXT	nvarchar(32)	VARCHAR2(32 CHAR)

Load the script into your database

The download is a plain .sql file. Each database runs it with its own tools.

PostgreSQL
$psql -v ON_ERROR_STOP=1 -d mydb -f pedidos_2024.postgresql.sql
ON_ERROR_STOP makes psql stop at the first error. The script runs in one transaction, so a failed load leaves the database as it was.
MySQL
$mysql --default-character-set=utf8mb4 mydb < pedidos_2024.mysql.sql
The script sets utf8mb4 itself; the flag makes the client send it the same way, so accents arrive intact.
MariaDB
$mariadb --default-character-set=utf8mb4 mydb < pedidos_2024.mariadb.sql
The script sets utf8mb4 itself; the flag makes the client send it the same way, so accents arrive intact.
SQLite
$sqlite3 pedidos_2024.db < pedidos_2024.sqlite.sql
The database file is created if it doesn't exist. The converter also downloads a ready .sqlite database, with no command at all.
SQL Server
$sqlcmd -S localhost -d mydb -f 65001 -i pedidos_2024.sql_server.sql
-f 65001 reads the file as UTF-8, so accents and ñ arrive as written.
Oracle
$sqlplus user@//localhost/mydb @pedidos_2024.oracle.sql
The script turns off substitution variables (SET DEFINE OFF), so an ampersand in your data stays an ampersand.

The command line loads files straight into a SQLite database for free. With IntoSQL Pro, it and the desktop app also load into PostgreSQL, MySQL and MariaDB. For SQL Server and Oracle, run the script.

What to know about JSON files and each database

What IntoSQL reads in JSON files

The file can be an array of objects, an object that holds that array under a key such as data, or one object per line (NDJSON).
Each key becomes a column. Objects and arrays inside a value are kept as JSON text.
Numbers keep their exact digits, and ISO 8601 dates become dates or timestamps.

What to know about each database

Pick the database once the file is open: each one gets the types it supports.

How each kind of value is stored in each database

Value	PostgreSQL	MySQL	MariaDB	SQLite	SQL Server	Oracle
Yes and no	boolean	tinyint(1)	tinyint(1)	BOOLEAN	bit	NUMBER(1)
Whole numbers	integer	int	int	INTEGER	int	NUMBER(10)
Large whole numbers	bigint	bigint	bigint	INTEGER	bigint	NUMBER(19)
Decimals	numeric(p,s)	decimal(p,s)double	decimal(p,s)double	NUMERIC(p,s)TEXT	decimal(p,s)	NUMBER(p,s)
Floating-point numbers	double precision	double	double	REAL	float	BINARY_DOUBLE
Dates	date	date	date	DATE	date	DATE
Times of day	time	time(6)time	time(6)time	TIME	time	INTERVAL DAY(0) TO SECOND
Dates with a time	timestamp	datetime(6)datetime	datetime(6)datetime	DATETIME	datetime2	TIMESTAMP
Dates with a time and offset	timestamptz	datetime(6)datetime	datetime(6)datetime	DATETIME	datetimeoffset	TIMESTAMP WITH TIME ZONE
Text	text	varchar(n)textmediumtextlongtext	varchar(n)textmediumtextlongtext	TEXT	nvarchar(n)nvarchar(max)	VARCHAR2(n CHAR)CLOB

Questions about JSON to SQL

Not answered here? Write to us from the contact page and a person replies. Contact page

How do I convert JSON to SQL?

Drop your file on the converter at the top of this page and choose your database: PostgreSQL, MySQL, MariaDB, SQLite, SQL Server or Oracle. Each one gets the types it supports. Download the script and run it in your database.

What shape does the JSON file need?

An array of objects, an object that holds that array under a key such as data, or one object per line (NDJSON). Each key becomes a column.

Which types does it use in each database?

Each database gets its own: yes and no values are boolean in PostgreSQL, tinyint(1) in MySQL and MariaDB, bit in SQL Server and NUMBER(1) in Oracle. The table on this page lists every kind of value.

Does it keep leading zeros?

Yes. A code such as 000481 stays text in every database, written as '000481', so no zero is lost.

Can it load the data without a script?

Yes. The command line loads into SQLite for free, and with IntoSQL Pro the desktop app and the command line load into PostgreSQL, MySQL and MariaDB. For SQL Server and Oracle, run the script.

How big can the JSON file be?

NDJSON, one object per line, is read as it streams in, so files of several gigabytes work in the browser. A JSON document is read whole, so its limit is the memory the browser gives this page. The desktop app and the command line take files of any size.