Ingest

Editar recurso

Actualiza metadatos o contenido. Si cambia el texto o el archivo, la ingestión se ejecutará otra vez.

Biblioteca Título Tipo URL fuente Archivo Texto pegado

# Guía enriquecida: arrays, nested fields y `UNNEST` en BigQuery

## 1. Modelo mental

En BigQuery, un array es un valor que contiene varios elementos. Mientras no uses `UNNEST`, el array sigue siendo un único valor dentro de una fila. Cuando usas `UNNEST`, ese array se expande en varias filas. Esa separación entre “colección dentro de una fila” y “colección convertida en filas” es la clave de casi todo el tema. ([Google Cloud Documentation][1])

Piensa así:

* `ARRAY_AGG` suele ir de muchas filas a una colección.
* `UNNEST` suele ir de una colección a muchas filas.

---

## 2. Crear y leer arrays simples

### Ejemplo base

```sql
WITH employees AS (
  SELECT 1 AS emp_id, 'Ana' AS name, [1000, 1100, 1200, 1300] AS pay_by_quarter
  UNION ALL
  SELECT 2, 'Luis', [2000, 2100, 2200, 2300]
)
SELECT *
FROM employees;
```

### Resultado

| emp_id | name | pay_by_quarter           |
| -----: | ---- | ------------------------ |
|      1 | Ana  | [1000, 1100, 1200, 1300] |
|      2 | Luis | [2000, 2100, 2200, 2300] |

Para acceder a un elemento puntual, BigQuery usa:

* `OFFSET(n)` para índice base 0
* `ORDINAL(n)` para índice base 1 ([Google Cloud Documentation][1])

### Leer el tercer elemento

```sql
WITH employees AS (
  SELECT 1 AS emp_id, 'Ana' AS name, [1000, 1100, 1200, 1300] AS pay_by_quarter
  UNION ALL
  SELECT 2, 'Luis', [2000, 2100, 2200, 2300]
)
SELECT
  emp_id,
  name,
  pay_by_quarter[ORDINAL(3)] AS q3_pay
FROM employees;
```

### Resultado

| emp_id | name | q3_pay |
| -----: | ---- | -----: |
|      1 | Ana  |   1200 |
|      2 | Luis |   2200 |

Esto es acceso directo. No hace falta `UNNEST` para leer un elemento puntual. ([Google Cloud Documentation][1])

---

## 3. `UNNEST`: cuándo cambia la cantidad de filas

Si ahora quieres convertir el array en filas:

```sql
WITH employees AS (
  SELECT 1 AS emp_id, 'Ana' AS name, [1000, 1100, 1200, 1300] AS pay_by_quarter
  UNION ALL
  SELECT 2, 'Luis', [2000, 2100, 2200, 2300]
)
SELECT
  emp_id,
  name,
  quarter_pay
FROM employees, UNNEST(pay_by_quarter) AS quarter_pay;
```

### Resultado

| emp_id | name | quarter_pay |
| -----: | ---- | ----------: |
|      1 | Ana  |        1000 |
|      1 | Ana  |        1100 |
|      1 | Ana  |        1200 |
|      1 | Ana  |        1300 |
|      2 | Luis |        2000 |
|      2 | Luis |        2100 |
|      2 | Luis |        2200 |
|      2 | Luis |        2300 |

Ahora sí cambió la cardinalidad. Antes había 2 filas; ahora hay 8. Eso pasa porque `UNNEST` devuelve una fila por elemento del array. ([Google Cloud Documentation][1])

---

## 4. Por qué BigQuery exige `UNNEST` explícito

La idea que discutimos del libro es esta: si BigQuery “explotara” arrays automáticamente en el `SELECT`, en consultas más complejas habría ambigüedad sobre qué debe quedarse como array y qué debe expandirse a filas. Por eso BigQuery mantiene una regla clara: el cambio de cardinalidad ocurre a través de `UNNEST` en el `FROM` o en joins, no por una expresión mágica en el `SELECT`. ([Google Cloud Documentation][2])

### Ejemplo con `ARRAY<STRUCT>`

```sql
WITH library AS (
  SELECT
    'Borges' AS author,
    [
      STRUCT(
        'Ficciones' AS title,
        [STRUCT('Tlön' AS title), STRUCT('Pierre Menard' AS title)] AS chapter
      ),
      STRUCT(
        'El Aleph' AS title,
        [STRUCT('El inmortal' AS title), STRUCT('El Aleph' AS title)] AS chapter
      )
    ] AS book
)
SELECT *
FROM library;
```

