Navegación Desanidar consultas Anulación de la dinamización de los objetos Escritura dinámica Semántica laxa Ordenar por

Consulta de datos semiestructurados

Con Amazon Redshift, puede consultar y analizar datos semiestructurados, como JSON, Avro o Ion, junto con los datos estructurados. Los datos semiestructurados se refieren a los datos que tienen un esquema flexible, lo que permite estructuras jerárquicas o anidadas. En las siguientes secciones, se muestra la consulta de datos semiestructurados mediante el uso del soporte de Amazon Redshift para formatos de datos abiertos, lo que le permite obtener información valiosa de estructuras de datos complejas.

Amazon Redshift utiliza el lenguaje PartiQL para ofrecer acceso compatible con SQL a datos relacionales, semiestructurados y anidados.

PartiQL funciona con tipos dinámicos. Este enfoque permite filtrar, unir y agrupar de forma intuitiva la combinación de conjuntos de datos estructurados, semiestructurados y anidados. La sintaxis PartiQL utiliza notación con puntos y subíndice de matriz para la navegación de rutas cuando se accede a datos anidados. También habilita los elementos de la cláusula FROM para iterar sobre matrices y utilizarlos para las operaciones de eliminación de anidamiento. A continuación, se describen los diferentes patrones de consulta que combinan el uso del tipo de datos SUPER con la navegación, la anulación de dinamización, la eliminación de anidamiento o las uniones de las rutas y las matrices. Para obtener más información acerca de PartiQL, consulte PartiQL: un lenguaje de consulta compatible con SQL para Amazon Redshift.

Amazon Redshift utiliza PartiQL para permitir la navegación hacia matrices y estructuras mediante la notación […] con corchetes y puntos respectivamente. Además, puede mezclar la navegación en estructuras mediante la notación con puntos y matrices con la notación con corchetes. Por ejemplo, en la siguiente instrucción se selecciona solo el tercer elemento de una matriz anidada a un nivel de profundidad en un objeto SUPER:


SELECT super_object.array[2];
         
 array
---------------
 third_element

Puede usar la notación de puntos y corchetes para realizar operaciones de datos como filtrar, unir y agregar. Puede utilizar estas notaciones en cualquier lugar de una consulta en el que por lo general hay referencias de columnas. Por ejemplo, la siguiente instrucción selecciona el número de eventos con el tipo UPDATED.


SELECT COUNT(*)
FROM test_json
WHERE all_data.data.pnr.events[0]."eventType" = 'UPDATED';
         
 eventType | count
-----------+-------
 "UPDATED" | 1

Para ver ejemplos más detallados sobre el uso de la navegación de PartiQL, consulte Ejemplos de uso de datos semiestructurados en Amazon Redshift.

Consultas de eliminación del anidamiento

Para anular el anidamiento de las consultas, Amazon Redshift ofrece dos formas de iterar sobre matrices SUPER: la sintaxis PartiQL y la operación UNNEST en la cláusula FROM. Ambos métodos de anulación de anidamiento producen el mismo resultado. Para obtener más información sobre la operación UNNEST, consulte Cláusula FROM. Para obtener ejemplos de uso de la operación UNNEST, consulte Ejemplos de UNNEST.

Amazon Redshift puede navegar por matrices SUPER mediante la sintaxis PartiQL en la cláusula FROM de una consulta. Continuando con el ejemplo anterior, el siguiente ejemplo itera los valores de atributo de c_orders.


SELECT orders.*, o FROM customer_orders orders, orders.c_orders o;

La sintaxis de PartiQL de eliminación de anidamiento con el elemento de cláusula FROM x (AS) y significa que y itera sobre cada valor (SUPER) en la expresión de matriz (SUPER) x. En este caso, x es una expresión SUPER y 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 siguiente, customer_orders_lineitem c es la iteración sobre la tabla base customer_order_lineitem y c.c_orders o es la iteración sobre la matriz c.c_orders. Para iterar el atributo o_lineitems, que es una matriz dentro de otra matriz, puede agregar varias cláusulas, como:


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

Amazon Redshift también admite un índice de matrices cuando se itera sobre la matriz usando la palabra clave AT. La cláusula x AS y AT z itera sobre 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)

A continuación, se muestra un ejemplo de iteración sobre una matriz escalar.


CREATE TABLE bar AS SELECT json_parse('{"scalar_array": [1, 2.3, 45000000]}') AS data;

