Nested JSON to Relational SQL: One Flag That Replaces Your ETL Script

The Problem: Nested JSON Breaks Your INSERT Statements

Real APIs don't return flat rows. A Stripe webhook has customer with an address object. A GitHub event has repo nested inside payload. A Shopify order has line_items as an array of objects.

Standard JSON-to-SQL tools handle one shape: an array of flat objects. The moment your data has nested objects or arrays of objects, you're back to writing Python scripts with json_normalize(), manual column renaming, and separate INSERT statements for child tables.

That's the gap json2sql --flatten closes. One flag:

• Nested objects (e.g., {"address": {"city": "SF", "zip": "94102"}}) are flattened into the parent row with prefixed column names: address_city, address_zip.
• Nested arrays of objects (e.g., {"items": [{"name": "Widget", "price": 9.99}]}) are extracted into a separate relational table with a foreign key referencing the parent.
• Both patterns are handled in a single pass — no separate scripts, no manual schema design.

Quick Recap: Flat JSON

If you've used json2sql before, you know the basic workflow:

# Flat JSON array → SQL
json2sql convert users.json --dialect postgres

# Output:
# CREATE TABLE "data" (
#   "id" INTEGER,
#   "name" TEXT,
#   "email" TEXT
# );
#
# INSERT INTO "data" ("id", "name", "email")
# VALUES
#   (1, 'Alice', 'alice@example.com'),
#   (2, 'Bob', 'bob@example.com');

That works for flat data. But try it on nested JSON and you get NULL placeholders where your nested data should be — or the tool just errors out. Let's see what --flatten does instead.

Pattern 1: Nested Objects → Prefixed Columns

Here's a common shape: an API response where each record has a nested object.

// customers.json
[
  {
    "id": 1,
    "name": "Alice Chen",
    "email": "alice@acme.com",
    "address": {
      "city": "San Francisco",
      "state": "CA",
      "zip": "94102"
    }
  },
  {
    "id": 2,
    "name": "Bob Martinez",
    "email": "bob@beta.io",
    "address": {
      "city": "Austin",
      "state": "TX",
      "zip": "73301"
    }
  }
]

Run json2sql with --flatten:

json2sql convert customers.json --flatten --dialect postgres

Output:

CREATE TABLE "data" (
  "id" INTEGER,
  "name" TEXT,
  "email" TEXT,
  "address_city" TEXT,
  "address_state" TEXT,
  "address_zip" TEXT
);

INSERT INTO "data" ("id", "name", "email", "address_city", "address_state", "address_zip")
VALUES
  (1, 'Alice Chen', 'alice@acme.com', 'San Francisco', 'CA', '94102'),
  (2, 'Bob Martinez', 'bob@beta.io', 'Austin', 'TX', '73301');

This is exactly what you'd do manually in a migration script — except json2sql does it in 200ms for any JSON shape, not just this one.

Pattern 2: Nested Arrays → Separate Relational Tables

Nested arrays of objects are the harder case. A flat approach would either lose the data or produce duplicate parent rows. json2sql extracts them into a proper child table with a foreign key.

// orders.json
[
  {
    "id": 101,
    "customer": "Alice Chen",
    "items": [
      {"sku": "WDG-001", "name": "Widget", "price": 9.99, "qty": 3},
      {"sku": "GDG-002", "name": "Gadget", "price": 24.99, "qty": 1}
    ]
  },
  {
    "id": 102,
    "customer": "Bob Martinez",
    "items": [
      {"sku": "WDG-001", "name": "Widget", "price": 9.99, "qty": 5}
    ]
  }
]

json2sql convert orders.json --flatten --dialect postgres --table orders

Output — two tables with a foreign key relationship:

CREATE TABLE "orders_items" (
  "orders_id" INTEGER,
  "sku" TEXT,
  "name" TEXT,
  "price" DOUBLE PRECISION,
  "qty" INTEGER
);

CREATE TABLE "orders" (
  "id" INTEGER,
  "customer" TEXT,
  "items" TEXT
);

INSERT INTO "orders" ("id", "customer", "items")
VALUES
  (101, 'Alice Chen', NULL),
  (102, 'Bob Martinez', NULL);

INSERT INTO "orders_items" ("orders_id", "sku", "name", "price", "qty")
VALUES
  (101, 'WDG-001', 'Widget', 9.99, 3),
  (101, 'GDG-002', 'Gadget', 24.99, 1),
  (102, 'WDG-001', 'Widget', 9.99, 5);

Pattern 3: Both Patterns in One Document

Real API responses mix both patterns. Here's a Stripe-like webhook payload:

// webhooks.json
[
  {
    "id": "evt_1234",
    "type": "invoice.paid",
    "created": 1716800000,
    "customer": {
      "name": "Acme Corp",
      "email": "billing@acme.com",
      "country": "US"
    },
    "lines": [
      {"description": "Pro Plan", "amount": 4999, "currency": "usd"},
      {"description": "Extra Seats", "amount": 999, "currency": "usd"}
    ]
  }
]

json2sql convert webhooks.json --flatten --dialect postgres --table events

