Navigation (導覽)解除巢狀化查詢物件取消樞紐動態類型寬鬆的語義自我檢查的種類排序依據

查詢半結構化資料

Amazon Redshift 使用 PartiQL 語言來提供對關聯式、半結構化和巢狀資料的 SQL 相容存取。

PartiQL 使用動態類型進行運作。這種方法可在結構化、半結構化和巢狀資料集的組合上實現直覺式篩選、聯結和彙總。存取巢狀資料時，PartiQL 語法會使用點符號和陣列下標來進行路徑導覽。它也可讓 FROM 子句項目迭代陣列，並用於解除巢狀化操作。接下來，您可以找到不同查詢模式的描述，這些模式結合了使用 SUPER 資料類型與路徑和陣列導覽、解除巢狀化、取消樞紐和聯結。

如需下列範例中所用資料表的詳細資訊，請參閱SUPER 範例資料集。

Amazon Redshift 使用 PartiQL，分別使用 [...] 括號和點符號啟用導覽到陣列和結構中。此外，您也可以使用點符號將導覽混合到結構中，以及使用括號符號將導覽混合到陣列中。例如，下列範例假設 c_orders SUPER 資料欄是具有結構的陣列，且屬性已命名為 o_orderkey。

若要擷取 customer_orders_lineitem 資料表中的資料，請執行以下命令。以您自己的憑證取代 IAM 角色。


COPY customer_orders_lineitem FROM 's3://redshift-downloads/semistructured/tpch-nested/data/json/customer_orders_lineitem'
REGION 'us-east-1' IAM_ROLE 'arn:aws:iam::xxxxxxxxxxxx:role/Redshift-S3'
FORMAT JSON 'auto';

SELECT c_orders[0].o_orderkey FROM customer_orders_lineitem;

Amazon Redshift 也使用資料表別名做為表示法的字首。以下範例與前面的範例是相同的查詢。


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

您可以在所有類型的查詢中使用點和括號符號，例如篩選、聯結和彙總。您可以在查詢中使用這些符號，其中通常有欄參考。下列範例會使用篩選結果的 SELECT 陳述式。


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

下列範例會在 GROUP BY 和 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;

解除巢狀化查詢

為了解除巢狀化查詢，Amazon Redshift 使用 PartiQL 語法來迭代 SUPER 陣列。它透過使用查詢的 FROM 子句導覽陣列來實現此目的。使用上一個範例，下列範例會迭代 c_orders 的屬性值。


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

解除巢狀化語法是 FROM 子句的擴充功能。在標準 SQL 中，FROM 子句 x (AS) y 代表 y 迭代關係 x 的每個元組。在這種情況下，x 指的是關係，而 y 指的是關係 x 的別名。同樣地，使用 FROM 子句項目 x (AS) y 解除巢狀化的 PartiQL 語法表示 y 會迭代 (SUPER) 陣列運算式 x 中的每個 (SUPER) 值。在這種情況下，x 是 SUPER 運算式，而 y 是 x 的別名。

左運算元也可以使用點和括號符號進行常規導覽。在上一個範例中，customer_orders_lineitem c 是 customer_order_lineitem 基底資料表上的迭代，而 c.c_orders o 是 c.c_orders 陣列上的迭代。若要迭代 o_lineitems 屬性 (即陣列中的陣列)，您可以新增多個子句。


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

使用 AT 關鍵字迭代陣列時，Amazon Redshift 也支援陣列索引。子句 x AS y AT z 會迭代陣列 x 並產生做為陣列索引的欄位 z,。下列範例顯示陣列索引的運作方式。


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)

下列範例會迭代純量陣列。


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)

下列範例會迭代多個層級的陣列。這個範例會使用多個解除巢狀化子句來迭代到最內層的陣列。f.multi_level_array AS 陣列會迭代 multi_level_array。陣列 AS 元素是在 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)

如需 FROM 子句的相關資訊，請參閱FROM 子句。

物件取消樞紐

若要執行物件取消樞紐，Amazon Redshift 會使用 PartiQL 語法來迭代 SUPER 物件。它使用具有 UNPIVOT 關鍵字的查詢的 FROM 子句來執行此操作。在這種情況下，表達式是對c.c_orders[0]象。範例查詢會重複執行物件傳回的每個屬性。


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)

