JSON to SQL: Convert JSON Arrays to SQL Tables

Why Convert JSON to SQL?

JSON and relational databases are complementary tools. You might need to convert JSON to SQL when: - Migrating from a NoSQL store (MongoDB, DynamoDB, Firestore) to PostgreSQL or MySQL - Loading fixture or seed data from a JSON file into a relational database - Prototyping a database schema from an API response you just received - Importing JSON exports from analytics tools, BI platforms, or external APIs

Converting JSON to SQL generates both the table structure (CREATE TABLE) and the data rows (INSERT INTO) in one step — reducing a manual, error-prone process to a paste-and-run workflow.

Basic Example

[
  {"id": 1, "name": "Ravi",  "city": "Surat",     "active": true,  "score": 9.5},
  {"id": 2, "name": "Priya", "city": "Ahmedabad", "active": false, "score": 8.2},
  {"id": 3, "name": "Arjun", "city": "Vadodara",  "active": true,  "score": 9.1}
]

CREATE TABLE "users" (
  "id"     INTEGER,
  "name"   VARCHAR(255),
  "city"   VARCHAR(255),
  "active" BOOLEAN,
  "score"  NUMERIC
);

INSERT INTO "users" ("id", "name", "city", "active", "score")
VALUES (1, 'Ravi', 'Surat', TRUE, 9.5);
INSERT INTO "users" ("id", "name", "city", "active", "score")
VALUES (2, 'Priya', 'Ahmedabad', FALSE, 8.2);
INSERT INTO "users" ("id", "name", "city", "active", "score")
VALUES (3, 'Arjun', 'Vadodara', TRUE, 9.1);

Type Inference Rules

The converter analyzes all values in each column across all rows and chooses the most appropriate SQL type:

SQL Dialect Differences

PostgreSQL: - Identifiers: double quotes ("column") - Booleans: TRUE / FALSE - Nested JSON: JSONB (binary, indexed) - Auto-increment: SERIAL or GENERATED ALWAYS AS IDENTITY

MySQL / MariaDB: - Identifiers: backticks (\column\`) - Booleans: TRUE / FALSE (stored as TINYINT) - Nested JSON: JSON type (MySQL 5.7.8+) - Auto-increment: AUTO_INCREMENT`

SQLite: - Identifiers: double quotes - No native boolean — use INTEGER (0/1) - No native JSON type — use TEXT - Auto-increment: INTEGER PRIMARY KEY AUTOINCREMENT

SQL Server (T-SQL): - Identifiers: square brackets ([column]) - Booleans: BIT (0 or 1) - JSON stored as NVARCHAR(MAX) (SQL Server 2016+) - Auto-increment: IDENTITY(1,1)

Adding Production Constraints After Generation

The generated SQL is a starting point. Add constraints based on your business rules:

-- PostgreSQL — production-ready version
CREATE TABLE users (
  id         SERIAL PRIMARY KEY,
  email      VARCHAR(255) NOT NULL UNIQUE,
  name       VARCHAR(100) NOT NULL,
  city       VARCHAR(100),
  active     BOOLEAN NOT NULL DEFAULT TRUE,
  score      NUMERIC(5, 2) CHECK (score >= 0 AND score <= 10),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Add indexes after loading data
CREATE INDEX idx_users_email  ON users (email);
CREATE INDEX idx_users_active ON users (active) WHERE active = TRUE;

The generator cannot know: which field is the primary key, which fields are unique, foreign key relationships, default values, or field-level constraints. Add these manually.

Handling Missing Keys and Null Values

When JSON objects in the array have different keys, the converter uses the union of all keys as columns and inserts NULL for absent values:

[
  {"id": 1, "name": "Ravi",  "phone": "9876543210"},
  {"id": 2, "name": "Priya", "email": "priya@example.com"}
]

CREATE TABLE "data" (
  "id"    INTEGER,
  "name"  VARCHAR(255),
  "phone" VARCHAR(255),
  "email" VARCHAR(255)
);

INSERT INTO "data" ("id", "name", "phone", "email")
VALUES (1, 'Ravi', '9876543210', NULL);

INSERT INTO "data" ("id", "name", "phone", "email")
VALUES (2, 'Priya', NULL, 'priya@example.com');

Loading Large Datasets Efficiently

Single INSERT statements per row are slow for thousands of records. Use faster alternatives:

COPY users (id, name, city, active, score)
FROM '/path/to/data.csv'
WITH (FORMAT csv, HEADER true, NULL 'null');

INSERT INTO users (id, name, city) VALUES
  (1, 'Ravi', 'Surat'),
  (2, 'Priya', 'Ahmedabad'),
  (3, 'Arjun', 'Vadodara');
-- Single statement, much faster than individual INSERTs

LOAD DATA LOCAL INFILE '/path/to/data.csv'
INTO TABLE users
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS (id, name, city, active, score);

import { Pool } from "pg";
const pool = new Pool({ connectionString: process.env.DATABASE_URL });

async function bulkInsert(rows) {
  const client = await pool.connect();
  try {
    await client.query("BEGIN");
    for (const row of rows) {
      await client.query(
        'INSERT INTO users (id, name, city, active) VALUES ($1, $2, $3, $4)',
        [row.id, row.name, row.city, row.active]
      );
    }
    await client.query("COMMIT");
  } catch (e) {
    await client.query("ROLLBACK");
    throw e;
  } finally {
    client.release();
  }
}

JSON Columns in Relational Databases

Modern relational databases support native JSON column types for storing semi-structured data alongside regular columns:

-- PostgreSQL JSONB — binary JSON, indexable
CREATE TABLE events (
  id         SERIAL PRIMARY KEY,
  event_type VARCHAR(50) NOT NULL,
  user_id    INTEGER NOT NULL,
  payload    JSONB,                    -- semi-structured data
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Query inside the JSONB column
SELECT * FROM events
WHERE payload->>'status' = 'completed'
  AND (payload->>'amount')::numeric > 100;

-- GIN index for JSONB queries
CREATE INDEX idx_events_payload ON events USING GIN (payload);

Best Practices

1. Test on 5-10 rows first before loading thousands
2. Wrap INSERTs in a transaction — rollback cleanly if any row fails
3. Add indexes after bulk loading — indexes slow inserts significantly
4. Use COPY or LOAD DATA for large files — orders of magnitude faster than individual INSERTs
5. Add NOT NULL constraints for required fields after reviewing the data
6. Use parameterized queries in application code — never string interpolation

Use JSONKit's JSON to SQL tool to generate CREATE TABLE and INSERT statements for any JSON array — choose your SQL dialect and table name, then copy and run.