- Categories:
Semi-structured and structured data functions (Higher-order)
TRANSFORM¶
Transforms an array based on the logic in a lambda expression.
Syntax¶
TRANSFORM( <array> , <lambda_expression> )
Arguments¶
arrayThe array that contains the elements to be transformed. The array can be semi-structured or structured.
lambda_expressionA lambda expression that defines the transformation logic on each array element.
The lambda expression must have only one argument specified in the following syntax:
<arg> [ <datatype> ] -> <expr>
Returns¶
The return type of this function is a semi-structured or structured array of the lambda expression result.
If either argument is NULL, the function returns NULL without reporting an error.
Usage notes¶
When the data type for the lambda argument is explicitly specified, the array element is coerced into the specified type before lambda invocation. For information about coercion, see Data type conversion.
When there is no data type specified for the lambda argument, its data type is derived from the input array as follows:
If the input array is semi-structured, the data type of the lambda argument is VARIANT.
If the input array is structured, the data type of the lambda argument is the data type of the array element.
For semi-structured array input, a semi-structured array is returned. For structured array input, a structured array of the lambda expression result type is returned.
Examples¶
The following examples use the TRANSFORM function.
Multiply each element in an array by a value¶
Use the TRANSFORM function to multiply each element in an array by two:
SELECT TRANSFORM([1, 2, 3], a INT -> a * 2) AS "Multiply by Two";
+-----------------+
| Multiply by Two |
|-----------------|
| [ |
| 2, |
| 4, |
| 6 |
| ] |
+-----------------+
This example is the same as the previous example, but it specifies a structured array of type INT:
SELECT TRANSFORM([1, 2, 3]::ARRAY(INT), a INT -> a * 2) AS "Multiply by Two (Structured)";
+------------------------------+
| Multiply by Two (Structured) |
|------------------------------|
| [ |
| 2, |
| 4, |
| 6 |
| ] |
+------------------------------+
Return values in an array with added text¶
Use the TRANSFORM function to return the value of each object in an array, and add text to each one:
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" |
| ] |
+-----------------------+
Transform array elements in table data¶
Assume you have a table named orders with the columns order_id, order_date, and order_detail. The
order_detail column is an array of the line items, their purchase quantity, and subtotal. The table contains
two rows of data. The following SQL statement creates this table and inserts the rows:
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-canceling Headphones', 'quantity':5, 'subtotal':1000}
] AS order_detail;
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-canceling Headphones", |
| | | "quantity": 5, |
| | | "subtotal": 1000 |
| | | } |
| | | ] |
+----------+------------+-------------------------------------------+
Use the TRANSFORM function to add a unit_price element to each array in the orders table:
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-canceling Headphones", |
| | | "quantity": 5, |
| | | "subtotal": 1000, |
| | | "unit_price": 200 |
| | | } |
| | | ] |
+----------+------------+-------------------------------------------+
Use the TRANSFORM function along with the OBJECT_DELETE function in the logic of the
lambda expression to delete the quantity element in each array from the orders table:
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-canceling Headphones", |
| | | "subtotal": 1000 |
| | | } |
| | | ] |
+----------+------------+-------------------------------------------+