查詢半結構化資料

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

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

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

Navigation (導覽)

Amazon Redshift 使用 PartiQL，分別使用 [...] 括號和點符號啟用導覽到陣列和結構中。此外，您也可以使用點符號將導覽混合到結構中，以及使用括號符號將導覽混合到陣列中。例如，下列陳述式只會選取陣列中巢狀 SUPER 物件深一層的第三個元素：

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

您可以在執行資料操作時使用點和括號標記法，例如篩選、聯結和彙總。您可以在查詢中通常有資料欄參考的任何位置使用這些符號。例如，下列陳述式會選取類型為 的事件數量UPDATED。

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

如需使用 PartiQL 導覽的更深入範例，請參閱 在 Amazon Redshift 中使用半結構化資料的範例。

解除巢狀化查詢

若要取消巢狀查詢，Amazon Redshift 提供兩種方式來逐一查看 SUPER 陣列：在 FROM 子句中的 PartiQL 語法和 UNNEST 操作。這兩種解除巢狀方法都會產生相同的輸出。如需 UNNEST 操作的資訊，請參閱 FROM 子句。如需使用 UNNEST 操作的範例，請參閱 UNNEST 範例。

Amazon Redshift 可以使用查詢的 FROM 子句中的 PartiQL 語法來導覽 SUPER 陣列。使用上一個範例，下列範例會迭代 c_orders 的屬性值。

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

使用 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 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)

下列範例會迭代多個層級的陣列。這個範例會使用多個解除巢狀化子句來迭代到最內層的陣列。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;

 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)

如需 FROM 子句的相關資訊，請參閱FROM 子句。如需解除巢狀化 SUPER 查詢的更多範例，請參閱 在 Amazon Redshift 中使用半結構化資料的範例。

物件取消樞紐

若要執行物件取消樞紐，Amazon Redshift 會使用 PartiQL 語法來迭代 SUPER 物件。它使用查詢的 FROM 子句以及 UNPIVOT 關鍵字來執行此操作。在下列範例中，表達式是 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 子句。如需搭配 SUPER 類型使用樞紐的範例，請參閱 在 Amazon Redshift 中使用半結構化資料的範例。

動態類型

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

請考慮下列範例，其會尋找訂單狀態為 的資料列shipped：

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

當 c_orders【0】.o_orderstatus 的值是字串 ‘shipped’ 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;

請注意相等述詞和比較述詞之間的以下區別。在前面的範例中，如果您以小於或等於的述詞取代相等述詞，則語義會產生 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;

排序依據

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

如需在 SUPER 查詢中使用 GROUP BY 和 ORDER BY 的範例，請參閱 篩選半結構化資料。