Consulta campos anidados y repetidos en SQL heredado

En este documento, se detalla cómo consultar datos anidados y repetidos en la sintaxis de consulta de SQL heredado. La sintaxis de consulta preferida para BigQuery es GoogleSQL. Para obtener información sobre el manejo de datos anidados y repetidos en GoogleSQL, consulta la guía de migración de GoogleSQL.

BigQuery es compatible con la carga y exportación de los datos anidados y repetidos en el formato de archivos JSON y Avro. Para muchas consultas de SQL heredado, BigQuery puede compactar datos de forma automática. Por ejemplo, muchas declaraciones SELECT pueden recuperar campos repetidos o anidados mientras mantienen la estructura de los datos. A su vez, las cláusulas WHERE pueden filtrar los datos mientras se mantiene su estructura. Por el contrario, las cláusulas ORDER BY y GROUP BY compactan los datos consultados de forma implícita. En los casos en que los datos no se compactan de forma implícita, como cuando se consultan varios campos repetidos en SQL heredado, puedes consultar tus datos con las funciones FLATTEN y WITHIN de SQL.

FLATTEN

Cuando consultas datos anidados, BigQuery compacta los datos de la tabla de forma automática por ti. Por ejemplo, observemos un esquema de muestra sobre datos de personas:

   Last modified                 Schema                 Total Rows   Total Bytes   Expiration
 ----------------- ----------------------------------- ------------ ------------- ------------
  27 Sep 10:01:06   |- kind: string                     4            794
                    |- fullName: string (required)
                    |- age: integer
                    |- gender: string
                    +- phoneNumber: record
                    |  |- areaCode: integer
                    |  |- number: integer
                    +- children: record (repeated)
                    |  |- name: string
                    |  |- gender: string
                    |  |- age: integer
                    +- citiesLived: record (repeated)
                    |  |- place: string
                    |  +- yearsLived: integer (repeated)

Ten en cuenta que hay varios campos anidados y repetidos. Si ejecutas una consulta de SQL heredado como la siguiente en la tabla sobre personas:

SELECT
  fullName AS name,
  age,
  gender,
  citiesLived.place,
  citiesLived.yearsLived
FROM [dataset.tableId]

BigQuery muestra los datos con una salida compactada:

+---------------+-----+--------+-------------------+------------------------+
|     name      | age | gender | citiesLived_place | citiesLived_yearsLived |
+---------------+-----+--------+-------------------+------------------------+
| John Doe      |  22 | Male   | Seattle           |                   1995 |
| John Doe      |  22 | Male   | Stockholm         |                   2005 |
| Mike Jones    |  35 | Male   | Los Angeles       |                   1989 |
| Mike Jones    |  35 | Male   | Los Angeles       |                   1993 |
| Mike Jones    |  35 | Male   | Los Angeles       |                   1998 |
| Mike Jones    |  35 | Male   | Los Angeles       |                   2002 |
| Mike Jones    |  35 | Male   | Washington DC     |                   1990 |
| Mike Jones    |  35 | Male   | Washington DC     |                   1993 |
| Mike Jones    |  35 | Male   | Washington DC     |                   1998 |
| Mike Jones    |  35 | Male   | Washington DC     |                   2008 |
| Mike Jones    |  35 | Male   | Portland          |                   1993 |
| Mike Jones    |  35 | Male   | Portland          |                   1998 |
| Mike Jones    |  35 | Male   | Portland          |                   2003 |
| Mike Jones    |  35 | Male   | Portland          |                   2005 |
| Mike Jones    |  35 | Male   | Austin            |                   1973 |
| Mike Jones    |  35 | Male   | Austin            |                   1998 |
| Mike Jones    |  35 | Male   | Austin            |                   2001 |
| Mike Jones    |  35 | Male   | Austin            |                   2005 |
| Anna Karenina |  45 | Female | Stockholm         |                   1992 |
| Anna Karenina |  45 | Female | Stockholm         |                   1998 |
| Anna Karenina |  45 | Female | Stockholm         |                   2000 |
| Anna Karenina |  45 | Female | Stockholm         |                   2010 |
| Anna Karenina |  45 | Female | Moscow            |                   1998 |
| Anna Karenina |  45 | Female | Moscow            |                   2001 |
| Anna Karenina |  45 | Female | Moscow            |                   2005 |
| Anna Karenina |  45 | Female | Austin            |                   1995 |
| Anna Karenina |  45 | Female | Austin            |                   1999 |
+---------------+-----+--------+-------------------+------------------------+

En este ejemplo, citiesLived.place ahora es citiesLived_place y citiesLived.yearsLived ahora es citiesLived_yearsLived.

A pesar de que BigQuery puede compactar los campos anidados automáticamente, tal vez necesites llamar de forma explícita a FLATTEN cuando se trata de más de un campo repetido. Por ejemplo, si intentas ejecutar una consulta de SQL heredado como la que se muestra a continuación:

