How to transform nested columns in ETL: step by step

João Barros 12 de August de 2026 4 min read

Explains how to extract and transform nested columns (JSON/struct) into a relational table, a common ETL task to prepare data for analysis and reporting. Transforming nested columns simplifies queries and integrations with tools like Power BI or SQL.

Prerequisites

Python 3.8+ installed
Libraries: pandas and pyarrow (or built-in json)
Sample file in JSON or CSV with JSON columns
Basic knowledge of ETL and SQL

Step 1: Understand the nested format

Inspect a sample record and identify the columns that contain JSON objects/arrays. Knowing whether you want to normalize arrays into separate rows or only expand simple objects determines the strategy.

# Exemplo de linha JSON num CSV
{
  "order_id": 1234,
  "customer": {"id": "C001", "name": "Ana"},
  "items": [{"sku": "A1", "qty": 2}, {"sku": "B2", "qty": 1}],
  "metadata": "{\"source\": \"web\"}"
}

Step 2: Load the data into a DataFrame

Read the file into pandas. Use appropriate dtypes to avoid pandas treating JSON as strings if they are already parsed.

import pandas as pd

# Se for CSV com uma coluna 'payload' em JSON
df = pd.read_csv('orders.csv')
# Ou se for um ficheiro JSON por linha (JSONL)
df = pd.read_json('orders.jsonl', lines=True)

print(df.head())

Step 3: Expand simple JSON objects (nested columns to columns)

When a column contains a JSON object (dict), use json_normalize or pandas.json_normalize to expand inner fields into separate columns.

from pandas import json_normalize

# Supondo coluna 'customer' com dicts
customers = json_normalize(df['customer'])
customers.columns = ['customer_' + c for c in customers.columns]
df = pd.concat([df.drop(columns=['customer']), customers], axis=1)

print(df.columns)

Step 4: Turn arrays into rows (explode arrays)

For fields that are arrays (e.g., 'items'), convert each element of the array into a separate row using explode and then normalize the elements.

# Assegurar que 'items' é lista de dicts
df['items'] = df['items'].apply(lambda x: x if isinstance(x, list) else [])

# explode transforma cada item numa linha própria, mantendo order_id
df_exploded = df.explode('items').reset_index(drop=True)

# Normaliza a coluna 'items' agora que contém um dict por linha
items = json_normalize(df_exploded['items'])
items.columns = ['item_' + c for c in items.columns]

# Junta com o resto
df_items = pd.concat([df_exploded.drop(columns=['items']), items], axis=1)

print(df_items.head())

Step 5: Handle inconsistent fields and types

Validate types, handle nulls and unify column names. Convert dates and numbers to appropriate types before the final load.

# Exemplo: normalizar datas e preencher nulos
df_items['order_date'] = pd.to_datetime(df_items.get('order_date', None), errors='coerce')
df_items['item_qty'] = pd.to_numeric(df_items.get('item_qty', '0'), errors='coerce').fillna(0).astype(int)

# Renomear colunas para convenção SQL-friendly
df_items = df_items.rename(columns=lambda c: c.lower().replace(' ', '_'))

Step 6: Save the result for loading (CSV/Parquet/SQL)

Choose the appropriate format: CSV/Parquet for files, or insert directly into a SQL table. Parquet preserves types and is efficient for analytics.

# Guardar em Parquet
df_items.to_parquet('orders_items.parquet', index=False)

# Ou guardar em CSV para uma carga simples
df_items.to_csv('orders_items.csv', index=False)

Verify the result

Check that the row counts make sense: original rows multiplied by the array elements. Confirm created columns (e.g., customer_id, item_sku) and types (dates and integers). Test a sample in Power BI or in a SQL table, if possible.

# Exemplos de verificações
print('Linhas originais:', len(df))
print('Linhas após explode:', len(df_items))
print(df_items[['order_id','customer_id','item_sku','item_qty']].head())

Conclusion

By transforming nested columns into relational tables you prepare data for analysis and integration with tools like Power BI or SQL. Next steps: implement this process in automated batch (Airflow/ETL tool) and add tests/validations. Tip: start by creating small scripts and validate with samples before processing the entire dataset — which nested column causes you the most headaches?