Convert JSON to SQL

Why Convert JSON to SQL?

Converting JSON files to SQL INSERT statements is essential for importing data from web APIs, NoSQL databases, or configuration files into relational databases. If you receive data as JSON from a REST API, a MongoDB export, or a front-end application, converting it to SQL lets you load it directly into MySQL, PostgreSQL, SQLite, or any other RDBMS with a simple copy-paste or file import.

This conversion is especially valuable for database seeding during development and testing. Instead of manually writing INSERT statements, you can maintain your seed data as JSON (which is easier to read and edit) and convert it to SQL when needed. It is also useful for data migration between systems -- exporting from a NoSQL database and importing into a relational one.

Our converter analyzes the JSON structure to generate clean, properly escaped SQL. JSON objects become table rows, keys become column names, and values are typed appropriately (strings quoted, numbers unquoted, null preserved). Arrays of objects produce multiple INSERT statements for the same table. Nested objects are flattened using underscore-separated column names (e.g., database_host, database_port).

Key Benefits of Converting JSON to SQL:

• Direct Database Import: Execute the generated SQL immediately in MySQL, PostgreSQL, or SQLite
• Proper Escaping: String values are properly quoted and special characters escaped to prevent SQL injection
• Type Inference: JSON numbers become INTEGER/FLOAT, strings become VARCHAR, booleans become BOOLEAN
• Batch Inserts: JSON arrays of objects generate efficient multi-row INSERT statements
• Schema Generation: Optional CREATE TABLE statements based on JSON key structure
• Data Migration: Move data from NoSQL (MongoDB) to relational databases seamlessly
• Development Seeding: Maintain seed data in JSON and generate SQL for database setup

Practical Examples

Example 1: User Records

Input JSON file (users.json):

[
  {
    "id": 1,
    "name": "Alice Johnson",
    "email": "[email protected]",
    "active": true
  },
  {
    "id": 2,
    "name": "Bob Smith",
    "email": "[email protected]",
    "active": false
  }
]

Output SQL file (users.sql):

CREATE TABLE IF NOT EXISTS users (
  id INTEGER,
  name VARCHAR(255),
  email VARCHAR(255),
  active BOOLEAN
);

INSERT INTO users (id, name, email, active)
VALUES (1, 'Alice Johnson', '[email protected]', TRUE);

INSERT INTO users (id, name, email, active)
VALUES (2, 'Bob Smith', '[email protected]', FALSE);

Example 2: Product Catalog

Input JSON file (products.json):

[
  {
    "sku": "WIDGET-001",
    "name": "Premium Widget",
    "price": 29.99,
    "category": "Tools",
    "in_stock": true
  },
  {
    "sku": "GADGET-042",
    "name": "Super Gadget",
    "price": 49.50,
    "category": "Electronics",
    "in_stock": false
  }
]

Output SQL file (products.sql):

CREATE TABLE IF NOT EXISTS products (
  sku VARCHAR(255),
  name VARCHAR(255),
  price DECIMAL(10,2),
  category VARCHAR(255),
  in_stock BOOLEAN
);

INSERT INTO products (sku, name, price, category, in_stock)
VALUES ('WIDGET-001', 'Premium Widget', 29.99, 'Tools', TRUE);

INSERT INTO products (sku, name, price, category, in_stock)
VALUES ('GADGET-042', 'Super Gadget', 49.50, 'Electronics', FALSE);

Example 3: Nested Configuration to Flat Table

Input JSON file (config.json):

{
  "app_name": "MyService",
  "version": "3.1",
  "database": {
    "host": "db.example.com",
    "port": 5432,
    "name": "production"
  },
  "debug": false
}

Output SQL file (config.sql):

CREATE TABLE IF NOT EXISTS config (
  app_name VARCHAR(255),
  version VARCHAR(50),
  database_host VARCHAR(255),
  database_port INTEGER,
  database_name VARCHAR(255),
  debug BOOLEAN
);

INSERT INTO config (app_name, version, database_host,
  database_port, database_name, debug)
VALUES ('MyService', '3.1', 'db.example.com',
  5432, 'production', FALSE);

Frequently Asked Questions (FAQ)

Q: What is JSON format?

A: JSON (JavaScript Object Notation) is a lightweight data interchange format standardized as RFC 8259 and ECMA-404. It uses key-value pairs in objects (curly braces), ordered lists in arrays (square brackets), and supports strings, numbers, booleans, and null. JSON is the dominant format for web APIs, configuration files (package.json, tsconfig.json), and NoSQL databases like MongoDB.

Q: What is SQL format?

A: SQL (Structured Query Language) is the standard language for relational databases, defined by ISO/IEC 9075. In this context, the converter generates SQL INSERT statements that you can execute in MySQL, PostgreSQL, SQLite, SQL Server, or Oracle to load your JSON data into database tables. The output may also include CREATE TABLE statements for schema definition.

Q: Which databases are compatible with the output?

A: The generated SQL uses standard ANSI syntax compatible with MySQL, MariaDB, PostgreSQL, SQLite, SQL Server, and Oracle. For specific database features (e.g., PostgreSQL's ON CONFLICT or MySQL's INSERT IGNORE), you may need minor manual adjustments, but the core INSERT INTO statements work across all major RDBMS.

Q: How are nested JSON objects handled?

A: Nested JSON objects are flattened into column names using underscore separators. For example, {"database": {"host": "localhost"}} becomes a column named database_host with the value 'localhost'. This produces a flat table structure that is compatible with relational database design.

Q: Are string values properly escaped?

A: Yes, all string values are properly escaped with single quotes. Special characters like apostrophes are doubled (O''Brien) following SQL escaping conventions. This prevents SQL injection and ensures the generated statements execute without syntax errors.

Q: How does the converter handle JSON arrays of objects?

A: When the JSON root is an array of objects (the most common pattern for datasets), each object becomes a separate INSERT statement targeting the same table. The table name is derived from the input filename, and column names come from the union of all object keys across the array.

Q: What happens if my JSON file has syntax errors?

A: If the JSON file contains syntax errors and cannot be parsed, the converter will include the raw content as SQL comments (-- prefixed lines). This ensures you always get output, and you can then fix the JSON and re-convert to get valid SQL statements.