SELECT fullName, age
FROM [dataset.tableId]
WHERE
  (citiesLived.yearsLived > 1995 ) AND
  (children.age > 3)

BigQuery muestra un error similar al siguiente:

Cannot query the cross product of repeated fields children.age and citiesLived.yearsLived

Para consultar en más de un campo repetido, debes compactar uno de los campos:

SELECT
  fullName,
  age,
  gender,
  citiesLived.place
FROM (FLATTEN([dataset.tableId], children))
WHERE
  (citiesLived.yearsLived > 1995) AND
  (children.age > 3)
GROUP BY fullName, age, gender, citiesLived.place

Que muestra lo siguiente:

+------------+-----+--------+-------------------+
|  fullName  | age | gender | citiesLived_place |
+------------+-----+--------+-------------------+
| John Doe   |  22 | Male   | Stockholm         |
| Mike Jones |  35 | Male   | Los Angeles       |
| Mike Jones |  35 | Male   | Washington DC     |
| Mike Jones |  35 | Male   | Portland          |
| Mike Jones |  35 | Male   | Austin            |
+------------+-----+--------+-------------------+

Cláusula WITHIN

La palabra clave WITHIN trabaja en forma específica con funciones agregadas para agregar datos en campos de niños y repetidos dentro de los registros y campos anidados. Cuando especificas la palabra clave WITHIN, debes especificar el permiso según el cual deseas agregar, como se indica a continuación:

  • WITHIN RECORD: Agrega datos en los valores repetidos en el registro.
  • WITHIN node_name: Agrega datos en los valores repetidos dentro del nodo especificado, en el que un nodo es un nodo superior del campo en la función de agregación.

Supongamos que quieres encontrar la cantidad de hijos que tiene cada persona en nuestro ejemplo anterior. Para hacerlo, puedes contar la cantidad de nombres de niños que tiene cada registro:

SELECT
  fullName,
  COUNT(children.name) WITHIN RECORD AS numberOfChildren
FROM [dataset.tableId];

Obtienes el resultado siguiente:

+---------------+------------------+
|   fullName    | numberOfChildren |
+---------------+------------------+
| John Doe      |                2 |
| Jane Austen   |                2 |
| Mike Jones    |                3 |
| Anna Karenina |                0 |
+---------------+------------------+

Con el fin de comparar, intenta enumerar todos los nombres de los niños:

SELECT fullName, children.name
FROM [dataset.tableId]
+---------------+---------------+
|   fullName    | children_name |
+---------------+---------------+
| John Doe      | Jane          |
| John Doe      | John          |
| Jane Austen   | Josh          |
| Jane Austen   | Jim           |
| Mike Jones    | Earl          |
| Mike Jones    | Sam           |
| Mike Jones    | Kit           |
| Anna Karenina | None          |
+---------------+---------------+

Esto coincide con nuestros resultados de la consulta WITHIN RECORD. John Doe tiene dos hijos llamados Jane y John; Jane Finten tiene dos hijos llamados Josh y Jim; Mike Jones tiene tres hijos llamados Earl, Sam y Kit, y Anna Karenina no tiene hijos.

Ahora, supongamos que quieres encontrar la cantidad de veces que una persona vivió en distintos lugares. Puedes usar la cláusula WITHIN para agregar en un nodo en particular:

SELECT
  fullName,
  COUNT(citiesLived.place) WITHIN RECORD AS numberOfPlacesLived,
  citiesLived.place,
  COUNT(citiesLived.yearsLived) WITHIN citiesLived AS numberOfTimesInEachCity,
FROM [dataset.tableId];
+---------------+---------------------+-------------------+-------------------------+
|   fullName    | numberOfPlacesLived | citiesLived_place | numberOfTimesInEachCity |
+---------------+---------------------+-------------------+-------------------------+
| John Doe      |                   2 | Seattle           |                       1 |
| John Doe      |                   2 | Stockholm         |                       1 |
| Mike Jones    |                   4 | Los Angeles       |                       4 |
| Mike Jones    |                   4 | Washington DC     |                       4 |
| Mike Jones    |                   4 | Portland          |                       4 |
| Mike Jones    |                   4 | Austin            |                       4 |
| Anna Karenina |                   3 | Stockholm         |                       4 |
| Anna Karenina |                   3 | Moscow            |                       3 |
| Anna Karenina |                   3 | Austin            |                       2 |
+---------------+---------------------+-------------------+-------------------------+

Esta consulta hace lo siguiente:

  • Realiza un WITHIN RECORD en citiesLived.place y cuenta la cantidad de lugares en los que vivió cada persona.
  • Realiza un WITHIN en citiesLived.yearsLived y cuenta la cantidad de veces que cada persona vivió en cada ciudad (solo cuenta en función de citiesLived).

Una de las características más poderosas de BigQuery es el uso de una agregación con un alcance delimitado sobre los campos anidados y repetidos, que muchas veces puede quitar uniones costosas en las consultas.