Bigquery

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

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

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

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

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:

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

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>

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:

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
  author,
  b.title AS book_title
FROM library, UNNEST(book) AS b;

Resultado

Si quisieras una fila por capítulo:

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
  author,
  b.title AS book_title,
  c.title AS chapter_title
FROM library,
UNNEST(book) AS b,
UNNEST(b.chapter) AS c;

Resultado

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

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
  author,
  b.title AS book_title,
  ARRAY(
    SELECT c.title
    FROM UNNEST(b.chapter) AS c
  ) AS chapter_titles
FROM library, UNNEST(book) AS b;

Resultado

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”

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

Reesquema a array de structs

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
  ein,
  ARRAY_AGG(STRUCT(elf, tax_pd, subseccd) ORDER BY tax_pd DESC) AS filing
FROM filings_raw
GROUP BY ein;

Resultado conceptual

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:

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

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'”

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

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:

'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

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:

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:

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

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

Resultado

Eso sigue siendo una sola fila con un array.

Convertirla en varias filas

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

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

---

9. Coma, CROSS JOIN y LEFT JOIN UNNEST

Cuando haces:

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

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

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

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

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

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

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])

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

Resultado

---

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

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

Resultado

TO_JSON_STRING con fechas

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

Resultado

TO_JSON_STRING con structs

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

Resultado

---

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:

-- “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:

WHERE elf != 'E'

cuando en realidad querías:

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

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

Una fila por pago trimestral

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

Una fila por organización con todos sus filings

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

Organizaciones sin ningún filing electrónico

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

Generar fechas

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

Convertir fechas generadas en filas

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

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

---