What is the SQL to JSON?
Seed files, database dumps and test fixtures are often written as SQL INSERT statements. When you need the same data in an API mock, a JavaScript app or a NoSQL database, converting it by hand is slow and error-prone. SQL to JSON reads the INSERT statements and gives you an array of objects, one per row, with the column names as keys.
It handles multi-row VALUES lists, MySQL INSERT … SET, quoted names in backticks, "double quotes" or [brackets], escaped quotes, NULL, TRUE and FALSE, and negative and decimal numbers. When an INSERT has no column list, it takes the column names from a CREATE TABLE statement in the same input. Function calls such as NOW() are kept as text.
How to use it
- Paste INSERT statements or a .sql dump, or click Example.
- Choose the output shape: an array for one table, or grouped by table name.
- Choose whether numbers become JSON numbers or stay as text.
- Copy or download the JSON.
Example
INSERT INTO users (id, name, active) VALUES (1, 'Asha', TRUE), (2, 'O''Brien', FALSE);
[
{ "id": 1, "name": "Asha", "active": true },
{ "id": 2, "name": "O'Brien", "active": false }
]Features
- Multi-row inserts. VALUES (…), (…), (…) and INSERT … SET, for MySQL, PostgreSQL, SQLite and SQL Server.
- Column names from CREATE TABLE. INSERTs without a column list use the table definition in the same input.
- Correct values. Escaped quotes, NULL, TRUE/FALSE, negatives and decimals; big integers kept exact as text.
- Many tables. Group rows by table name when the input inserts into several tables.
- Dump friendly. Skips comments, SET, LOCK TABLES and other statements in mysqldump output.
- Private. Your data is converted in the browser and never uploaded.
Frequently asked questions
What if my INSERT has no column names?
Include the CREATE TABLE statement for that table in the input and its column order is used. Otherwise columns are named column1, column2 and so on.
How are dates and functions handled?
Date strings stay as strings, exactly as written. Function calls and expressions such as NOW() or CURRENT_TIMESTAMP are kept as text, because their value is only known inside the database.
Why are some numbers in quotes?
Integers larger than JavaScript can represent exactly (above 9,007,199,254,740,991) are kept as strings so no digits are lost. Turn off “Numbers as numbers” to keep every value as text.
Can I convert JSON to SQL INSERTs?
Not with this tool yet. To go the other way, the CSV to JSON and JSON to CSV tools can help move data between formats.
Last updated · Free, no sign-up, runs in your browser.