从补丁 198 开始，Amazon Redshift 将不再支持创建新的 Python UDF。现有的 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 列的数组
以下查询取消嵌套 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)
```