Converting JSON Arrays into Multiple SQL Rows

JSON Arrays to SQL Rows

When a JSON object contains an array field, the relational equivalent is typically a separate table with a foreign key. Converting these arrays requires generating INSERT statements for both the parent and child tables.

Example JSON

{
  "id": 1,
  "name": "Alice",
  "skills": ["Python", "SQL", "Kotlin"],
  "projects": [
    { "name": "API Gateway", "role": "lead" },
    { "name": "Data Pipeline", "role": "contributor" }
  ]
}

Normalized SQL Output

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

-- Scalar array -> child table
INSERT INTO user_skills (user_id, skill) VALUES
  (1, 'Python'),
  (1, 'SQL'),
  (1, 'Kotlin');

-- Object array -> child table
INSERT INTO user_projects (user_id, project_name, role) VALUES
  (1, 'API Gateway', 'lead'),
  (1, 'Data Pipeline', 'contributor');

Alternative: Array Columns

PostgreSQL supports native array types:

INSERT INTO users (id, name, skills)
VALUES (1, 'Alice', ARRAY['Python', 'SQL', 'Kotlin']);

MySQL does not have native array columns; use JSON columns or normalization instead.

Junction Tables for Many-to-Many

If skills are shared across users, a junction table is more appropriate:

INSERT INTO skills (id, name) VALUES (1, 'Python'), (2, 'SQL'), (3, 'Kotlin')
  ON CONFLICT (name) DO NOTHING;

INSERT INTO user_skills (user_id, skill_id) VALUES (1, 1), (1, 2), (1, 3);

UNNEST for Existing Arrays

If the data is already in a PostgreSQL JSON column, you can expand arrays inline:

SELECT id, name, UNNEST(skills) AS skill
FROM users;

Choosing the Right Approach

Scenario	Approach
Simple string arrays, single-user context	Array column (PostgreSQL)
Shared reference data across entities	Junction table
Object arrays with multiple fields	Separate child table
MySQL target	Always normalize to child table