JSON Array ↔ Table

What is JSON Flattening?

JSON flattening (or JSON-to-table conversion) transforms hierarchical JSON data into a flat, tabular format — rows and columns — suitable for spreadsheets, relational databases, and CSV files.

JSON is the dominant data interchange format for web APIs. But JSON’s strength — the ability to represent deeply nested, hierarchical data — becomes a problem when you need to analyze that data in tools designed for flat tables. Excel cannot natively display a nested JSON object. SQL databases store data in rows and columns. CSV files are inherently flat.

JSON-to-table conversion bridges this gap. It takes nested JSON structures and produces a flat representation where each JSON object becomes a row and each key (including nested keys expressed in dot notation) becomes a column.

How Nested Object Handling Works

The core challenge of JSON flattening is deciding how to represent nested structures in a flat format. The standard approach uses dot notation to create column names that encode the original path:

{
  "user": {
    "name": "Alice",
    "address": {
      "city": "Paris",
      "zip": "75001"
    }
  }
}

Flattens to:

user.name	user.address.city	user.address.zip
Alice	Paris	75001

Each level of nesting adds another dot-separated segment to the column name. This preserves the hierarchy in a human-readable way and avoids collisions — if both user.name and company.name exist, they become separate columns.

Depth Limits

For deeply nested JSON (5+ levels), dot-notation column names can become unwieldy (data.results[0].metadata.tags[2].label). Some tools offer a maximum depth parameter — nesting beyond that depth is serialized as a JSON string in a single cell.

Array Handling

Arrays introduce the most complexity in JSON flattening. There are several strategies:

Array of objects (uniform): When every element has the same keys, each element becomes a row. This is the most common and cleanest case — a JSON API returning a list of users converts directly to a table.

[
  {"name": "Alice", "age": 30},
  {"name": "Bob", "age": 25}
]

name	age
Alice	30
Bob	25

Array of primitives: Simple arrays like ["red", "green", "blue"] can be joined into a single cell (red, green, blue) or expanded into numbered columns (colors.0, colors.1, colors.2).

Nested arrays: When an object contains an array, the flattener must decide: expand each array element into a separate row (one-to-many join), or serialize the array as a string in a single cell. Most tools offer both options.

Ragged arrays: When array elements have different keys, the flattener creates columns for all keys across all elements, filling with empty values where a key is absent in a particular element.

Common Use Cases

• API data analysis: Exporting API responses (user lists, transaction records, product catalogs) into spreadsheets for analysis, filtering, and charting
• Database import: Converting JSON API data into flat CSV/TSV for bulk import into relational databases (PostgreSQL COPY, MySQL LOAD DATA)
• Report generation: Transforming JSON log data or analytics events into tabular reports for stakeholders who work in Excel
• Data pipeline integration: Converting JSON payloads between microservices that use JSON and legacy systems that consume CSV or fixed-width files
• Quick exploration: Viewing complex JSON data as a table to quickly understand its structure, identify patterns, and spot anomalies