Aquí:

* `author` es escalar
* `book` es un array
* `chapter` dentro de cada `book` también es un array

Si quisieras una fila por libro:

### Resultado

| author | book_title |
| ------ | ---------- |
| Borges | Ficciones  |
| Borges | El Aleph   |

Si quisieras una fila por capítulo:

### Resultado

| author | book_title | chapter_title |
| ------ | ---------- | ------------- |
| Borges | Ficciones  | Tlön          |
| Borges | Ficciones  | Pierre Menard |
| Borges | El Aleph   | El inmortal   |
| Borges | El Aleph   | El Aleph      |

Y si quisieras una fila por libro pero con los capítulos como array:

### Resultado

| author | book_title | chapter_titles          |
| ------ | ---------- | ----------------------- |
| Borges | Ficciones  | [Tlön, Pierre Menard]   |
| Borges | El Aleph   | [El inmortal, El Aleph] |

Ese es exactamente el tipo de ambigüedad que BigQuery evita al obligarte a usar `UNNEST` explícito. ([Google Cloud Documentation][2])

---

## 5. Usar arrays para almacenar repeated fields

El texto que compartiste usa el caso de filings de charities: una organización puede tener varios filings, y una forma útil de modelarlo es tener una fila por organización y un array de filings dentro de esa fila. El libro lo hace con `ARRAY_AGG(STRUCT(...))`.  `ARRAY_AGG` agrega valores de muchas filas en un array. ([Google Cloud Documentation][1])

### Tabla “plana”

```sql
WITH filings_raw AS (
  SELECT '100' AS ein, 'E' AS elf, 201412 AS tax_pd, 8 AS subseccd
  UNION ALL
  SELECT '100', 'E', 201312, 8
  UNION ALL
  SELECT '200', 'P', 201412, 12
  UNION ALL
  SELECT '300', 'E', 201412, 8
)
SELECT *
FROM filings_raw;
```

### Resultado

| ein | elf | tax_pd | subseccd |
| --- | --- | -----: | -------: |
| 100 | E   | 201412 |        8 |
| 100 | E   | 201312 |        8 |
| 200 | P   | 201412 |       12 |
| 300 | E   | 201412 |        8 |

### Reesquema a array de structs

### Resultado conceptual

| ein | filing                           |
| --- | -------------------------------- |
| 100 | [{E, 201412, 8}, {E, 201312, 8}] |
| 200 | [{P, 201412, 12}]                |
| 300 | [{E, 201412, 8}]                 |

Ahora cada fila representa mejor a la entidad `ein`, y `filing` representa la colección asociada. Eso es muy parecido al ejemplo del libro.

---

## 6. Cómo los arrays ayudan a evitar errores lógicos

Este es uno de los mejores puntos del capítulo. Si la tabla está plana y haces esto:

```sql
WITH filings_raw AS (
  SELECT '100' AS ein, 'E' AS elf
  UNION ALL
  SELECT '100', 'P'
  UNION ALL
  SELECT '200', 'P'
  UNION ALL
  SELECT '300', 'E'
)
SELECT DISTINCT ein
FROM filings_raw
WHERE elf != 'E';
```

### Resultado

| ein |
| --- |
| 100 |
| 200 |

Pero eso no significa “organizaciones que nunca filingan electrónicamente”. Solo significa “organizaciones con al menos una fila no electrónica”. El `100` sale porque tiene una fila `P`, aunque también tiene una `E`.

Si modelas bien la colección, la pregunta correcta pasa a ser: “¿existe algún filing electrónico?” o “¿no existe ninguno?”. El libro da el patrón con `UNNEST(filing)` dentro de una subquery.

### Pregunta correcta: “no existe ningún filing con `elf = 'E'`”

```sql
WITH filings AS (
  SELECT '100' AS ein, [STRUCT('E' AS elf), STRUCT('P' AS elf)] AS filing
  UNION ALL
  SELECT '200', [STRUCT('P' AS elf)]
  UNION ALL
  SELECT '300', [STRUCT('E' AS elf)]
)
SELECT
  ein
FROM filings
WHERE NOT EXISTS (
  SELECT 1
  FROM UNNEST(filing) AS f
  WHERE f.elf = 'E'
);
```

