In this lesson, you will learn how to extract, flatten, and normalize deeply nested JSON attributes directly within your analytical pipeline to transform raw semi-structured logs into query-ready relational formats.
The Challenge of Nested Payloads
In modern analytical pipelines, data often arrives in large, nested JSON blobs. While storing this raw data in a VARIANT or JSON column provides flexibility, it is devastating for performance if you query it repeatedly. To support efficient OLAP operations, you must perform "on-the-fly" normalization during your ETL/ELT process, converting these deep hierarchies into flat, relational structures.
Extract and Normalize
Most analytical databases provide path-extraction operators (like the : colon operator or JSON_EXTRACT_PATH_TEXT) to reach into specific keys. However, when dealing with arrays or nested objects, you need to combine these with lateral joins or unnesting functions to transform a single wide row into a vertical set of relational facts.
Consider a raw payload representing an order:
{
"order_id": 101,
"line_items": [
{"product": "A", "qty": 2},
{"product": "B", "qty": 1}
]
}
To normalize this into a fact table format, you use a lateral join to "explode" the nested array into distinct rows:
SELECT
raw_data:order_id::INT AS order_id,
item.value:product::STRING AS product_name,
item.value:qty::INT AS quantity
FROM raw_orders_table,
LATERAL FLATTEN(input => raw_data:line_items) AS item;
Why Normalize in the Pipeline?
By normalizing during the ingestion or transformation phase (rather than at query time), you map the semi-structured data into your established Fact Table Grain. This allows you to leverage your existing Columnar Storage and Partitioning strategies, ensuring that downstream queries—such as aggregations or window functions—run at maximum speed. If you leave the data nested, the database must parse the JSON string every single time a query executes, which ignores all the performance optimization work we’ve covered in previous lessons.
Key Takeaway: Normalize nested JSON into flat, relational structures during the pipeline stage to ensure analytical queries can leverage columnar storage and indexing performance.