Interrogtement de données imbrication

AWS Clean Roomsoffre un accès compatible avec le langage SQL aux données relationnelles ou imbrication.

AWS Clean Roomsutilise la notation par tableau et le désimbrication par tableau pour la navigation par chemin et par tableau. Il permet également deFROMéléments de clause à itérer sur des tableau et à utiliser pour les opérations désimbrication. Les rubriques suivantes décrivent les différents modèles de requête qui combinent l'utilisation du type de données par tableau, la désimbrication ou les jointures.

Navigation

AWS Clean Roomspermet de naviguer dans les tableaux et les structures à l'aide du[...]notation entre crochets et points respectivement. En outre, vous pouvez mélanger la navigation dans des structures en utilisant notation par points avec la navigation dans des tableaux en utilisant la notation entre crochets.

Par exemple, l'exemple de requête suivant part du principe quec_ordersla colonne de données par tableau est un tableau avec une structure et un attribut est nomméo_orderkey.

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

Vous pouvez utiliser la notation par points et crochets dans tous les types de requêtes, comme le filtrage, la jointure et l'agrégation. Vous pouvez utiliser ces notations dans une requête dans laquelle il y a normalement des références de colonne.

L'exemple suivant utilise une instruction SELECT qui filtre les résultats.

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

L'exemple suivant utilise la notation par points et crochets dans les clauses GROUP BY et 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;

Désimbriquer des requêtes

Pour annuler les requêtes,AWS Clean Roomspermet l'itération sur des tableaux. Pour ce faire, il navigue dans le tableau à l'aide de la clause FROM d'une requête.

En utilisant l'exemple précédent, le suivant itère sur les valeurs de l'attribut pour c_orders.

SELECT o FROM customer_orders_lineitem c, c.c_orders o;

La syntaxe de désimbrication est une extension de la clause FROM. Dans SQL standard, la clause FROM x (AS) y signifie que y itère sur chaque tuple dans la relation x. Dans ce cas, x fait référence à une relation et y fait référence à un alias pour la relation x. De même, la syntaxe de désimbrication à l'aide de l'élément de clause FROMx (AS) ysignifie queyitère sur chaque valeur d'une expression matriciellex. Dans ce cas,xest une expression matricielle etyest un alias pourx.

L'opérande de gauche peut également utiliser la notation par points et crochets pour la navigation régulière.

Dans l'exemple précédent :

• customer_orders_lineitem cest l'itération surcustomer_order_lineitemtable de base

• c.c_orders oest l'itération surc.c_orders array

Pour itérer sur l'attribut o_lineitems, qui est un tableau dans un tableau, vous ajoutez plusieurs clauses.

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

AWS Clean Roomsprend également en charge un index par tableau lors de l'itbrication sur le tableau à l'aide duATmot clé. La clausex AS y AT zitère sur un tableauxet génère le champz, qui est l'index du tableau.

L'exemple suivant illustre le fonctionnement d'un index de tableau.

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)

L'exemple suivant itère sur un tableau scalaire

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)

L'exemple suivant itère sur un tableau de plusieurs niveaux. L'exemple utilise plusieurs clauses de désimbrication (unnest) pour effectuer une itération dans les tableaux les plus intérieurs. Lef.multi_level_array ASle tableau itèremulti_level_array. Le tableauASl'élément est l'itbrication par tableau à l'intérieurmulti_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)

Sémantique laxiste

Par défaut, les opérations de navigation sur des valeurs de données imbrication renvoient la valeur nulle au lieu de renvoyer une erreur lorsque la navigation n'est pas valide. La navigation par objet n'est pas valide si la valeur de données imbrication n'est pas un objet ou si la valeur de données imbrication n'est pas un objet ou si la valeur de données imbrication n'est pas un objet.

Par exemple, la requête suivante accède à un nom d'attribut non valide dans la colonne de données imbricationc_orders:

SELECT c.c_orders.something FROM customer_orders_lineitem c;

La navigation par tableau renvoie la valeur de données imbrication si la valeur des données imbrication n'est pas un tableau ou si l'index du tableau est hors limites.

La requête suivante renvoie la valeur Null, carc_orders[1][1]est hors limites.

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

Types d'introspection

Les colonnes de type de données imbrication prennent en charge les fonctions d'inspection qui renvoient le type et d'autres informations de type de données concernant la valeur. AWS Clean Rooms prend en charge les fonctions booléennes suivantes pour les colonnes de données imbrication :

• 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

Toutes ces fonctions renvoient false si la valeur d'entrée est nulle. IS_SCALAR, IS_OBJECT et IS_ARRAY s'excluent mutuellement et couvrent toutes les valeurs possibles à l'exception de null. Pour déduire les types correspondant aux données,AWS Clean Roomsutilise la fonction JSON_TYPEOF qui renvoie le type (le niveau supérieur de) la valeur de données imbrication, comme le montre l'exemple suivant :

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