### Resultado

| ein |
| --- |
| 200 |

Ese resultado sí coincide con “organizaciones que no filingan electrónicamente”.

---

## 7. `NOT IN` vs `NOT EXISTS`

El texto del libro usa este patrón:

```sql
'E' NOT IN (SELECT elf FROM UNNEST(filing))
```

y la idea es correcta para “no hay `E` en el array”.  En BigQuery, también existe la forma `x [NOT] IN UNNEST(array_expression)` para probar membresía en arrays. ([Google Cloud Documentation][3])

### Forma equivalente y más explícita

```sql
WHERE NOT EXISTS (
  SELECT 1
  FROM UNNEST(filing) AS f
  WHERE f.elf = 'E'
)
```

Las dos expresan la idea de “ningún elemento cumple esta condición”.

### Ojo con el ejemplo del libro

El libro luego muestra:

```sql
EXISTS (SELECT elf FROM UNNEST(filing) WHERE elf != 'E')
```

pero eso no es equivalente. Eso significa “existe al menos un filing no electrónico”, que incluiría casos mixtos. Ese es un punto donde tu texto induce a error.

### Comparación rápida

Supón esta tabla:

| ein | filing |
| --- | ------ |
| 100 | [E, P] |
| 200 | [P]    |
| 300 | [E]    |

Entonces:

* `'E' NOT IN (...)` devuelve `200`
* `NOT EXISTS (... WHERE elf = 'E')` devuelve `200`
* `EXISTS (... WHERE elf != 'E')` devuelve `100` y `200`

La tercera consulta no responde la misma pregunta.

---

## 8. Generar datos con arrays

El texto también muestra el otro gran uso de arrays: no solo almacenar colecciones, sino generarlas. Usa `GENERATE_DATE_ARRAY` para crear una lista de fechas.  BigQuery soporta `GENERATE_ARRAY` y `GENERATE_DATE_ARRAY` para construir secuencias. ([Google Cloud Documentation][1])

### Generar una lista de fechas

```sql
SELECT
  GENERATE_DATE_ARRAY('2026-01-01', '2026-01-21', INTERVAL 10 DAY) AS days;
```

### Resultado

| days                                 |
| ------------------------------------ |
| [2026-01-01, 2026-01-11, 2026-01-21] |

Eso sigue siendo una sola fila con un array.

### Convertirla en varias filas

```sql
WITH days AS (
  SELECT GENERATE_DATE_ARRAY('2026-01-01', '2026-01-21', INTERVAL 10 DAY) AS summer
)
SELECT summer_day
FROM days, UNNEST(summer) AS summer_day;
```

### Resultado

| summer_day |
| ---------- |
| 2026-01-01 |
| 2026-01-11 |
| 2026-01-21 |

Ese patrón aparece tal cual en el material que compartiste.

---

## 9. Coma, `CROSS JOIN` y `LEFT JOIN UNNEST`

Cuando haces:

```sql
FROM days, UNNEST(summer) AS summer_day
```

estás haciendo un cross join correlacionado con el array. Si el array está vacío o es `NULL`, esa fila puede desaparecer. El texto que compartiste lo explica y sugiere `LEFT JOIN UNNEST(...)` para preservar la fila izquierda.  BigQuery admite `UNNEST` en joins y el uso de `LEFT JOIN` es la forma correcta de mantener filas aun si no hay elementos en el array. ([Google Cloud Documentation][2])

### Ejemplo

```sql
WITH days AS (
  SELECT 1 AS id, [DATE '2026-01-01', DATE '2026-01-11'] AS summer
  UNION ALL
  SELECT 2 AS id, [] AS summer
)
SELECT id, summer_day
FROM days LEFT JOIN UNNEST(summer) AS summer_day;
```

### Resultado conceptual

| id | summer_day |
| -: | ---------- |
|  1 | 2026-01-01 |
|  1 | 2026-01-11 |
|  2 | NULL       |

Si hubieras usado coma o `CROSS JOIN`, la fila con `id = 2` no aparecería.

---

## 10. `OFFSET`, `ORDINAL` y `WITH OFFSET`

Ya vimos que:

