To convert semi-structured data into rows and columns in Snowflake, theFLATTENfunction is utilized.

FLATTEN Function: This function takes semi-structured data (e.g., JSON) and transforms it into a relational table format by breaking down nested structures into individual rows. This process is essential for querying and analyzing semi-structured data using standard SQL operations.

Example Usage:

SELECT

f.value:attribute1 AS attribute1,

f.value:attribute2 AS attribute2

FROM

my_table,

LATERAL FLATTEN(input => my_table.semi_structured_column) f;

Snowflake Documentation on FLATTEN