Consultar datos anidados - AWS Clean Rooms

Las traducciones son generadas a través de traducción automática. En caso de conflicto entre la traducción y la version original de inglés, prevalecerá la version en inglés.

Consultar datos anidados

AWS Clean Rooms ofrece acceso compatible con SQL a datos relacionales y anidados.

AWS Clean Rooms utiliza notación con puntos y subíndice de matriz para la navegación de rutas al acceder a datos anidados. También habilita los elementos de cláusula FROM para iterar matrices y utilizarlas para las operaciones de desanidamiento. Los siguientes temas ofrecen descripciones de los diferentes patrones de consulta que combinan el uso del tipo de datos matriz/estructura/mapa con la navegación, el desanidamiento y la combinación de rutas y matrices.

AWS Clean Rooms permite la navegación en matrices y estructuras utilizando la notación con corchetes [...] y puntos, respectivamente. Además, puede combinar la navegación en estructuras utilizando la notación con puntos y matrices con la notación con corchetes.

Por ejemplo, en la siguiente consulta de ejemplo, se presupone que la columna de datos de matriz c_orders es una matriz con una estructura y que un atributo se denomina o_orderkey.

SELECT cust.c_orders[0].o_orderkey FROM customer_orders_lineitem AS cust;

Puede utilizar las notaciones con puntos y corchetes en todos los tipos de consultas, como las de filtrado, combinación y agregación. También puede utilizar estas notaciones en una consulta en la que por lo general hay referencias de columnas.

En el siguiente ejemplo, se utiliza una instrucción SELECT que filtra los resultados.

SELECT count(*) FROM customer_orders_lineitem WHERE c_orders[0].o_orderkey IS NOT NULL;

En el siguiente ejemplo, se utiliza la navegación con corchetes y puntos tanto en las cláusulas GROUP BY como ORDER BY.

SELECT c_orders[0].o_orderdate, c_orders[0].o_orderstatus, count(*) FROM customer_orders_lineitem WHERE c_orders[0].o_orderkey IS NOT NULL GROUP BY c_orders[0].o_orderstatus, c_orders[0].o_orderdate ORDER BY c_orders[0].o_orderdate;

Desanidar consultas

Para desanidar consultas, AWS Clean Rooms permite la iteración sobre matrices. Para ello, navega por la matriz utilizando la cláusula FROM de una consulta.

Continuando con el ejemplo anterior, el siguiente ejemplo itera los valores de atributo de c_orders.

SELECT o FROM customer_orders_lineitem c, c.c_orders o;

La sintaxis de desanidamiento es una extensión de la cláusula FROM. En SQL estándar, la cláusula FROM x (AS) y significa que y itera cada tupla en relación con x. En este caso, x hace referencia a una relación e y hace referencia a un alias de relación x. Del mismo modo, la sintaxis de desanidamiento con el elemento de cláusula FROM x (AS) y significa que y itera cada valor en la expresión de matriz x. En este caso, x es una expresión de matriz e y es un alias de x.

El operando izquierdo también puede utilizar la notación con puntos y corchetes para la navegación normal.

En el ejemplo anterior:

  • customer_orders_lineitem c es la iteración sobre la tabla base customer_order_lineitem

  • c.c_orders o es la iteración sobre la c.c_orders array

Para iterar el atributo o_lineitems, que es una matriz dentro de otra matriz, debe añadir varias cláusulas.

SELECT o, l FROM customer_orders_lineitem c, c.c_orders o, o.o_lineitems l;

AWS Clean Rooms también admite un índice de matrices al iterar la matriz usando la palabra clave AT. La cláusula x AS y AT z itera la matriz x y genera el campo z, que es el índice de la matriz.

En el siguiente ejemplo se muestra cómo funciona un índice de matrices.

SELECT c_name, orders.o_orderkey AS orderkey, index AS orderkey_index FROM customer_orders_lineitem c, c.c_orders AS orders AT index ORDER BY orderkey_index; c_name | orderkey | orderkey_index -------------------+----------+---------------- Customer#000008251 | 3020007 | 0 Customer#000009452 | 4043971 | 0 (2 rows)

En el siguiente ejemplo se itera una matriz escalar.

CREATE TABLE bar AS SELECT json_parse('{"scalar_array": [1, 2.3, 45000000]}') AS data; SELECT index, element FROM bar AS b, b.data.scalar_array AS element AT index; index | element -------+---------- 0 | 1 1 | 2.3 2 | 45000000 (3 rows)

En el siguiente ejemplo se itera una matriz de varios niveles. En el ejemplo se utilizan varias cláusulas de desanidamiento para iterar en las matrices más internas. La matriz f.multi_level_array AS itera multi_level_array. El elemento AS de la matriz representa la iteración sobre las matrices en multi_level_array.

CREATE TABLE foo AS SELECT json_parse('[[1.1, 1.2], [2.1, 2.2], [3.1, 3.2]]') AS multi_level_array; SELECT array, element FROM foo AS f, f.multi_level_array AS array, array AS element; array | element -----------+--------- [1.1,1.2] | 1.1 [1.1,1.2] | 1.2 [2.1,2.2] | 2.1 [2.1,2.2] | 2.2 [3.1,3.2] | 3.1 [3.1,3.2] | 3.2 (6 rows)

Semántica laxa

De manera predeterminada, las operaciones de navegación en valores de datos anidados devuelven valores nulos en lugar de devolver un error cuando la navegación no es válida. La navegación por objetos no es válida si el valor de datos anidado no es un objeto, o si el valor de datos anidado es un objeto, pero no contiene el nombre del atributo utilizado en la consulta.

Por ejemplo, la siguiente accede a un nombre de atributo no válido de la columna de datos anidados c_orders:

SELECT c.c_orders.something FROM customer_orders_lineitem c;

La navegación por matrices devuelve el valor nulo si el valor de datos anidado no es una matriz o si el índice de la matriz está fuera de límites.

La siguiente consulta devuelve el valor nulo porque c_orders[1][1] está fuera de límites.

SELECT c.c_orders[1][1] FROM customer_orders_lineitem c;

Tipos de introspección

Las columnas de datos anidados admiten funciones de inspección que devuelven el tipo y otra información del tipo relativa al valor. AWS Clean Rooms admite las siguientes funciones booleanas para las columnas de datos anidados:

  • DECIMAL_PRECISION

  • DECIMAL_SCALE

  • IS_ARRAY

  • IS_BIGINT

  • IS_CHAR

  • IS_DECIMAL

  • IS_FLOAT

  • IS_INTEGER

  • IS_OBJECT

  • IS_SCALAR

  • IS_SMALLINT

  • IS_VARCHAR

  • JSON_TYPEOF

Todas estas funciones devuelven un valor false si el valor de entrada es nulo. IS_SCALAR, IS_OBJECT e IS_ARRAY son mutuamente excluyentes y cubren todos los valores posibles, excepto los nulos. Para inferir los tipos correspondientes a los datos, AWS Clean Rooms utiliza la función JSON_TYPEOF, que devuelve el tipo (el nivel superior) del valor de datos anidados, como se muestra en el siguiente ejemplo:

SELECT JSON_TYPEOF(r_nations) FROM region_nations; json_typeof ------------- array (1 row)
SELECT JSON_TYPEOF(r_nations[0].n_nationkey) FROM region_nations; json_typeof ------------- number