自 2025 年 11 月 1 日起，Amazon Redshift 將不再支援建立新的 Python UDFs。如果您想要使用 Python UDFs，請在該日期之前建立 UDFs。現有的 Python UDFs將繼續如常運作。如需詳細資訊，請參閱部落格文章
本文為英文版的機器翻譯版本，如內容有任何歧義或不一致之處，概以英文版為準。
UNNEST 範例
UNNEST 是 FROM 子句中的參數，可將巢狀資料展開為包含資料未巢狀元素的資料欄。如需解除巢狀化資料的資訊，請參閱 查詢半結構化資料。
下列陳述式會建立並填入orders資料表，其中包含包含產品 IDs 陣列的資料products欄。本節中的範例使用此表格中的範例資料。
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)
