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" |
| ] |
+-----------------------+
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)
)
) AS 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'
)
) AS 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 |
| | | } |
| | | ] |
+----------+------------+-------------------------------------------+
Create a table with one column of type ARRAY and another column of type INT:
CREATE OR REPLACE TABLE transform_column_ref_demo AS
SELECT [ 1, 2, 3 ] AS col1, 10 AS col2
UNION
SELECT [ 4, 5, 6 ] AS col1, -1 AS col2
UNION
SELECT [ 7, 8, 9 ] AS col1, NULL AS col2;
SELECT * FROM transform_column_ref_demo;
+------+------+
| COL1 | COL2 |
|------+------|
| [ | 10 |
| 1, | |
| 2, | |
| 3 | |
| ] | |
| [ | -1 |
| 4, | |
| 5, | |
| 6 | |
| ] | |
| [ | NULL |
| 7, | |
| 8, | |
| 9 | |
| ] | |
+------+------+
Use the TRANSFORM function to add the value in col2 to the value of each array element in each row:
SELECT TRANSFORM(col1, v INT -> v + col2) AS transform_col_ref
FROM transform_column_ref_demo;
+-------------------+
| TRANSFORM_COL_REF |
|-------------------|
| [ |
| 11, |
| 12, |
| 13 |
| ] |
| [ |
| 3, |
| 4, |
| 5 |
| ] |
| [ |
| undefined, |
| undefined, |
| undefined |
| ] |
+-------------------+