查詢半結構化資料

本文為英文版的機器翻譯版本，如內容有任何歧義或不一致之處，概以英文版為準。

本文為英文版的機器翻譯版本，如內容有任何歧義或不一致之處，概以英文版為準。

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

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

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

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

Navigation (導覽)

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;

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

如果輸入值為 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 子句。