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

查詢半結構化資料

透過 Amazon Redshift，您可以查詢和分析半結構化資料，例如 JSON、Avro 或 Ion，以及結構化資料。半結構化資料是指具有彈性結構描述的資料，允許階層式或巢狀結構。下列各節示範使用 Amazon Redshift 對開放資料格式的支援查詢半結構化資料，讓您從複雜的資料結構中解除鎖定有價值的資訊。

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

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

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

Amazon Redshift 使用 PartiQL，分別使用 [...] 括號和點符號啟用導覽到陣列和結構中。此外，您也可以使用點符號將導覽混合到結構中，以及使用括號符號將導覽混合到陣列中。例如，下列範例假設c_ordersSUPER資料欄是具有結構的陣列，且屬性名為 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子句項目取消巢狀的 PartiQL 語法x (AS) y表示 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 也支援評估為深度相等的物件和陣列的相等性，例如深入評估物件或陣列並比較所有屬性。請謹慎使用深度相等，因為執行深度相等的過程可能非常耗時。

使用動態類型進行聯結

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

一開始發行的整數部分索引鍵更換為字串部分索引鍵，例如 '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值時，Lax 語意特別有用。如果轉換無效，則將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值的動態類型，傳回VARCHAR具有布林值、數字、字串、物件、陣列或 null 值的。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_ 和 IS_ARRAY 是互斥的OBJECT，涵蓋除了 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的值。若要搭配SUPER資料欄使用 ORDER BY 子句，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 文件。請參閱您的瀏覽器說明頁以取得說明。

文件慣用形式

卸載半結構化資料

運算子和函數