* `OFFSET(0)` es el primer elemento
* `ORDINAL(1)` es el primer elemento ([Google Cloud Documentation][1])

Además, cuando usas `UNNEST`, puedes recuperar la posición original con `WITH OFFSET`. BigQuery aclara que `UNNEST` no preserva el orden por sí solo, y que si necesitas restaurarlo, debes usar `WITH OFFSET` y luego ordenar por ese offset. ([Google Cloud Documentation][4])

### Ejemplo

```sql
WITH t AS (
  SELECT ['A', 'B', 'C'] AS letters
)
SELECT
  letter,
  pos
FROM t, UNNEST(letters) AS letter WITH OFFSET AS pos
ORDER BY pos;
```

### Resultado

| letter | pos |
| ------ | --: |
| A      |   0 |
| B      |   1 |
| C      |   2 |

Esto es muy útil cuando necesitas desnormalizar un array sin perder su orden original.

---

## 11. Repartir elementos usando índices y módulo

El texto del libro usa el ejemplo de los minions para turnarse tareas. La idea es:

1. generar una secuencia de días
2. generar una secuencia de posiciones
3. asignar un trabajador usando `MOD(posición, cantidad_de_trabajadores)`

### Ejemplo adaptado

```sql
WITH plan AS (
  SELECT
    GENERATE_DATE_ARRAY('2026-01-01', '2026-01-31', INTERVAL 10 DAY) AS days,
    ['Lak', 'Jordan', 'Graham'] AS workers
)
SELECT
  days[ORDINAL(dayno)] AS work_day,
  workers[OFFSET(MOD(dayno, ARRAY_LENGTH(workers)))] AS worker
FROM plan,
UNNEST(GENERATE_ARRAY(1, ARRAY_LENGTH(days), 1)) AS dayno
ORDER BY work_day;
```

### Resultado conceptual

| work_day   | worker |
| ---------- | ------ |
| 2026-01-01 | Jordan |
| 2026-01-11 | Graham |
| 2026-01-21 | Lak    |
| 2026-01-31 | Jordan |

Esto ilustra bien la diferencia entre índice base 1 para `ORDINAL` e índice base 0 para `OFFSET`.

---

## 12. `ARRAY_CONCAT`

Sirve para concatenar arrays del mismo tipo. El libro lo resume en su tabla de funciones.  BigQuery documenta `ARRAY_CONCAT` como la función para combinar arrays. ([Google Cloud Documentation][1])

```sql
SELECT ARRAY_CONCAT(['A', 'B'], ['C', 'D']) AS letters;
```

### Resultado

| letters      |
| ------------ |
| [A, B, C, D] |

---

## 13. `ARRAY_TO_STRING` y `TO_JSON_STRING`

Estas funciones son muy útiles para depuración. El texto que compartiste lo enfatiza: `ARRAY_TO_STRING` funciona bien con arrays de strings, y `TO_JSON_STRING` es útil cuando el array contiene tipos más complejos como fechas o structs.  BigQuery documenta ambas funciones como formas de convertir arrays o valores complejos a representaciones legibles. ([Google Cloud Documentation][1])

### `ARRAY_TO_STRING`

```sql
SELECT ARRAY_TO_STRING(['A', 'B', NULL, 'D'], '*', 'na') AS arr;
```

### Resultado

| arr      |
| -------- |
| A*B*na*D |

### `TO_JSON_STRING` con fechas

```sql
SELECT TO_JSON_STRING(
  GENERATE_DATE_ARRAY('2026-01-01', '2026-01-21', INTERVAL 10 DAY)
) AS json_dates;
```

### Resultado

| json_dates                               |
| ---------------------------------------- |
| ["2026-01-01","2026-01-11","2026-01-21"] |

### `TO_JSON_STRING` con structs

```sql
SELECT TO_JSON_STRING([
  STRUCT(1 AS a, 'bbb' AS b),
  STRUCT(2 AS a, 'ccc' AS b)
]) AS json_structs;
```

### Resultado

| json_structs                          |
| ------------------------------------- |
| [{"a":1,"b":"bbb"},{"a":2,"b":"ccc"}] |

---

## 14. Resumen de funciones clave

El material que compartiste cierra con una tabla-resumen de funciones. La versión útil y corregida sería esta.

### Crear arrays