Output:

CREATE TABLE "events_lines" (
  "events_id" TEXT,
  "description" TEXT,
  "amount" INTEGER,
  "currency" TEXT
);

CREATE TABLE "events" (
  "id" TEXT,
  "type" TEXT,
  "created" INTEGER,
  "customer_name" TEXT,
  "customer_email" TEXT,
  "customer_country" TEXT,
  "lines" TEXT
);

INSERT INTO "events" ("id", "type", "created", "customer_name", "customer_email", "customer_country", "lines")
VALUES ('evt_1234', 'invoice.paid', 1716800000, 'Acme Corp', 'billing@acme.com', 'US', NULL);

INSERT INTO "events_lines" ("events_id", "description", "amount", "currency")
VALUES
  ('evt_1234', 'Pro Plan', 4999, 'usd'),
  ('evt_1234', 'Extra Seats', 999, 'usd');

Schema-Only Mode: Design Before You Load

If you want to review the generated schema before inserting data, use --schema-only:

json2sql convert webhooks.json --flatten --schema-only --dialect postgres --table events

This outputs only the CREATE TABLE statements — no INSERT. Use it to:

• Verify the schema matches your existing database structure
• Adjust column names or types before loading
• Generate migration files for review in a PR

Multi-Dialect Output

json2sql generates type-correct SQL for three dialects. The same nested JSON produces different DDL depending on your target:

JSON Type	PostgreSQL	MySQL	SQLite
string	TEXT	VARCHAR(255)	TEXT
integer	INTEGER	INT	INTEGER
float	DOUBLE PRECISION	DOUBLE	REAL
boolean	BOOLEAN	TINYINT(1)	INTEGER
Insert style	Multi-row	Multi-row	One-row-per-INSERT
Identifier quotes	"col"	`col`	"col"

# Same data, different targets
json2sql convert data.json --flatten --dialect postgres -o seed_pg.sql
json2sql convert data.json --flatten --dialect mysql -o seed_mysql.sql
json2sql convert data.json --flatten --dialect sqlite -o seed_sqlite.sql

CI/CD Pipeline: JSON Fixtures → Test Database

The most common production use case: seed a test database from JSON fixture files as part of your CI pipeline.

GitHub Actions Example

name: Integration Tests
on: [push, pull_request]

jobs:
  test:
    runs-on: ubuntu-latest
    services:
      postgres:
        image: postgres:16
        env:
          POSTGRES_DB: test_db
          POSTGRES_USER: postgres
          POSTGRES_PASSWORD: postgres
        ports:
          - 5432:5432
        options: >-
          --health-cmd pg_isready
          --health-interval 10s
          --health-timeout 5s
          --health-retries 5

    steps:
      - uses: actions/checkout@v4

      - name: Install json2sql
        run: pip install json2sql-cli

      - name: Generate SQL from fixtures
        run: |
          json2sql convert tests/fixtures/orders.json \
            --flatten --dialect postgres --table orders \
            -o tests/seed_orders.sql

          json2sql convert tests/fixtures/customers.json \
            --flatten --dialect postgres --table customers \
            -o tests/seed_customers.sql

      - name: Seed test database
        env:
          PGHOST: localhost
          PGDATABASE: test_db
          PGUSER: postgres
          PGPASSWORD: postgres
        run: |
          psql -f tests/seed_customers.sql
          psql -f tests/seed_orders.sql

      - name: Run integration tests
        run: pytest tests/integration/

Stdin Pipe for Inline Data

# Pipe from curl (e.g., seed from a live API response)
curl -s https://api.example.com/users | json2sql convert --flatten --dialect postgres --table users

# Pipe from jq (transform then convert)
cat data.json | jq '.records' | json2sql convert --flatten --dialect mysql --table records

When to Use --flatten vs. a Custom Script

Scenario	json2sql --flatten	Custom Python script
One-off data import	30 seconds	30+ minutes
CI test database seeding	One command in YAML	Maintain a script + deps
Schema exploration / prototyping	--schema-only	Write, run, iterate
Complex joins / denormalization	—	Better (arbitrary logic)
Multiple nested levels (3+ deep)	Flattens one level deep	Better (recursive control)

Install and Try It Now

# Install
pip install json2sql-cli

# Basic flat conversion
json2sql convert data.json

# Nested JSON → relational tables
json2sql convert nested.json --flatten --dialect postgres --table my_table

# Schema-only preview
json2sql convert nested.json --flatten --schema-only

Also available via Homebrew and Scoop:

# macOS / Linux
brew tap Coding-Dev-Tools/tap
brew install json2sql

# Windows
scoop bucket add Coding-Dev-Tools https://github.com/Coding-Dev-Tools/scoop-bucket
scoop install json2sql

What's Next

json2sql is one of 11 CLI tools in the DevForge suite. If you work with JSON data in pipelines, also check out:

• DataMorph — batch-convert between CSV, JSON, YAML, TOML, and more
• API Contract Guardian — catch breaking API changes before they reach production
• SchemaForge — generate SQLAlchemy models and Drizzle schemas from database introspection

Star json2sql on GitHub