Convert nested JSON into relational SQL tables with json2sql

Real-world JSON is rarely flat. An API response is a user object with an embedded address object and an orders array; a webhook payload nests metadata three levels deep; an event stream wraps fields in data and context objects. If you load that JSON straight into a relational database, you end up either stuffing an entire blob into a single JSON column (unqueryable) or hand-writing a flattening script that breaks the moment the schema shifts. json2sql solves the middle path: give it nested JSON and the --flatten flag, and it generates normalized SQL INSERT statements across multiple relational tables — automatically.

Install

pip install git+https://github.com/Coding-Dev-Tools/json2sql.git

json2sql is not published on public PyPI (publishing is pending), so a bare pip install json2sql-cli will fail — use the git+ command above. It is MIT-licensed, requires only Python 3.10+, and runs fully offline on the free tier — no telemetry, no phone-home, no account required. Homebrew and Scoop are also available: brew tap Coding-Dev-Tools/tap && brew install json2sql-cli or scoop bucket add Coding-Dev-Tools https://github.com/Coding-Dev-Tools/scoop-bucket && scoop install json2sql-cli.

Flatten a nested object into linked tables

Consider a user record where the address is an embedded object:

// user.json
{
  "id": 1,
  "name": "Ada Lovelace",
  "address": {
    "street": "12 Analytical Way",
    "city": "London",
    "zip": "EC1A"
  }
}

Run json2sql with --flatten and a target dialect:

json2sql convert user.json --dialect postgres --flatten -o user.sql

Instead of dumping the address into a JSON column, json2sql creates a normalized address table and references it from the parent user table via a foreign key (address_id). The result is queryable, indexable, and joins cleanly with the rest of your schema.

Turn JSON arrays into multiple INSERT rows

Arrays of objects map naturally to relational rows. Given a payload with an orders array:

// order.json
{
  "id": 1,
  "customer": "Ada Lovelace",
  "orders": [
    { "sku": "A-101", "qty": 2, "price": 19.99 },
    { "sku": "B-204", "qty": 1, "price": 8.50 }
  ]
}

json2sql emits one INSERT per array element into the child orders table, each linked back to its parent record:

json2sql convert order.json --dialect sqlite --flatten -o orders.sql
sqlite3 test.db < orders.sql

Pick your dialect

json2sql emits correct INSERT syntax for three major engines, so the same nested JSON produces portable SQL:

json2sql convert data.json --dialect postgres   # PostgreSQL
json2sql convert data.json --dialect mysql      # MySQL
json2sql convert data.json --dialect sqlite     # SQLite

Use it in a pipeline

json2sql reads from stdin, so it drops into any shell or CI step. Generate seed data for integration tests, or transform a JSON export into INSERTs for a new system:

# Pipe JSON straight into SQL
cat fixtures.json | json2sql convert --dialect postgres --table events --flatten > events.sql

# GitHub Actions: prepare test data before running migrations
json2sql convert fixtures.json --dialect sqlite -o seed.sql
sqlite3 test.db < seed.sql

Why flatten instead of storing JSON blobs?

Storing nested JSON in a single column keeps the write simple but punishes every read: you can't index a nested field, joins become string-parsing exercises, and aggregations need a JSON-aware query engine. Flattening at conversion time — exactly what the --flatten flag does — moves the complexity to a one-off step and leaves you with boring, fast, queryable relational tables. json2sql's type inference auto-detects strings, numbers, booleans, and nulls, so the generated columns come out with sensible types rather than everything landing as TEXT.

Pricing

json2sql is one of eleven CLI tools in the Coding Dev Tools suite. One license covers all of them.

The free tier runs fully offline with no row cap that blocks evaluation on small datasets — install it and flatten your first nested JSON today.

View json2sql on GitHub →