Amazon Redshift 將不再支援從修補程式 198 開始建立新的 Python UDFs。現有 Python UDF 將繼續正常運作至 2026 年 6 月 30 日。如需詳細資訊，請參閱[部落格文章](https://aws.amazon.com/blogs/big-data/amazon-redshift-python-user-defined-functions-will-reach-end-of-support-after-june-30-2026/)。 本文為英文版的機器翻譯版本，如內容有任何歧義或不一致之處，概以英文版為準。 # 查詢半結構化資料 您可以使用 Amazon Redshift 查詢和分析半結構化資料，例如 JSON、Avro 或 Ion，以及您的結構化資料。半結構化資料是指具有彈性結構描述的資料，允許階層式或巢狀結構。下列各節示範使用 Amazon Redshift 對開放資料格式的支援來查詢半結構化資料，讓您從複雜的資料結構中獲得寶貴的資訊。 Amazon Redshift 使用 PartiQL 語言來提供對關聯式、半結構化和巢狀資料的 SQL 相容存取。 PartiQL 使用動態類型進行運作。這種方法可對結構化、半結構化和巢狀資料集的組合進行直覺式篩選、聯結和彙總。存取巢狀資料時，PartiQL 語法會使用點符號和陣列下標來進行路徑導覽。它也可讓 FROM 子句項目迭代陣列，並用於解除巢狀化操作。接下來，您可以找到不同查詢模式的描述，這些模式結合了使用 SUPER 資料類型與路徑和陣列導覽、解除巢狀化、取消樞紐和聯結。如需 PartiQL 的詳細資訊，請參閱 [PartiQL - 適用於 Amazon Redshift 的 SQL 相容查詢語言](super-partiql.md)。 ## 導航 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 中使用半結構化資料的範例](super-examples.md)。 ## 解除巢狀化查詢 若要解除巢狀查詢，Amazon Redshift 提供兩種方式來對 SUPER 陣列進行迭代：PartiQL 語法和 FROM 子句中的 UNNEST 操作。這兩種解除巢狀方法會產生相同的輸出。如需 UNNEST 操作的相關資訊，請參閱 [FROM 子句](r_FROM_clause30.md)。如需使用 UNNEST 操作的範例，請參閱 [UNNEST 範例](r_FROM_clause-unnest-examples.md)。 Amazon Redshift 可在查詢的 FROM 子句中，使用 PartiQL 語法來瀏覽 SUPER 陣列。使用上一個範例，下列範例會迭代 `c_orders` 的屬性值。 ``` SELECT orders.*, o FROM customer_orders orders, orders.c_orders o; ``` 使用 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 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 子句](r_FROM_clause30.md)。如需解除巢狀化 SUPER 查詢的更多範例，請參閱 [在 Amazon Redshift 中使用半結構化資料的範例](super-examples.md)。 ## 物件取消樞紐 若要執行物件取消樞紐，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 子句](r_FROM_clause30.md)。如需搭配 SUPER 類型使用樞紐的範例，請參閱 [在 Amazon Redshift 中使用半結構化資料的範例](super-examples.md)。 ## 動態類型 動態類型不需要明確轉換從點和括號路徑中擷取的資料。Amazon Redshift 會使用動態類型來處理無結構描述的 SUPER 資料，無需在查詢中使用資料類型之前先宣告資料類型。動態類型會使用導覽至 SUPER 資料欄的結果，而不必明確地將它們轉換為 Amazon Redshift 類型。動態類型在聯結和 GROUP BY 子句中最有用。下列範例使用 SELECT 陳述式，該陳述式不需要將點和方括號運算式明確轉換為一般的 Amazon Redshift 類型。如需類型相容性和轉換的相關資訊，請參閱[類型相容性與轉換](c_Supported_data_types.md#r_Type_conversion)。 請參考下列範例，此範例會尋找訂單狀態為 `shipped` 的列： ``` SELECT c_orders[0].o_orderkey FROM customer_orders_lineitem WHERE c_orders[0].o_orderstatus = 'shipped'; ``` 當 c\$1orders[0].o\$1orderstatus 是字串‘shipped’時，此範例查詢中的等號計算結果為 `true`。在所有其他情況下，等號評估為 `false`，包括相等參數是不同類型的情況。例如，如果訂單狀態為整數，則不會選取其所屬列。 ### 動態和靜態類型 如果不使用動態類型，則無法確定 c\$1orders[0].o\$1orderstatus 是字串、整數還是結構。您只能確定 c\$1orders[0].o\$1orderstatus 是 SUPER 資料類型，它可以是 Amazon Redshift 純量、陣列或結構。c\$1orders[0].o\$1orderstatus 的靜態類型是 SUPER 資料類型。通常，類型隱含地是 SQL 中的靜態類型。 Amazon Redshift 使用動態類型來處理無結構描述資料。當查詢評估資料時，c\$1orders[0].o\$1orderstatus 結果是特定類型。例如，對 customer\$1orders\$1lineitem 的第一則記錄評估 c\$1orders[0].o\$1orderstatus 可能會得到整數。對第二則記錄進行評估可能會得到字串。這些是運算式的動態類型。 將 SQL 運算子或函數搭配具有動態類型的點和括號運算式使用時，Amazon Redshift 會產生類似於將標準 SQL 運算子或函數搭配個別靜態類型使用的結果。在此範例中，當路徑運算式的動態類型是字串時，與字串 'P' 的比較是有意義的。只要 c\$1orders[0].o\$1orderstatus 的動態類型是字串以外的任何其他資料類型，則等式傳回 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\$1orders[0].o\$1orderstatus 是字串，且按字母順序等於或小於 ‘P’，則 Amazon Redshift 傳回 true。如果它的字母順序大於 'P'，則 Amazon Redshift 傳回 false。但是，如果 c\$1orders[0].o\$1orderstatus 不是字串，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; ``` ## 不區分大小寫的查詢 您可以使用 COLLATE 函數或在資料欄或資料庫層級設定定序，對 SUPER 資料執行不區分大小寫的字串比較。如需在建立資料表時設定定序的詳細資訊，請參閱 [CREATE TABLE](r_CREATE_TABLE_NEW.md)。如需 SUPER 資料運算子和函數定序行為的相關資訊，請參閱 [定序行為](operators-functions.md#collation-behavior)。 下列範例會在從 SUPER 資料擷取的字串值上使用 COLLATE 函數。 ``` CREATE TABLE events (data SUPER); INSERT INTO events VALUES (JSON_PARSE('{"status": "Active", "name": "Event1"}')); INSERT INTO events VALUES (JSON_PARSE('{"status": "ACTIVE", "name": "Event2"}')); INSERT INTO events VALUES (JSON_PARSE('{"status": "active", "name": "Event3"}')); SELECT data.name FROM events WHERE COLLATE(data.status::VARCHAR, 'case_insensitive') = 'active'; name ---------- "Event1" "Event2" "Event3" (3 rows) ``` 您也可以在建立資料表時，使用不區分大小寫的定序來定義 SUPER 資料欄。在此情況下，資料欄上的所有字串比較不區分大小寫。 ``` CREATE TABLE events_ci (data SUPER COLLATE CASE_INSENSITIVE); INSERT INTO events_ci VALUES (JSON_PARSE('{"status": "Active"}')); INSERT INTO events_ci VALUES (JSON_PARSE('{"status": "ACTIVE"}')); SELECT * FROM events_ci WHERE data.status::VARCHAR = 'active'; data ----------------------- {"status":"Active"} {"status":"ACTIVE"} (2 rows) ``` ## 寬鬆的語義 預設情況下，導覽無效時 SUPER 值的導覽操作會傳回 null，而不是傳回錯誤。如果 SUPER 值不是物件，或者 SUPER 值是物件但不包含查詢中使用的屬性名稱，則物件導覽無效。例如，下列查詢會存取 SUPER 資料欄 cdata 中無效的屬性名稱： ``` SELECT c.c_orders.something FROM customer_orders_lineitem c; ``` 如果 SUPER 值不是陣列或陣列索引超出邊界，則陣列導覽傳回 null。下列查詢會傳回 null，因為 c\$1orders[1][1] 超出範圍。 ``` SELECT c.c_orders[1][1] FROM customer_orders_lineitem c; ``` 寬鬆的語義在使用動態類型轉換 SUPER 值時特別有用。如果轉換無效，將 SUPER 值轉換為錯誤類型會傳回 null，而不是錯誤。例如，以下查詢傳回 null，因為它無法將物件屬性 o\$1orderstatus 的字串值 '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 的範例，請參閱 [篩選半結構化資料](super-examples.md#super-examples-filter)。