Categories:

Semi-structured and structured data functions (Higher-order)

# TRANSFORMÂ¶

Transforms an array based on the logic in a Lambda expression.

Filter and transform data with Snowflake higher-order functions

## SyntaxÂ¶

```TRANSFORM( <array> , <lambda_expression> )
```

## ArgumentsÂ¶

`array`

The array that contains the elements to be transformed. The array can be semi-structured or structured.

`lambda_expression`

A 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             |
| ]               |
+-----------------+
```

### 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},
] AS order_detail
UNION SELECT 2 AS order_id, '2024-01-02' AS order_date, [
{'item':'Laptop', 'quantity':5, 'subtotal':7500},
];

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                       |
|          |            |   }                                        |
|          |            | ]                                          |
+----------+------------+--------------------------------------------+
```

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-cancelling Headphones", |
|          |            |     "quantity": 5,                         |
|          |            |     "subtotal": 1000,                      |
|          |            |     "unit_price": 200                      |
|          |            |   }                                        |
|          |            | ]                                          |
+----------+------------+--------------------------------------------+
```

Use the TRANSFORM function to delete the `quantity` element in each array in 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-cancelling Headphones", |
|          |            |     "subtotal": 1000                       |
|          |            |   }                                        |
|          |            | ]                                          |
+----------+------------+--------------------------------------------+
```
Language: English