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/)。 本文為英文版的機器翻譯版本，如內容有任何歧義或不一致之處，概以英文版為準。 # UNNEST 範例 UNNEST 是 FROM 子句中的參數，可將巢狀資料展開，成為包含資料的非巢狀元素的欄。如需解除巢狀化資料的相關資訊，請參閱 [查詢半結構化資料](query-super.md)。 下列陳述式會建立並填入 `orders` 資料表，其中包含的 `products` 欄內含產品 ID 的陣列。本節中的範例使用此資料表中的範例資料。 ``` CREATE TABLE orders ( order_id INT, products SUPER ); -- Populate table INSERT INTO orders VALUES (1001, JSON_PARSE('[ { "product_id": "P456", "name": "Monitor", "price": 299.99, "quantity": 1, "specs": { "size": "27 inch", "resolution": "4K" } } ] ')), (1002, JSON_PARSE(' [ { "product_id": "P567", "name": "USB Cable", "price": 9.99, "quantity": 3 }, { "product_id": "P678", "name": "Headphones", "price": 159.99, "quantity": 1, "specs": { "type": "Wireless", "battery_life": "20 hours" } } ] ')); ``` 以下範例說明如何使用 PartiQL 語法將包含範例資料的查詢解除巢狀化。 ## 將沒有 OFFSET 欄的陣列解除巢狀化 下列查詢會將產品欄中的 SUPER 陣列解除巢狀化，其中每一列代表 `order_id` 中訂單的某一個項目。 ``` SELECT o.order_id, unnested_products.product FROM orders o, UNNEST(o.products) AS unnested_products(product); order_id | product ----------+----------------------------------------------------------------------------------------------------------------------------- 1001 | {"product_id":"P456","name":"Monitor","price":299.99,"quantity":1,"specs":{"size":"27 inch","resolution":"4K"}} 1002 | {"product_id":"P567","name":"USB Cable","price":9.99,"quantity":3} 1002 | {"product_id":"P678","name":"Headphones","price":159.99,"quantity":1,"specs":{"type":"Wireless","battery_life":"20 hours"}} (3 rows) ``` 下列查詢會尋找每個訂單中最昂貴的產品。 ``` SELECT o.order_id, MAX(unnested_products.product) FROM orders o, UNNEST(o.products) AS unnested_products(product); order_id | product ----------+----------------------------------------------------------------------------------------------------------------------------- 1001 | {"product_id":"P456","name":"Monitor","price":299.99,"quantity":1,"specs":{"size":"27 inch","resolution":"4K"}} 1002 | {"product_id":"P678","name":"Headphones","price":159.99,"quantity":1,"specs":{"type":"Wireless","battery_life":"20 hours"}} (2 rows) ``` ## 將具有隱含 OFFSET 欄的陣列解除巢狀化 下列查詢使用 `UNNEST ... WITH OFFSET` 參數來顯示其訂單陣列內每個產品從零開始的位置。 ``` SELECT o.order_id, up.product, up.offset_col FROM orders o, UNNEST(o.products) WITH OFFSET AS up(product); order_id | product | offset_col ----------+-----------------------------------------------------------------------------------------------------------------------------+------------ 1001 | {"product_id":"P456","name":"Monitor","price":299.99,"quantity":1,"specs":{"size":"27 inch","resolution":"4K"}} | 0 1002 | {"product_id":"P567","name":"USB Cable","price":9.99,"quantity":3} | 0 1002 | {"product_id":"P678","name":"Headphones","price":159.99,"quantity":1,"specs":{"type":"Wireless","battery_life":"20 hours"}} | 1 (3 rows) ``` 由於陳述式未指定位移欄的別名，因此 Amazon Redshift 預設會將其命名為 `offset_col`。 ## 將具有明確 OFFSET 欄的陣列解除巢狀化 下列查詢同樣使用 `UNNEST ... WITH OFFSET` 參數來顯示其訂單陣列內的產品。相較於先前範例中的查詢，此查詢的差異在於其使用別名 `idx` 明確命名位移欄。 ``` SELECT o.order_id, up.product, up.idx FROM orders o, UNNEST(o.products) WITH OFFSET AS up(product, idx); order_id | product | idx ----------+-----------------------------------------------------------------------------------------------------------------------------+----- 1001 | {"product_id":"P456","name":"Monitor","price":299.99,"quantity":1,"specs":{"size":"27 inch","resolution":"4K"}} | 0 1002 | {"product_id":"P567","name":"USB Cable","price":9.99,"quantity":3} | 0 1002 | {"product_id":"P678","name":"Headphones","price":159.99,"quantity":1,"specs":{"type":"Wireless","battery_life":"20 hours"}} | 1 (3 rows) ```