JSON to SQL Converter

Typical Use Cases

Scenario 1: Backend Developers — You receive a JSON-formatted user list from an API (e.g., [{"id":1,"name":"John Doe"},{"id":2,"name":"Jane Smith"}]) and need to batch insert them into a MySQL users table. Manually writing INSERT statements is tedious and error-prone. By pasting the JSON into this tool, you instantly get executable SQL.

Scenario 2: Data Analysts — While analyzing customer data, you need to write a list of parameters (a JSON array) from a configuration file into a database as an initialization script. Using this tool, you can copy the generated INSERT statements directly into your SQL client in one step.

Scenario 3: Website Migration — Export JSON-formatted data (like articles or categories) from an old system and use the tool to generate INSERT statements to import directly into the new database, avoiding the need to write cumbersome intermediate scripts.

Principles & Algorithm

The core logic is simple: it takes the keys of a JSON object as the SQL table column names, and the corresponding values as the values in the INSERT statement. If the JSON is an array, it generates one INSERT statement per element; if it's a single object, it generates a single statement. The tool automatically handles data types: numbers are written directly, strings are wrapped in single quotes (with internal single quotes escaped), booleans are converted to 1 or 0, and null values are converted to SQL's NULL.

Suppose you have an object like {"name": "O'Brien", "age": 30}, the SQL will become: INSERT INTO mytable (name, age) VALUES ('O''Brien', 30) — the single quote is correctly escaped.

Complete Example: Converting a JSON Array to INSERT Statements

Paste the following data into the "JSON Input" box on the left side of the tool:

[
  {"id": 1, "name": "David", "score": 85.5},
  {"id": 2, "name": "Mary", "score": 92.0},
  {"id": 3, "name": "Linda", "score": null}
]

Then, enter students in the "Table Name" input box. Click the "Convert" button, and the output area on the right will display:

INSERT INTO students (id, name, score) VALUES (1, 'David', 85.5);
INSERT INTO students (id, name, score) VALUES (2, 'Mary', 92.0);
INSERT INTO students (id, name, score) VALUES (3, 'Linda', NULL);

Interpretation: The generated SQL can be executed directly in MySQL or PostgreSQL (assuming the students table already exists). Note that NULL does not need quotes; the tool handles this automatically. Each statement ends with a semicolon for easy copy-pasting.

More Examples: Handling Booleans and Single Quotes

Suppose the JSON is:

[
  {"title": "It's a test", "published": true},
  {"title": "Say "Hello" ", "published": false}
]

Enter articles as the table name, and the conversion result is:

INSERT INTO articles (title, published) VALUES ('It''s a test', 1);
INSERT INTO articles (title, published) VALUES ('Say "Hello" ', 0);

Note: Single quotes within strings are escaped as two single quotes (standard SQL practice), while double quotes remain unchanged. The boolean value true becomes 1, and false becomes 0. If your database does not support this boolean representation, you can adjust it manually.

How to Interpret the Results

The SQL generated by the tool generally conforms to the ANSI SQL standard and can be used directly in databases like MySQL, PostgreSQL, and SQLite. Check the following points to ensure accuracy:

Table and Column Names: Ensure the target table exists in your database and the column names match the JSON keys (case-sensitive).
Data Types: Numbers are written directly, strings have single quotes, and NULL has no quotes. If a column is an integer and you provide a decimal value, most databases will convert it automatically.
Batch Processing: If the JSON array has hundreds of rows, the tool will generate hundreds of individual INSERT statements. Execution efficiency might not be optimal; you can manually merge them into a single INSERT INTO ... VALUES (...), (...) format (the tool does not support merging, so you need to copy, paste, and adjust it yourself).

Common Mistakes

Forgetting the Table Name: The tool uses table as the default table name, which can easily result in invalid statements. Always enter the correct name in the table name box.
Inconsistent JSON Object Fields: If objects in an array contain different sets of keys (e.g., the first has name but the second doesn't), the tool takes the union of all keys and fills missing values with NULL. This may cause insertion failures if the target table does not allow NULLs.
Unflattened Nested Objects: The tool only processes single-level JSON objects. If a value is an object (e.g., {"address": {"city": "New York"}}), the generated SQL will literally write [object Object], which is incorrect data. You need to flatten nested objects into flat keys first.
Unrecognized Special Characters: Although the tool handles single quotes, characters like backslashes or special Unicode characters might still cause issues depending on your database settings. It is recommended to test on a small sample first.

Boundaries & Limitations

1. Only Processes Flat JSON: Nested objects or nested arrays are not supported. If you need a multi-level structure, please flatten it with other tools first. 2. Input Size Limits: In a browser environment, the length of the JSON string passed to the tool is limited by memory. It is recommended not to exceed 10MB per conversion, otherwise the page may slow down or crash. 3. Output Syntax Leans Towards MySQL: While the basic syntax is universal, MySQL represents booleans as 1/0, whereas PostgreSQL can use TRUE/FALSE keywords. If you use PostgreSQL, you need to manually replace 1 and 0 with boolean values. 4. No DDL Support: The tool only generates INSERT statements and will not create tables. You must manually create the table structure first.

Frequently Asked Questions (FAQ)

Q: Can the tool process a single JSON object?
Yes. If the input is {"a":1,"b":2}, it will generate a single INSERT statement.

Q: How are dates in JSON handled?
Date strings are treated as regular strings (wrapped in single quotes). If the column in your database is a date type, the string format must match the database's format (e.g., '2023-10-01').

Q: Can it generate REPLACE INTO or UPDATE statements?
Currently, it only generates INSERT statements. Please modify other types of SQL manually.

Q: Why is there no semicolon at the end of the generated SQL?
The tool adds a semicolon to the end of each statement by default, but you can batch copy and add or remove them yourself.

Q: What if other types (like strings) are mixed into the JSON array?
The tool will skip non-object elements (like numbers or strings) and only process objects. If all elements are non-objects, the output will be empty.

Q: Does this tool require an internet connection?
No, all conversions are done locally in your browser. Your data is not uploaded to any server, so you can use it with peace of mind.

Now you can paste your own JSON into the calculator above and try it out.