SELECT element, index 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 no anidadas para iterar en las matrices más internas. La matriz AS f.multi_level_array itera sobre multi_level_array. El elemento AS de la matriz representa la iteración sobre las matrices dentro de 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;

 element | array
---------+---------
 1.1     | [1.1,1.2]
 1.2     | [1.1,1.2]
 2.1     | [2.1,2.2] 
 2.2     | [2.1,2.2]
 3.1     | [3.1,3.2] 
 3.2     | [3.1,3.2] 
(6 rows)

Para obtener más información acerca de la cláusula FROM, consulte Cláusula FROM. Para ver más ejemplos de cómo eliminar el anidamiento de consultas SUPER, consulte Ejemplos de uso de datos semiestructurados en Amazon Redshift.

Anulación de la dinamización de los objetos

Para anular la dinamización del objeto, Amazon Redshift utiliza la sintaxis de PartiQL para iterar sobre objetos SUPER. Para ello, utiliza la cláusula FROM de una consulta junto con la palabra clave UNPIVOT. En el ejemplo siguiente, la expresión es el objeto c.c_orders[0]. La consulta de ejemplo itera sobre cada atributo devuelto por el objeto.


SELECT attr as attribute_name, json_typeof(val) as value_type 
FROM customer_orders_lineitem c, UNPIVOT c.c_orders[0] AS val AT attr 
WHERE c_custkey = 9451;

 attribute_name  | value_type
-----------------+------------
 o_orderstatus   | string
 o_clerk         | string
 o_lineitems     | array
 o_orderdate     | string
 o_shippriority  | number
 o_totalprice    | number
 o_orderkey      | number
 o_comment       | string
 o_orderpriority | string
(9 rows)

Al igual que para eliminar el anidamiento, la sintaxis para anular la dinamización también es una extensión de la cláusula FROM. La diferencia es que la sintaxis para anular la dinamización utiliza la palabra clave UNPIVOT para indicar que está iterando sobre un objeto en lugar de una matriz. Utiliza AS value_alias para iterar sobre todos los valores dentro de un objeto y utiliza AT attribute_alias para iterar sobre todos los atributos. Supongamos que tenemos la siguiente sintaxis:


UNPIVOT expression AS value_alias [ AT attribute_alias ]

Amazon Redshift admite la anulación de la dinamización de objetos y la eliminación del anidamiento de matrices en una sola cláusula FROM de la siguiente manera:


SELECT attr as attribute_name, val as object_value
FROM customer_orders_lineitem c, c.c_orders AS o, UNPIVOT o AS val AT attr 
WHERE c_custkey = 9451;

Cuando se utiliza la anulación de la dinamización de objetos, Amazon Redshift no admite la anulación correlacionada. En concreto, supongamos que tiene un caso en el que hay varios ejemplos de anulación de la dinamización en diferentes niveles de consulta y la anulación de la dinamización interna hace referencia al exterior. Amazon Redshift no admite este tipo de anulación de la dinamización múltiple.

Para obtener más información acerca de la cláusula FROM, consulte Cláusula FROM. Para ver ejemplos del uso del pivote con el tipo SUPER, consulte Ejemplos de uso de datos semiestructurados en Amazon Redshift.

Escritura dinámica

La escritura dinámica no requiere la conversión explícita de los datos que se extraen de las rutas con puntos y corchetes. Amazon Redshift utiliza la escritura dinámica para procesar datos SUPER sin esquema sin necesidad de declarar los tipos de datos antes de utilizarlos en la consulta. La escritura dinámica utiliza los resultados de navegar por columnas de datos SUPER sin tener que convertirlos explícitamente en tipos de Amazon Redshift. La escritura dinámica es más útil en las uniones y las cláusulas GROUP BY. En el siguiente ejemplo, se utiliza una instrucción SELECT que no requiere ninguna conversión explícita de las expresiones con puntos y corchetes en los tipos habituales de Amazon Redshift. Para obtener más información acerca de la compatibilidad y la conversión de tipos, consulte Conversión y compatibilidad de tipos.

Considere el ejemplo siguiente, que busca filas en las que el estado de un pedido es shipped:


SELECT c_orders[0].o_orderkey
FROM customer_orders_lineitem
WHERE c_orders[0].o_orderstatus = 'shipped';

El signo de igualdad en esta consulta de ejemplo se evalúa como true cuando el valor de c_orders[0].o_orderstatus es la cadena «enviado». En todos los demás casos, el signo de igualdad se evalúa como false, incluidos los casos en los que los argumentos de la igualdad son tipos diferentes. Por ejemplo, si el estado del pedido es un número entero, no se seleccionará su fila.

