Convert a JSON Array to Multi-Row INSERT Statements

JSON Arrays to Multi-Row INSERT

When your JSON contains an array of objects, you can generate a single INSERT statement with multiple VALUES tuples. This is more efficient than individual INSERT statements because the database parses the query only once.

Example JSON

[
  { "id": 1, "name": "Alice", "email": "alice@example.com" },
  { "id": 2, "name": "Bob", "email": "bob@example.com" },
  { "id": 3, "name": "Charlie", "email": "charlie@example.com" }
]

Generated SQL

INSERT INTO users (id, name, email) VALUES
  (1, 'Alice', 'alice@example.com'),
  (2, 'Bob', 'bob@example.com'),
  (3, 'Charlie', 'charlie@example.com');

Column Extraction

The converter scans all objects in the array to build a unified set of column names. This handles cases where some objects have keys that others lack. Missing values are filled with NULL.

Inconsistent Keys

[
  { "id": 1, "name": "Alice" },
  { "id": 2, "name": "Bob", "email": "bob@example.com" }
]

INSERT INTO users (id, name, email) VALUES
  (1, 'Alice', NULL),
  (2, 'Bob', 'bob@example.com');

Performance Benefits

Multi-row INSERT is significantly faster than individual statements because:

1. Fewer round trips -- one statement instead of N
2. Single parse -- the database parses the SQL template once
3. Bulk logging -- transaction logs record one operation
4. Index updates -- indexes are updated in batch

Database Limits

Most databases limit the number of values in a single INSERT. MySQL defaults to a 1MB packet size (max_allowed_packet), PostgreSQL has no hard row limit but practical limits around 1000 parameters for prepared statements. The converter can split large arrays into chunked INSERT statements.