Categories:

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

TRANSFORM

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

See also:

Filter and transform data with Snowflake higher-order functions

Syntax

TRANSFORM( <array> , <lambda_expression> )
Copy

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>
Copy

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";
Copy
+-----------------+
| 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";
Copy
+-----------------------+
| 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-cancelling Headphones', 'quantity':5, 'subtotal':1000}
  ];

SELECT * FROM orders;
Copy
+----------+------------+--------------------------------------------+
| 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;
Copy
+----------+------------+--------------------------------------------+
| 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;
Copy
+----------+------------+--------------------------------------------+
| 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                       |
|          |            |   }                                        |
|          |            | ]                                          |
+----------+------------+--------------------------------------------+