Welcome to Killing JSON, a series in which I channel my frustration with loading data into teaching you something. Today, we’re talking about the FLATTEN function in Snowflake.

Flattening JSON, Easy Enough

If you’ve ever loaded semi-structured data into Snowflake, I can bet you’ve used FLATTEN to put it into a table. You probably understand that this function plays an important role in unraveling the nested dictionaries in a JSON file to produce a structure compatible with a table.

It sounds simple (maybe because it is) but let’s look at the syntax for the FLATTEN function together:

FLATTEN( INPUT => <expr> [ , PATH => <constant_expr> ]
                         [ , OUTER => TRUE | FALSE ]
                         [ , RECURSIVE => TRUE | FALSE ]
                         [ , MODE => 'OBJECT' | 'ARRAY' | 'BOTH' ] )

Huh? What should my INPUT be? What is recursion? Which mode should I select? And, most of all, why do all the arguments use => for assignment?

Well, at least the last one has a simple answer: the => operator is simply an assignment operator, needed because SQL uses the ordinary = as a logical operator. As for the rest, let’s tackle them one by one. Let’s try to figure out how FLATTEN works though simple examples.

Simple Outputs of the FLATTEN Function

In this first example, we’re passing very simple JSON data as the input to FLATTEN.

-- "parse_json" let's us create JSON inline.
SELECT * FROM TABLE(FLATTEN(input => parse_json('{"a":1, "b":[77,88]}')));

What does each column mean?

• SEQ – A unique sequence number associated to the JSON data we passed.
• KEY – For maps (such as "a" : 1), returns the key ("a")
• PATH – The path to the element that needs to be flattened. In this case, it’s the same as the key.
• INDEX – The index of the array element. If it is not an array, this is null.
• VALUE – The value in the flattened value ("1")
• THIS – The element being flattened.

So far, so good. Let’s keep playing with simple data, and pass FLATTEN an array.

-- This array is missing a value, but it'll be ok!
SELECT * FROM TABLE(FLATTEN(input => parse_json('[1, 55, ,77]'))) ;

There’s some interesting differences between this example and the previous. For one, all the keys are null. This makes sense, our data consists of a simple array. Also, the path variable is now equivalent to the index of the value within the array. Null values in the array were completely skipped.

Using the PATH Parameter

Armed with our knowledge of the output of the FLATTEN function, let’s learn to use the PATH parameter. This parameter indicates the path to the element within the data structure which needs to be flattened. If we construct JSON data which contains nested elements that can be flattened, we can use this parameter to pinpoint those elements.

-- "b" is the key to an array, which can be flattened
SELECT * FROM TABLE(FLATTEN(input => parse_json('{"a":1, "b":[77,88]}'), path => 'b'));

Interesting! We can see that the path variable now reflects that we passed "b" as an argument, and this now refers to the array keyed by "b."

Using the OUTER Parameter

The OUTER parameter can either be TRUE or FALSE. If false, which is the default, any input rows that cannot be expanded are completely omitted from the output. If true, one row will be generated even for zero-row expansions. Zero-row expansions are those with NULL in the KEY, INDEX, and VALUE columns. Let’s see an example to understand this.

-- There's nothing here...
SELECT * FROM TABLE(FLATTEN(input => parse_json('[]')));

Hm. There’s no values to unpack, so we have no output. Setting OUTER to TRUE changes this be

El uso de datos semiestructurados ha crecido enormemente en los últimos años. APIs, sistemas de eventos, aplicaciones web y microservicios suelen generar información en formato JSON.

Aunque este formato es flexible y ampliamente utilizado, trabajar con JSON anidado puede ser complicado cuando necesitamos analizar los datos en estructuras tabulares.

En Snowflake, el tipo de dato VARIANT permite almacenar JSON directamente. Sin embargo, para poder analizar estos datos en consultas SQL tradicionales, a menudo necesitamos descomponer las estructuras anidadas.

Aquí es donde entra la función FLATTEN.

El reto del JSON anidado

Un documento JSON típico puede tener una estructura como esta:

{

  «order_id»: 1001,

  «customer»: «Alice»,

  «items»: [

    {«product»: «Laptop», «price»: 1200},

    {«product»: «Mouse», «price»: 50}

  ]

}