* `[...]`
* `GENERATE_ARRAY`
* `GENERATE_DATE_ARRAY`
* `ARRAY(subquery)` ([Google Cloud Documentation][1])

### Acceder a elementos

* `arr[OFFSET(0)]`
* `arr[ORDINAL(1)]` ([Google Cloud Documentation][5])

### Medir

* `ARRAY_LENGTH(arr)` ([Google Cloud Documentation][5])

### Expandir

* `UNNEST(arr)` ([Google Cloud Documentation][1])

### Consultar contenido

* `x IN UNNEST(arr)`
* `EXISTS (SELECT 1 FROM UNNEST(arr) ...)`
* `NOT EXISTS (SELECT 1 FROM UNNEST(arr) ...)` ([Google Cloud Documentation][3])

### Agregar

* `ARRAY_AGG(expr)` ([Google Cloud Documentation][1])

### Combinar

* `ARRAY_CONCAT(a1, a2)` ([Google Cloud Documentation][1])

### Imprimir

* `ARRAY_TO_STRING`
* `TO_JSON_STRING` ([Google Cloud Documentation][1])

---

## 15. Errores comunes

### Error 1: confundir acceso directo con flattening

Esto está mal como idea:

```sql
-- “quiero el tercer elemento, así que uso UNNEST”
```

Para sacar un elemento puntual, usa `OFFSET` u `ORDINAL`. Usa `UNNEST` solo cuando realmente quieras múltiples filas. ([Google Cloud Documentation][1])

### Error 2: hacer filtros fila a fila cuando la lógica es “a nivel colección”

Este error aparece mucho en tablas planas con repeated fields. El ejemplo clásico es:

```sql
WHERE elf != 'E'
```

cuando en realidad querías:

```sql
WHERE NOT EXISTS (
  SELECT 1 FROM UNNEST(filing) WHERE elf = 'E'
)
```

El texto que compartiste usa ese caso precisamente para explicar por qué arrays pueden ayudar a imponer una lógica más segura.

### Error 3: usar `EXISTS (... WHERE elf != 'E')` pensando que equivale a “nunca hay E”

No equivale. Incluye casos mixtos. Ese es el punto más problemático del fragmento del libro.

### Error 4: asumir que `UNNEST` conserva orden

Si te importa la posición, usa `WITH OFFSET` y luego `ORDER BY offset`. ([Google Cloud Documentation][4])

---

## 16. Regla práctica para decidir qué hacer

Cuando veas un array, hazte esta pregunta:

**¿Quiero seguir teniendo una fila por entidad, o quiero una fila por elemento del array?**

Si quieres una fila por entidad:

* mantén el array como array
* usa `OFFSET`, `ORDINAL`, `IN`, `EXISTS`, `NOT EXISTS`, subqueries correlacionadas

Si quieres una fila por elemento:

* usa `UNNEST`

Esa sola decisión aclara la mayoría de las consultas.

---

## 17. Cheat sheet final

### Una fila por empleado, tercer pago trimestral

```sql
SELECT pay_by_quarter[ORDINAL(3)] AS q3_pay
FROM employees;
```

### Una fila por pago trimestral

```sql
SELECT quarter_pay
FROM employees, UNNEST(pay_by_quarter) AS quarter_pay;
```

### Una fila por organización con todos sus filings

```sql
SELECT ein, ARRAY_AGG(STRUCT(elf, tax_pd, subseccd)) AS filing
FROM filings_raw
GROUP BY ein;
```

### Organizaciones sin ningún filing electrónico

```sql
SELECT ein
FROM filings
WHERE NOT EXISTS (
  SELECT 1
  FROM UNNEST(filing) AS f
  WHERE f.elf = 'E'
);
```

### Generar fechas

```sql
SELECT GENERATE_DATE_ARRAY('2026-01-01', '2026-01-21', INTERVAL 10 DAY);
```

### Convertir fechas generadas en filas

```sql
WITH days AS (
  SELECT GENERATE_DATE_ARRAY('2026-01-01', '2026-01-21', INTERVAL 10 DAY) AS d
)
SELECT day
FROM days, UNNEST(d) AS day;
```

### Mantener orden al expandir

```sql
SELECT x, pos
FROM t, UNNEST(arr) AS x WITH OFFSET AS pos
ORDER BY pos;
```

---

Si editas este texto, se volverá a preparar el contenido para búsqueda.

Cancelar