Escritura dinámica y estática

Sin usar la escritura dinámica, no se puede determinar si c_orders[0].o_orderstatus es una cadena, un entero o una estructura. Solo puede determinar que c_orders[0].o_orderstatus es un tipo de datos SUPER, que puede ser un escalar, una matriz o una estructura de Amazon Redshift. El tipo estático de c_orders[0].o_orderstatus es un tipo de datos SUPER. De forma convencional, un tipo es implícitamente un tipo estático en SQL.

Amazon Redshift utiliza la escritura dinámica para el procesamiento de datos sin esquema. Cuando la consulta evalúa los datos, c_orders[0].o_orderstatus resulta ser un tipo específico. Por ejemplo, evaluar c_orders[0].o_orderstatus en el primer registro de customer_orders_lineitem puede resultar en un entero. La evaluación en el segundo registro puede resultar en una cadena. Estos son los tipos dinámicos de la expresión.

Cuando se utiliza un operador o una función SQL con expresiones con puntos y corchetes que tienen tipos dinámicos, Amazon Redshift produce resultados similares a utilizar el operador o la función SQL estándar con los respectivos tipos estáticos. En este ejemplo, cuando el tipo dinámico de la expresión de ruta es una cadena, la comparación con la cadena “P” es significativa. Siempre que el tipo dinámico de c_orders[0].o_orderstatus sea cualquier otro tipo de datos, excepto que sea una cadena, la igualdad devolverá el resultado false. Otras funciones devuelven valores nulos cuando se usan argumentos mal escritos.

En el siguiente ejemplo, se la consulta anterior se escribe con escritura estática:


SELECT c_custkey
FROM customer_orders_lineitem
WHERE CASE WHEN JSON_TYPEOF(c_orders[0].o_orderstatus) = 'string'
           THEN c_orders[0].o_orderstatus::VARCHAR = 'P'
           ELSE FALSE END;

Tenga en cuenta la siguiente distinción entre predicados de igualdad y predicados de comparación. En el ejemplo anterior, si el predicado de igualdad se reemplaza con un predicado de menor que o igual, la semántica produce un valor nulo en vez de uno false.


SELECT c_orders[0]. o_orderkey
FROM customer_orders_lineitem
WHERE c_orders[0].o_orderstatus <= 'P';

En este ejemplo, si c_orders[0].o_orderstatus es una cadena, Amazon Redshift devuelve el valor true si es alfabéticamente igual o menor que “P”. Amazon Redshift devuelve un valor false si es alfabéticamente mayor que “P”. Sin embargo, si c_orders[0].o_orderstatus no es una cadena, Amazon Redshift devuelve un valor “null” (nulo) debido a que Amazon Redshift no puede comparar valores de diferentes tipos, como se muestra en la consulta siguiente:


SELECT c_custkey
FROM customer_orders_lineitem
WHERE CASE WHEN JSON_TYPEOF(c_orders[0].o_orderstatus) = 'string'
           THEN c_orders[0].o_orderstatus::VARCHAR <= 'P'
           ELSE NULL END;

La escritura dinámica no efectúa exclusiones de las comparaciones de tipos que son mínimamente comparables. Por ejemplo, puede convertir los tipos escalares CHAR y VARCHAR de Amazon Redshift en SUPER. Estos tipos son comparables como cadenas, lo que incluye ignorar los caracteres de espacio en blanco finales similares a los tipos CHAR y VARCHAR de Amazon Redshift. De manera similar, los valores enteros, decimales y de coma flotante son comparables como valores SUPER. Específicamente para las columnas decimales, cada valor también puede tener una escala diferente. Amazon Redshift sigue considerándolos como tipos dinámicos.

Amazon Redshift también admite la igualdad en los objetos y las matrices que se evalúan como de igualdad profunda, tales como la evaluación profunda de objetos o matrices y la comparación de todos los atributos. Utilice la igualdad profunda con precaución, ya que el proceso de realizar la igualdad profunda puede llevar mucho tiempo.

Uso de la escritura dinámica para las uniones

Para las uniones, la escritura dinámica combina automáticamente los valores con diferentes tipos dinámicos, sin tener que realizar un largo análisis de CASE WHEN para averiguar qué tipos de datos pueden aparecer. Por ejemplo, suponga que, con el tiempo, su organización cambió el formato que utilizaba para las claves de partes.