El problema es que el campo items contiene un array de objetos, lo que dificulta el análisis directo mediante SQL.

Si queremos analizar cada producto de forma independiente, necesitamos convertir ese array en múltiples filas.

Cómo Snowflake almacena JSON

Snowflake permite almacenar JSON directamente mediante el tipo VARIANT.

Ejemplo de tabla:

CREATE TABLE orders_raw (

    data VARIANT

);

Cada registro puede contener un objeto JSON completo.

Esto facilita la ingestión de datos sin necesidad de definir un esquema rígido desde el inicio.

Introducción a la función FLATTEN

La función FLATTEN permite expandir arrays o estructuras JSON anidadas en filas individuales.

Esto transforma datos jerárquicos en formato tabular.

Ejemplo básico:

SELECT

    data:order_id AS order_id,

    item.value:product AS product,

    item.value:price AS price

FROM orders_raw,

LATERAL FLATTEN(input => data:items) item;

Resultado:

Cada elemento del array se convierte en una fila independiente.

Qué significa LATERAL FLATTEN

La sintaxis habitual incluye LATERAL.

LATERAL FLATTEN(…)

Esto indica que la función debe evaluarse para cada fila del dataset original.

Es similar a aplicar un bucle sobre cada array contenido en el JSON.

Columnas generadas por FLATTEN

La función FLATTEN genera varias columnas útiles automáticamente.

Estas columnas facilitan explorar estructuras complejas.

Conversión de tipos

Los valores extraídos de JSON suelen necesitar conversión de tipo.

Ejemplo:

SELECT

    data:order_id::INTEGER AS order_id,

    item.value:product::STRING AS product,

    item.value:price::FLOAT AS price

FROM orders_raw,

LATERAL FLATTEN(input => data:items) item;

Esto convierte explícitamente los valores al tipo adecuado.

Trabajar con múltiples niveles de anidamiento

En algunos casos, los JSON pueden contener arrays dentro de arrays.

Ejemplo:

{

  «order_id»: 1001,

  «items»: [

    {

      «product»: «Laptop»,

      «discounts»: [

        {«type»: «promo», «amount»: 50}

      ]

    }

  ]

}

Podemos usar múltiples FLATTEN:

SELECT

    data:order_id AS order_id,

    item.value:product AS product,

    discount.value:type AS discount_type

FROM orders_raw,

LATERAL FLATTEN(input => data:items) item,

LATERAL FLATTEN(input => item.value:discounts) discount;

Esto permite navegar estructuras JSON profundamente anidadas.

Uso en pipelines de datos

La función FLATTEN suele utilizarse en procesos de transformación dentro de pipelines de datos.

Un flujo típico sería:

1. Ingesta de datos JSON desde APIs o eventos
2. Almacenamiento en formato VARIANT
3. Transformación usando FLATTEN
4. Normalización en tablas analíticas

Esto convierte datos flexibles en modelos estructurados listos para análisis.

Ventajas de trabajar con JSON en Snowflake

Snowflake permite:

• Ingestión rápida de datos sin esquema
• Almacenamiento flexible de estructuras complejas
• Consultas directas sobre JSON
• Transformaciones progresivas hacia modelos analíticos

Esto facilita la integración de fuentes modernas de datos.

Buenas prácticas

Cuando trabajes con JSON y FLATTEN:

• Convierte tipos explícitamente
• Documenta rutas JSON utilizadas
• Evita consultas excesivamente complejas
• Considera crear tablas transformadas para análisis frecuente
• Valida la estructura del JSON antes de procesarlo

Estas prácticas ayudan a mantener pipelines robustos.

Conclusión

El formato JSON ofrece gran flexibilidad para ingestión de datos, pero su estructura jerárquica puede dificultar el análisis. La función FLATTEN en Snowflake permite transformar arrays y estructuras anidadas en datasets tabulares, facilitando su uso en consultas SQL tradicionales.

En arquitecturas modernas de datos, dominar herramientas como FLATTEN es esencial para integrar datos semiestructurados dentro de modelos analíticos eficientes.