如同解除巢狀化結構，取消樞紐語法也是 FROM 子句的擴充功能。不同之處在於，取消樞紐的語法使用 UNPIVOT 關鍵字來指示它正在迭代物件而不是陣列。它使用 AS value_alias 迭代物件內的所有值，並使用 AT attribute_alias 迭代所有屬性。考慮下面的語法片段：


UNPIVOT expression AS value_alias [ AT attribute_alias ]

Amazon Redshift 支援在單一 FROM 子句中使用物件解除樞紐和陣列解除巢狀，如下所示：


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;

當您使用物件取消樞紐時，Amazon Redshift 不支援相關的取消樞紐。具體來說，假設您有多個在不同查詢層級中取消樞紐的範例，而內部未樞紐分析會參考外部層級的範例。Amazon Redshift 不支援這種類型的多重取消樞紐。

如需 FROM 子句的相關資訊，請參閱FROM 子句。如需示範如何使用 PIVOT 和 UNPIVOT 查詢結構化資料的範例，請參閱PIVOT 和 UNPIVOT 範例。

動態類型

動態類型不需要明確轉換從點和括號路徑中擷取的資料。Amazon Redshift 會使用動態類型來處理無結構描述的 SUPER 資料，無需在查詢中使用資料類型之前先宣告資料類型。動態類型會使用導覽至 SUPER 資料欄的結果，而不必明確地將它們轉換為 Amazon Redshift 類型。動態類型在聯結和 GROUP BY 子句中最有用。下列範例使用 SELECT 陳述式，該陳述式不需要將點和方括號運算式明確轉換為一般的 Amazon Redshift 類型。如需類型相容性和轉換的相關資訊，請參閱類型相容性與轉換。


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

當 c_orders[0].o_orderstatus 是字串 ‘P’ 時，此查詢中的等號計算結果為 true。在所有其他情況下，等號評估為 false，包括相等參數是不同類型的情況。

動態和靜態類型

如果不使用動態類型，則無法確定 c_orders[0].o_orderstatus 是字串、整數還是結構。您只能確定 c_orders[0].o_orderstatus 是 SUPER 資料類型，它可以是 Amazon Redshift 純量、陣列或結構。c_orders[0].o_orderstatus 的靜態類型是 SUPER 資料類型。通常，類型隱含地是 SQL 中的靜態類型。

Amazon Redshift 使用動態類型來處理無結構描述資料。當查詢評估資料時，c_orders[0].o_orderstatus 結果是特定類型。例如，對 customer_orders_lineitem 的第一則記錄評估 c_orders[0].o_orderstatus 可能會得到整數。對第二則記錄進行評估可能會得到字串。這些是運算式的動態類型。

將 SQL 運算子或函數搭配具有動態類型的點和括號運算式使用時，Amazon Redshift 會產生類似於將標準 SQL 運算子或函數搭配個別靜態類型使用的結果。在此範例中，當路徑運算式的動態類型是字串時，與字串 'P' 的比較是有意義的。只要 c_orders[0].o_orderstatus 的動態類型是字串以外的任何其他資料類型，則等式傳回 false。當使用類型錯誤的引數時，其他函數傳回 null。

下列範例使用靜態類型撰寫先前的查詢：


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;

請注意相等述詞和比較述詞之間的以下區別。在前面的範例中，如果您以述詞取代相等 less-than-or-equal述詞，語意會產生 null 而不是 false。


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

在此範例中，如果 c_orders[0].o_orderstatus 是字串，且按字母順序等於或小於 ‘P’，則 Amazon Redshift 傳回 true。如果它的字母順序大於 'P'，則 Amazon Redshift 傳回 false。但是，如果 c_orders[0].o_orderstatus 不是字串，Amazon Redshift 將傳回 null，因為 Amazon Redshift 無法比較不同類型的值，如以下查詢所示：


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;

動態類型並不排除具有最低可比性的類型比較。例如，您可以將 CHAR 和 VARCHAR Amazon Redshift 純量類型轉換為 SUPER。它們與字串相當，包括忽略類似 Amazon Redshift CHAR 和 VARCHAR 類型的結尾空白字元。同樣地，整數、小數和浮點值與 SUPER 值具有可比性。特別是對於小數欄，每個值也可以有不同的小數位數。Amazon Redshift 仍將它們視為動態類型。

Amazon Redshift 也支援評估為深度相等的物件和陣列的相等性，例如深入評估物件或陣列並比較所有屬性。請謹慎使用深度相等，因為執行深度相等的過程可能非常耗時。

使用動態類型進行聯結

