Inserting Nested JSON Objects and Arrays

Nested Objects as JSON Strings

When your JSON data contains nested objects or arrays, the bulk INSERT tool serializes them as JSON strings. This is ideal for databases that support JSON columns (PostgreSQL JSONB, MySQL JSON).

Example JSON

[
  {
    "id": 1,
    "name": "Alice",
    "metadata": { "role": "admin", "permissions": ["read", "write"] },
    "tags": ["vip", "early-adopter"]
  },
  {
    "id": 2,
    "name": "Bob",
    "metadata": { "role": "user", "permissions": ["read"] },
    "tags": ["standard"]
  }
]

Generated SQL

INSERT INTO "users" ("id", "name", "metadata", "tags")
VALUES
  (1, 'Alice', '{"role":"admin","permissions":["read","write"]}', '["vip","early-adopter"]'),
  (2, 'Bob', '{"role":"user","permissions":["read"]}', '["standard"]');

Using with JSONB (PostgreSQL)

For PostgreSQL, you can cast the text values to JSONB in your actual query:

INSERT INTO "users" ("id", "name", "metadata", "tags")
VALUES
  (1, 'Alice', '{"role":"admin"}'::jsonb, '["vip"]'::jsonb);

Column Type Inference

Columns with nested objects are inferred as TEXT type. If you need JSON or JSONB, modify the CREATE TABLE output manually or alter the table after creation.

Special Characters in JSON Strings

The serialized JSON is properly escaped for SQL:

• Single quotes within JSON strings are doubled ('')
• The JSON structure is preserved exactly as serialized by JSON.stringify()
• Unicode characters are passed through unchanged