UNNEST 範例 - Amazon Redshift
將沒有 OFFSET 欄的陣列解除巢狀化將具有隱含 OFFSET 欄的陣列解除巢狀化將具有明確 OFFSET 欄的陣列解除巢狀化

Amazon Redshift 自 2025 年 11 月 1 日起不再支援建立新的 Python UDF。如果您想要使用 Python UDF，請在該日期之前建立 UDF。現有 Python UDF 將繼續正常運作。如需詳細資訊，請參閱部落格文章。

UNNEST 範例

UNNEST 是 FROM 子句中的參數，可將巢狀資料展開，成為包含資料的非巢狀元素的欄。如需解除巢狀化資料的相關資訊，請參閱 查詢半結構化資料。

下列陳述式會建立並填入 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)