Categorias:

Funções de dados semiestruturados e estruturados (Ordem superior)

TRANSFORM

Transforma uma matriz com base na lógica de uma expressão lambda.

Consulte também:

Usar funções lambda em dados com funções de ordem superior do Snowflake

Sintaxe

TRANSFORM( <array> , <lambda_expression> )

Argumentos

array

A matriz que contém os elementos a serem transformados. A matriz pode ser semiestruturada ou estruturada.

lambda_expression

Uma expressão lambda que define a lógica de transformação em cada elemento da matriz.

A expressão lambda deve ter apenas um argumento especificado na seguinte sintaxe:

<arg> [ <datatype> ] -> <expr>

Retornos

O tipo de retorno desta função é uma matriz semiestruturada ou estruturada do resultado da expressão lambda.

Se qualquer dos argumentos for NULL, a função retorna NULL sem relatar um erro.

Notas de uso

  • Quando o tipo de dados para o argumento lambda é especificado explicitamente, o elemento da matriz é forçado para o tipo especificado antes da invocação da lambda. Para obter informações sobre coerção, consulte Conversão do tipo de dados.

  • Quando não há nenhum tipo de dados especificado para o argumento lambda, seu tipo de dados é derivado da matriz de entrada da seguinte forma:

    • Se a matriz de entrada for semiestruturada, o tipo de dados do argumento lambda será VARIANT.

    • Se a matriz de entrada for estruturada, o tipo de dados do argumento lambda será o tipo de dados do elemento da matriz.

  • Para entrada de matriz semiestruturada, uma matriz semiestruturada é retornada. Para entrada de matriz estruturada, uma matriz estruturada do tipo de resultado da expressão lambda é retornada.

Exemplos

Os exemplos a seguir usam a função TRANSFORM.

Multiplicação de cada elemento em uma matriz por um valor

Use a função TRANSFORM para multiplicar cada elemento de uma matriz por dois:

SELECT TRANSFORM([1, 2, 3], a INT -> a * 2) AS "Multiply by Two";

+-----------------+
| Multiply by Two |
|-----------------|
| [               |
|   2,            |
|   4,            |
|   6             |
| ]               |
+-----------------+

Este exemplo é o mesmo que o exemplo anterior, mas especifica uma matriz estruturada do tipo INT:

SELECT TRANSFORM([1, 2, 3]::ARRAY(INT), a INT -> a * 2) AS "Multiply by Two (Structured)";

+------------------------------+
| Multiply by Two (Structured) |
|------------------------------|
| [                            |
|   2,                         |
|   4,                         |
|   6                          |
| ]                            |
+------------------------------+

Retorno de valores em uma matriz com texto adicionado

Use a função TRANSFORM para retornar o valor de cada objeto em uma matriz e adicionar texto a cada um:

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"  |
| ]                     |
+-----------------------+

Transformação de elementos de matriz em dados de tabela

Suponha que você tenha uma tabela chamada orders com as colunas order_id, order_date e order_detail. A coluna order_detail é uma matriz dos itens de linha, sua quantidade de compra e subtotal. A tabela contém duas linhas de dados. A seguinte instrução SQL cria esta tabela e insere as linhas:

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 a função TRANSFORM para adicionar um elemento unit_price para cada matriz na tabela orders:

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 a função TRANSFORM junto com a função OBJECT_DELETE na lógica da expressão lambda para excluir o elemento quantity em cada matriz da tabela orders:

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

Faça referência a uma coluna de tabela em uma expressão lambda para transformar elementos de matriz em dados de tabela

Criar uma tabela com uma coluna do tipo ARRAY e outra coluna do tipo 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 a função TRANSFORM para adicionar o valor em col2 ao valor de cada elemento da matriz em cada linha:

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