Flattening Nested JSON Objects for SQL INSERT

Nested JSON to Flat SQL Rows

Relational databases store data in flat rows, but JSON often contains nested objects. Converting nested JSON to SQL requires a flattening strategy that preserves data relationships.

Example JSON

{
  "id": 1,
  "name": "Alice",
  "address": {
    "street": "123 Main St",
    "city": "Portland",
    "state": "OR",
    "zip": "97201"
  },
  "preferences": {
    "theme": "dark",
    "language": "en"
  }
}

Strategy 1: Dot-Notation Flattening

Flatten nested keys into column names using underscores or dots:

INSERT INTO users (id, name, address_street, address_city, address_state, address_zip, preferences_theme, preferences_language)
VALUES (1, 'Alice', '123 Main St', 'Portland', 'OR', '97201', 'dark', 'en');

This is the simplest approach but can produce very wide tables with many columns.

Strategy 2: Separate Table Insertion

Normalize the data into multiple tables with foreign keys:

INSERT INTO users (id, name) VALUES (1, 'Alice');

INSERT INTO addresses (user_id, street, city, state, zip)
VALUES (1, '123 Main St', 'Portland', 'OR', '97201');

INSERT INTO user_preferences (user_id, theme, language)
VALUES (1, 'dark', 'en');

Strategy 3: JSON Column Storage

Modern databases support JSON columns:

-- PostgreSQL
INSERT INTO users (id, name, address, preferences)
VALUES (1, 'Alice',
  '{"street":"123 Main St","city":"Portland","state":"OR","zip":"97201"}'::JSONB,
  '{"theme":"dark","language":"en"}'::JSONB);

Which Strategy to Choose?

Strategy	Best for	Drawback
Flattening	Simple nesting, one level deep	Wide tables, naming collisions
Separate tables	Deep nesting, many-to-one relationships	Multiple statements, foreign keys
JSON column	Flexible schema, variable structures	Harder to query, index limitations

Depth Limits

For deeply nested JSON (3+ levels), flattening becomes impractical. The converter should offer a configurable depth limit, flattening up to N levels and storing deeper structures as JSON column values.