Las claves iniciales de partes de enteros emitidas se reemplazan por claves de partes de cadenas, como “A55”, y, luego, se reemplazan de nuevo por claves de partes de matrices, como [‘X’, 10], que combina una cadena y un número. Amazon Redshift no tiene que realizar un análisis prolongado de casos sobre las claves de partes y puede utilizar uniones, como se muestra en el ejemplo siguiente.


SELECT c.c_name
    ,l.l_extendedprice
    ,l.l_discount
FROM customer_orders_lineitem c
    ,c.c_orders o
    ,o.o_lineitems l
    ,supplier_partsupp s
    ,s.s_partsupps ps
WHERE l.l_partkey = ps.ps_partkey
AND c.c_nationkey = s.s_nationkey
ORDER BY c.c_name;

En el siguiente ejemplo, se muestra cuán compleja e ineficiente puede ser la misma consulta si no se usa la escritura dinámica:


SELECT c.c_name
    ,l.l_extendedprice
    ,l.l_discount
FROM customer_orders_lineitem c
    ,c.c_orders o
    ,o.o_lineitems l
    ,supplier_partsupp s
    ,s.s_partsupps ps
WHERE CASE WHEN IS_INTEGER(l.l_partkey) AND IS_INTEGER(ps.ps_partkey)
           THEN l.l_partkey::integer = ps.ps_partkey::integer
           WHEN IS_VARCHAR(l.l_partkey) AND IS_VARCHAR(ps.ps_partkey)
           THEN l.l_partkey::varchar = ps.ps_partkey::varchar
           WHEN IS_ARRAY(l.l_partkey) AND IS_ARRAY(ps.ps_partkey)
                AND IS_VARCHAR(l.l_partkey[0]) AND IS_VARCHAR(ps.ps_partkey[0])
                AND IS_INTEGER(l.l_partkey[1]) AND IS_INTEGER(ps.ps_partkey[1])
           THEN l.l_partkey[0]::varchar = ps.ps_partkey[0]::varchar
                AND l.l_partkey[1]::integer = ps.ps_partkey[1]::integer
           ELSE FALSE END
AND c.c_nationkey = s.s_nationkey
ORDER BY c.c_name;

Semántica laxa

De manera predeterminada, las operaciones de navegación en los valores SUPER 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 SUPER no es un objeto o si el valor SUPER es un objeto, pero no contiene el nombre del atributo utilizado en la consulta. Por ejemplo, la siguiente consulta tiene acceso a un nombre de atributo no válido de la columna de datos SUPER cdata:


SELECT c.c_orders.something FROM customer_orders_lineitem c;

La navegación por matrices devuelve el valor nulo si el valor SUPER no es una matriz o si el índice de matriz está fuera de los límites. La siguiente consulta devuelve el valor nulo porque c_orders[1][1] está fuera de los límites.


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

La semántica laxa es especialmente útil cuando se utiliza la escritura dinámica para convertir un valor SUPER. Convertir un valor SUPER en un tipo incorrecto devuelve el valor nulo en lugar de un error si la conversión no es válida. Por ejemplo, la siguiente consulta devuelve el valor nulo porque no puede convertir el valor de cadena “Good” del atributo de objeto o_orderstatus en INTEGER. Amazon Redshift devuelve un error para una conversión de VARCHAR en INTEGER, pero no para una conversión SUPER.


SELECT c.c_orders.o_orderstatus::integer FROM customer_orders_lineitem c;

Ordenar por

Amazon Redshift no define comparaciones SUPER entre valores con diferentes tipos dinámicos. El valor SUPER que es una cadena no es más pequeño ni más grande que un valor SUPER que es un número. Para utilizar cláusulas ORDER BY con columnas SUPER, Amazon Redshift define un orden total entre los diferentes tipos que se deben observar cuando Amazon Redshift clasifica los valores SUPER mediante cláusulas ORDER BY. El orden entre los tipos dinámicos es booleano, de número, de cadena, de matriz, de objeto.

Para ver un ejemplo del uso de GROUP BY y ORDER BY en una consulta SUPER, consulte Filtrado de datos semiestructurados.

Aviso JavaScript está desactivado o no está disponible en su navegador.

Para utilizar la documentación de AWS, debe estar habilitado JavaScript. Para obtener más información, consulte las páginas de ayuda de su navegador.

Convenciones del documento

Descarga de datos semiestructurados

Operadores y funciones