對於聯結，動態類型會自動比對具有不同動態類型的值，而無需執行長時間的 CASE WHEN 分析以找出可能出現的資料類型。例如，假設您的組織隨著時間變更了其用於部分索引鍵的格式。

一開始發行的整數部分索引鍵更換為字串部分索引鍵，例如 'A55'，後來再次更換為陣列部分索引鍵，例如組合字串和數字的 ['X'，10]。Amazon Redshift 不需要對部分索引鍵執行冗長的案例分析，而且可以使用聯結，如下列範例所示。


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;

下列範例顯示如果不使用動態類型，相同查詢可能會有多複雜且效率低落：


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;

寬鬆的語義

預設情況下，導覽無效時 SUPER 值的導覽操作會傳回 null，而不是傳回錯誤。如果 SUPER 值不是物件，或者 SUPER 值是物件但不包含查詢中使用的屬性名稱，則物件導覽無效。例如，下列查詢會存取 SUPER 資料欄 cdata 中無效的屬性名稱：


SELECT c.c_orders.something FROM customer_orders_lineitem c;

如果 SUPER 值不是陣列或陣列索引超出邊界，則陣列導覽傳回 null。下列查詢會傳回 null，因為 c_orders[1][1] 超出範圍。


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

寬鬆的語義在使用動態類型轉換 SUPER 值時特別有用。如果轉換無效，將 SUPER 值轉換為錯誤類型會傳回 null，而不是錯誤。例如，以下查詢傳回 null，因為它無法將物件屬性 o_orderstatus 的字串值 'Good' 轉換為 INTEGER。Amazon Redshift 對於 VARCHAR 到 INTEGER 轉換傳回錯誤，但對於 SUPER 轉換則不會傳回錯誤。


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

自我檢查的種類

SUPER 資料欄支援傳回動態類型和 SUPER 值的其他類型資訊的檢查函數。最常見的範例是 JSON_TYPEOF 純量函數，此函數會根據 SUPER 值的動態類型，傳回含有布林值、數字、字串、物件、陣列或 null 的 VARCHAR。Amazon Redshift 支援 SUPER 資料欄的下列布林函數：

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

如果輸入值為 null，所有這些函數傳回 false。IS_SCALAR、IS_OBJECT 和 IS_ARRAY 是互斥的，涵蓋除 null 之外的所有可能值。

為了推論資料的對應類型，Amazon Redshift 使用 JSON_TYPEOF 函數傳回 SUPER 值 (頂層) 的類型，如下列範例所示：


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

Amazon Redshift 將其視為單一長字串，類似於將此值插入 VARCHAR 欄而不是 SUPER。由於該欄是 SUPER，因此單一字串仍然是有效的 SUPER 值，並且在 JSON_TYPEOF 中註明了差異：


SELECT IS_VARCHAR(r_nations[0].n_name) FROM region_nations;
 is_varchar
-------------
 true
(1 row)


SELECT r_nations[4].n_name FROM region_nations
WHERE CASE WHEN IS_INTEGER(r_nations[4].n_nationkey) 
           THEN r_nations[4].n_nationkey::INTEGER = 15 
           ELSE false END;

排序依據

Amazon Redshift 不會定義具有不同動態類型的值之間的 SUPER 比較。字串形式的 SUPER 值既不小於也不大於數字形式的 SUPER 值。為了將 ORDER BY 子句與 SUPER 欄結合使用，Amazon Redshift 定義了當 Amazon Redshift 使用 ORDER BY 子句對 SUPER 值進行排名時要觀察的不同類型之間的總排序。動態類型之間的順序是布林、數字、字串、陣列、物件。以下範例顯示了不同類型的順序：


INSERT INTO region_nations VALUES 
(100,'name1','comment1','AWS'), 
(200,'name2','comment2',1),
(300,'name3','comment3',ARRAY(1, 'abc', null)),
(400,'name4','comment4',-2.5),
(500,'name5','comment5','Amazon');

SELECT r_nations FROM region_nations order by r_nations;

r_nations
----------------
 -2.5
 1
 "Amazon"
 "AWS"
 [1,"abc",null]
(5 rows)

如需 ORDER BY 子句的相關資訊，請參閱ORDER BY 子句。

您的瀏覽器已停用或無法使用 Javascript。

您必須啟用 Javascript，才能使用 AWS 文件。請參閱您的瀏覽器說明頁以取得說明。

文件慣用形式

卸載 Parquet 格式的半結構化資料

運算子和函數