Flatten

Flatten expands nested columns into separate flat columns. Struct, Object, and JSON-string columns are unnested into individual fields. List columns can be exploded into one row per element, split into a fixed number of indexed columns, or reduced to an item count, depending on the mode you choose. New columns are named using the pattern originalcolumn_fieldname. The source column can be kept or dropped. You can flatten all eligible columns at once or select specific columns.

When to Use Flatten

• Unpack JSON metadata. Expand a metadata JSON column into separate metadata_source, metadata_channel columns for filtering.
• Explode a list into rows. Convert an array-valued column into one row per value for counting or joining, when you genuinely need row-per-element granularity.
• Split a list into columns. Turn a fixed-shape list, like a top-3 ranking or a set of tags with a known maximum count, into indexed columns on the same row instead of multiplying rows.
• Count list items without exploding. Get "number of tags" or "number of line items" as a new integer column, with no row multiplication.
• Prepare for Group By. Flatten nested struct fields before applying Group By on extracted sub-fields.
• Normalize API response data. Expand deeply nested API payload columns into queryable flat fields.
• Fix Group By warning. Group By warns when Object or List columns exist. Flatten them first to avoid data loss in aggregations.

Sample Dataset

The examples in this doc use the Edilitics sample orders dataset. Download it to follow along in your own workspace.

Relevant columns for Flatten examples:

List Modes

A List column supports three modes, chosen per column. Struct, Object, and JSON-string columns always unnest the same way, so no mode selector appears for them; only Max Depth applies.

How to Apply Flatten

Before and After

Flatten metadata (Object column) with Max Depth 2, Drop Original Column on.

Input:

Output:

Original metadata column dropped. Two new flat columns added. Row count unchanged.

How Flatten Works by Column Type

Max Depth

Max Depth applies to Struct, Object, and JSON-string columns. It controls how many levels of nesting the operation recurses into. Default is 3.

New column names at each level append the field name: metadata_user_address_city at depth 3.

Code Equivalent

-- PostgreSQL: expand JSON object column
SELECT
  order_id,
  revenue,
  metadata->>'source' AS metadata_source,
  metadata->>'channel' AS metadata_channel
FROM orders;

-- BigQuery: flatten STRUCT column
SELECT
  order_id,
  revenue,
  metadata.source AS metadata_source,
  metadata.channel AS metadata_channel
FROM orders;

import polars as pl

# Flatten struct/JSON column (dtype must be supplied explicitly)
df = df.with_columns(
    pl.col("metadata").str.json_decode(pl.Struct({"source": pl.String, "channel": pl.String}))
).unnest("metadata").rename({
    "source": "metadata_source",
    "channel": "metadata_channel"
})

# Explode list column into rows
df = df.explode("tags")

# Split list column into indexed columns instead of exploding
df = df.with_columns(
    pl.col("tags").list.to_struct(fields=["tags_1", "tags_2", "tags_3"])
).unnest("tags")

# Count list items without exploding
df = df.with_columns(pl.col("tags").list.len().alias("tags_count"))

Operation Reference

Frequently Asked Questions

Next Steps

Need help? Email support@edilitics.com with your workspace, job ID, and context. We reply within one business day.