UNNEST 示例 - Amazon Redshift
取消嵌套一个没有 OFFSET 列的数组取消嵌套带有隐式 OFFSET 列的数组取消嵌套带有显式 OFFSET 列的数组

从 2025 年 11 月 1 日起，Amazon Redshift 将不再支持创建新的 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 列的数组

以下查询取消嵌套 products 列中的 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)