Consultar dados aninhados

AWS Clean Rooms oferece acesso compatível com SQL a dados relacionais e aninhados.

AWS Clean Rooms usa notação pontilhada e subscrito de matriz para navegação de caminho ao acessar dados aninhados. Ele também permite que os itens da cláusula FROM iterem sobre matrizes e sejam usados para operações de desagrupamento. Os tópicos a seguir fornecem descrições dos diferentes padrões de consulta que combinam o uso do tipo de dados array/struct/map com navegação de caminho e matriz, desaninhamento e junções.

Navegação

AWS Clean Rooms permite a navegação em matrizes e estruturas usando a notação de colchetes [...] e pontos, respectivamente. Além disso, você pode misturar navegação em estruturas usando a notação de pontos e matrizes usando a notação de colchetes.

Por exemplo, o exemplo de consulta a seguir pressupõe que a coluna de dados da matriz c_orders é uma matriz com uma estrutura e um atributo denominado o_orderkey.​

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

Você pode usar as notações de ponto e colchetes em todos os tipos de consultas, como filtragem, junção e agregação. Você pode usar essas notações em uma consulta na qual normalmente há referências de coluna.

O exemplo a seguir usa uma instrução SELECT que filtra resultados.

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

O exemplo a seguir usa a navegação entre colchetes e pontos nas cláusulas GROUP BY e 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;

Desaninhar consultas

Para desaninhar consultas, AWS Clean Rooms habilita a iteração em matrizes. Ele faz isso navegando pela matriz usando a cláusula FROM de uma consulta.

Com o exemplo anterior, o exemplo a seguir itera sobre os valores do atributo para c_orders.

SELECT o FROM customer_orders_lineitem c, c.c_orders o;

A sintaxe de desaninhamento é uma extensão da cláusula FROM. No SQL padrão, a cláusula FROM x (AS) y significa que y itera sobre cada tupla em relação a x. Nesse caso, x refere-se a uma relação, e y refere-se a um alias para a relação x. Da mesma forma, a sintaxe de desaninhamento usando o item da cláusula FROM x (AS) y significa que y itera sobre cada valor na expressão da matriz x. Nesse caso, x é uma expressão de matriz e y é um alias para x.

O operando esquerdo também pode usar a notação de pontos e colchetes para navegação regular.

No exemplo anterior:

• customer_orders_lineitem c é a iteração sobre a tabela base customer_order_lineitem

• c.c_orders o é a iteração sobre o c.c_orders array

Para iterar sobre atributo o_lineitems, que é uma matriz dentro de uma matriz, é necessário adicionar várias cláusulas.

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

AWS Clean Rooms também oferece suporte a um índice de matriz ao iterar sobre a matriz usando a palavra-chave AT. A cláusula x AS y AT z itera sobre a matriz x e gera o campo z, que é o índice de matriz.

O exemplo a seguir mostra como o índice da matriz funciona.

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)

O exemplo a seguir itera sobre uma 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)

O exemplo a seguir itera sobre uma matriz de vários níveis. O exemplo usa várias cláusulas unnest para iterar nas matrizes mais internas. A matriz f.multi_level_array AS itera sobre multi_level_array. O elemento de matriz AS é a iteração sobre as matrizes 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;

   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 lax

Por padrão, as operações de navegação em valores de dados aninhados retornam nulo em vez de retornar um erro quando a navegação é inválida. A navegação de objetos será inválida se o valor dos dados aninhados não for um objeto ou se o valor dos dados aninhados for um objeto, mas não contiver o nome do atributo usado na consulta.

Por exemplo, a consulta a seguir acessa um nome de atributo inválido na coluna de dados aninhados c_orders:

SELECT c.c_orders.something FROM customer_orders_lineitem c;

A navegação da matriz retornará nulo se o valor dos dados aninhados não for uma matriz ou se o índice da matriz estiver fora dos limites.

A consulta a seguir retorna nulo porque c_orders[1][1] está fora dos limites.

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

Tipos de introspecção

Colunas de tipo de dados aninhadas suportam funções de inspeção que retornam o tipo e outras informações de tipo sobre o valor. O AWS Clean Rooms oferece suporte às seguintes funções booleanas para colunas de dados aninhados:

• 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 essas funções retornam false se o valor de entrada for nulo. IS_SCALAR, IS_OBJECT e IS_ARRAY são mutuamente exclusivos e cobrem todos os valores possíveis, exceto nulo. Para inferir os tipos correspondentes aos dados, AWS Clean Rooms usa a função JSON_TYPEOF que retorna o tipo (o nível superior) do valor de dados aninhados, conforme mostrado no exemplo a seguir:

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