> ## Documentation Index
> Fetch the complete documentation index at: https://docs.nekt.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Unnest arrays

> Explode array columns into individual rows — essential for tags, labels, and line items from API sources.

## When to use this

Many API sources return columns that contain arrays — for example, a CRM that stores deal labels as `["hot", "enterprise", "renewal"]` in a single column, or an e-commerce platform that nests line items inside each order.

To filter, aggregate, or join on these values you first need to **unnest** (or explode) the array so that each element becomes its own row.

***

## Sample input

Imagine a `crm_deals` table ingested from your CRM into the **Raw** layer:

| deal\_id | deal\_name | amount | labels                   |
| -------- | ---------- | ------ | ------------------------ |
| 1        | Acme Corp  | 50000  | \["hot", "enterprise"]   |
| 2        | Globex Inc | 12000  | \["renewal"]             |
| 3        | Initech    | 8500   | \["hot", "smb", "trial"] |

Each row has a `labels` column that is an **array of strings**. We want to produce one row per deal-label pair while keeping the other columns intact.

***

## Implementation

<Tabs>
  <Tab title="Nekt Express / BigQuery">
    BigQuery uses `CROSS JOIN UNNEST` directly on the array column. The alias becomes the new column name.

    ```sql theme={null}
    SELECT
      d.deal_id,
      d.deal_name,
      d.amount,
      label
    FROM `raw.crm_deals` AS d
    CROSS JOIN UNNEST(d.labels) AS label
    ```

    <Tip>
      If the column is a JSON string instead of a native `ARRAY`, parse it first:

      ```sql theme={null}
      CROSS JOIN UNNEST(JSON_EXTRACT_STRING_ARRAY(d.labels)) AS label
      ```
    </Tip>
  </Tab>

  <Tab title="Athena SQL">
    Athena (Trino / Presto) uses `CROSS JOIN UNNEST` to expand an array column. You alias both the virtual table and the new column.

    ```sql theme={null}
    SELECT
      d.deal_id,
      d.deal_name,
      d.amount,
      label
    FROM raw.crm_deals AS d
    CROSS JOIN UNNEST(d.labels) AS t(label)
    ```

    <Tip>
      If the `labels` column is stored as a JSON string rather than a native array, cast it first:

      ```sql theme={null}
      CROSS JOIN UNNEST(CAST(json_parse(d.labels) AS ARRAY(VARCHAR))) AS t(label)
      ```
    </Tip>
  </Tab>

  <Tab title="Python (Nekt SDK)">
    In PySpark, use the `explode` function to turn each array element into a separate row.

    ```python theme={null}
    import nekt
    from pyspark.sql import functions as F

    deals_df = nekt.load_table(layer_name="Raw", table_name="crm_deals")

    unnested_df = deals_df.select(
        "deal_id",
        "deal_name",
        "amount",
        F.explode("labels").alias("label")
    )

    nekt.save_table(
        df=unnested_df,
        layer_name="Trusted",
        table_name="crm_deals_labels"
    )
    ```

    <Tip>
      If the column contains a JSON string instead of a native array, parse it first with `from_json`:

      ```python theme={null}
      from pyspark.sql.types import ArrayType, StringType

      deals_df = deals_df.withColumn(
          "labels",
          F.from_json(F.col("labels"), ArrayType(StringType()))
      )
      ```
    </Tip>
  </Tab>
</Tabs>

***

## Expected output

After unnesting, the table contains one row for every deal-label combination:

| deal\_id | deal\_name | amount | label      |
| -------- | ---------- | ------ | ---------- |
| 1        | Acme Corp  | 50000  | hot        |
| 1        | Acme Corp  | 50000  | enterprise |
| 2        | Globex Inc | 12000  | renewal    |
| 3        | Initech    | 8500   | hot        |
| 3        | Initech    | 8500   | smb        |
| 3        | Initech    | 8500   | trial      |

***

## Tips and gotchas

<Warning>
  Unnesting **removes rows** where the array is empty (`[]`). If you need to keep those rows, use a **LEFT JOIN** variant instead:

  * **Athena SQL**: not directly supported — use a `LEFT JOIN UNNEST` (available since Trino 360+) or a `UNION` with a `WHERE cardinality(labels) = 0` clause.
  * **BigQuery**: use `LEFT JOIN UNNEST(d.labels) AS label` instead of `CROSS JOIN`.
  * **PySpark**: use `F.explode_outer("labels")` instead of `F.explode("labels")` — it produces a `NULL` row for empty arrays.
</Warning>

<Note>
  Unnesting multiplies the row count — if a deal has 3 labels, it becomes 3 rows. Keep this in mind when calculating aggregates like `SUM(amount)`, as you'll need to deduplicate or aggregate at the right grain to avoid inflated numbers.
</Note>