- 类别：
半结构化和结构化数据函数 （高阶）
TRANSFORM¶
基于 Lambda 表达式中的逻辑转换 数组。
语法¶
TRANSFORM( <array> , <lambda_expression> )
实参¶
array包含要转换的元素的数组。这可以是半结构化数组，也可以是结构化数组。
lambda_expression一个 Lambda 表达式，定义每个数组元素的转换逻辑。
Lambda 表达式必须仅指定一个实参，语法如下：
<arg> [ <datatype> ] -> <expr>
返回¶
此函数的返回类型是 Lambda 表达式结果的半结构化数组或结构化数组。
如果任一实参为 NULL，则此函数返回 NULL 且不报告错误。
使用说明¶
示例¶
以下示例使用 TRANSFORM 函数。
将数组中的每个元素乘以一个值¶
使用 TRANSFORM 函数，将数组中的每个元素乘以 2：
SELECT TRANSFORM([1, 2, 3], a INT -> a * 2) AS "Multiply by Two";
+-----------------+
| Multiply by Two |
|-----------------|
| [ |
| 2, |
| 4, |
| 6 |
| ] |
+-----------------+
在包含额外文本的数组中返回值¶
使用 TRANSFORM 函数返回数组中每个对象的值，并为每个对象添加文本：
SELECT TRANSFORM(
[
{'name':'Pat', 'value': 50},
{'name':'Terry', 'value': 75},
{'name':'Dana', 'value': 25}
],
c -> c:value || ' is the number') AS "Return Values";
+-----------------------+
| Return Values |
|-----------------------|
| [ |
| "50 is the number", |
| "75 is the number", |
| "25 is the number" |
| ] |
+-----------------------+
转换表数据中的数组元素¶
假设您有一个名为 orders 的表，包含 order_id、order_date 和 order_detail 列。order_detail 列是包含行项目、其购买数量和小计的数组。该表包含两行数据。以下 SQL 语句会创建此表并插入行：
CREATE OR REPLACE TABLE orders AS
SELECT 1 AS order_id, '2024-01-01' AS order_date, [
{'item':'UHD Monitor', 'quantity':3, 'subtotal':1500},
{'item':'Business Printer', 'quantity':1, 'subtotal':1200}
] AS order_detail
UNION SELECT 2 AS order_id, '2024-01-02' AS order_date, [
{'item':'Laptop', 'quantity':5, 'subtotal':7500},
{'item':'Noise-cancelling Headphones', 'quantity':5, 'subtotal':1000}
];
SELECT * FROM orders;
+----------+------------+--------------------------------------------+
| ORDER_ID | ORDER_DATE | ORDER_DETAIL |
|----------+------------+--------------------------------------------|
| 1 | 2024-01-01 | [ |
| | | { |
| | | "item": "UHD Monitor", |
| | | "quantity": 3, |
| | | "subtotal": 1500 |
| | | }, |
| | | { |
| | | "item": "Business Printer", |
| | | "quantity": 1, |
| | | "subtotal": 1200 |
| | | } |
| | | ] |
| 2 | 2024-01-02 | [ |
| | | { |
| | | "item": "Laptop", |
| | | "quantity": 5, |
| | | "subtotal": 7500 |
| | | }, |
| | | { |
| | | "item": "Noise-cancelling Headphones", |
| | | "quantity": 5, |
| | | "subtotal": 1000 |
| | | } |
| | | ] |
+----------+------------+--------------------------------------------+
使用 TRANSFORM 函数，将一个 unit_price 元素添加到 orders 表中的每个数组：
SELECT order_id,
order_date,
TRANSFORM(o.order_detail, i -> OBJECT_INSERT(
i,
'unit_price',
(i:subtotal / i:quantity)::NUMERIC(10,2))) ORDER_DETAIL_WITH_UNIT_PRICE
FROM orders o;
+----------+------------+--------------------------------------------+
| ORDER_ID | ORDER_DATE | ORDER_DETAIL_WITH_UNIT_PRICE |
|----------+------------+--------------------------------------------|
| 1 | 2024-01-01 | [ |
| | | { |
| | | "item": "UHD Monitor", |
| | | "quantity": 3, |
| | | "subtotal": 1500, |
| | | "unit_price": 500 |
| | | }, |
| | | { |
| | | "item": "Business Printer", |
| | | "quantity": 1, |
| | | "subtotal": 1200, |
| | | "unit_price": 1200 |
| | | } |
| | | ] |
| 2 | 2024-01-02 | [ |
| | | { |
| | | "item": "Laptop", |
| | | "quantity": 5, |
| | | "subtotal": 7500, |
| | | "unit_price": 1500 |
| | | }, |
| | | { |
| | | "item": "Noise-cancelling Headphones", |
| | | "quantity": 5, |
| | | "subtotal": 1000, |
| | | "unit_price": 200 |
| | | } |
| | | ] |
+----------+------------+--------------------------------------------+
使用 TRANSFORM 函数删除 orders 表中各数组的 quantity 元素。
SELECT order_id,
order_date,
TRANSFORM(o.order_detail, i -> OBJECT_DELETE(
i,
'quantity')) ORDER_DETAIL_WITHOUT_QUANTITY
FROM orders o;
+----------+------------+--------------------------------------------+
| ORDER_ID | ORDER_DATE | ORDER_DETAIL_WITHOUT_QUANTITY |
|----------+------------+--------------------------------------------|
| 1 | 2024-01-01 | [ |
| | | { |
| | | "item": "UHD Monitor", |
| | | "subtotal": 1500 |
| | | }, |
| | | { |
| | | "item": "Business Printer", |
| | | "subtotal": 1200 |
| | | } |
| | | ] |
| 2 | 2024-01-02 | [ |
| | | { |
| | | "item": "Laptop", |
| | | "subtotal": 7500 |
| | | }, |
| | | { |
| | | "item": "Noise-cancelling Headphones", |
| | | "subtotal": 1000 |
| | | } |
| | | ] |
+----------+------------+--------------------------------------------+