---
title: Special Data Pipeline Transformation Functions
slug: b0nW-s
description: The Data Pipeline functionality allows data transformation during loading using SQL functions like `EXPLODE_OUTER`, which expands arrays into individual rows. 
docTags: 
createdAt: Wed Nov 27 2024 19:40:04 GMT+0000 (Coordinated Universal Time)
updatedAt: Mon Mar 30 2026 18:52:44 GMT+0000 (Coordinated Universal Time)
---

When you load data in the Ocient® System, you can use special loading transformation functions in the `SELECT` SQL statement to transform the data on load.

# EXPLODE\_OUTER

`EXPLODE_OUTER` expands a one-dimensional or multidimensional array into its elements with one element per row of output from the system. The array can contain a maximum of 8,192 elements, which is the limit for the default settings.

The function takes an array of rank `N` and expands the elements, returning elements of rank `N-1`. For example, when you apply `EXPLODE_OUTER` to an array of integers, the system returns each integer in its own row. When you apply `EXPLODE_OUTER` to an array of arrays of integers, the system returns each array of integers in its own row. You can add further transforms to these elements by nesting the statement, e.g., `NANOS_TO_TIMESTAMP(EXPLODE_OUTER($int_array_field[]))`.

You can use this function with multiple columns in the same table. The system holds constant columns that are not expanded. Also, the system combines expanded columns together in array order. If multiple expanded columns have different array lengths, the system expands the record into `N` records where `N` is the maximum length. Columns with array lengths less than `N` have NULL values after the system expands each value.

**Limitations**

- When you use this function with multi-dimensional arrays, you cannot apply it multiple times to match the number of dimensions, for example.
- Each record must have an associated 64-bit record number, which uniquely identifies a row within its partition. Configure the format of this number using the `RECORD_NUMBER_FORMAT` advanced pipeline option to modify the maximum number of elements that the array to expand can contain. For details, see [Advanced Pipeline Tuning Options](<./././SQL Reference/Data Definition Language _DDL_ Statement Reference/Data Pipelines.md>).&#x20;
  - The system uses record numbers for deduplication, so changing the `RECORD_NUMBER_FORMAT` option of a pipeline can change its deduplication behavior. Always create a new pipeline when you change this option to make rows deduplicate properly.
  - To make rows deduplicate properly when you do not set this option, always create a new pipeline when adding an `EXPLODE_OUTER` statement to your pipeline or removing all such statements from your pipeline.

**Syntax**

```sql
EXPLODE_OUTER(array)
```

| **Argument** | **Data Type** | **Description**      |
| ------------ | ------------- | -------------------- |
| `array`      | Array         | An array to expand.  |

**Example**

Create the `test_table` table to load data with three columns where the first two columns are integers, and the third is a string.

```sql
CREATE TABLE test_table ( 
    "i" INT NULL, 
    "j" INT NULL,
    "v" VARCHAR(536870912) COMPRESSION DYNAMIC NULL );
```

Preview the load using this inline source data in JSON format: `{"id": 1, "int_array": [1, 2, 3], "string_array": ["a", "b"]}`. Use the `EXPLODE_OUTER` function to expand the elements of the arrays in the second and third JSON data values.

```sql
PREVIEW PIPELINE p1 
SOURCE INLINE
    '{"id": 1, "int_array": [1, 2, 3], "string_array": ["a", "b"]}' 
EXTRACT FORMAT json 
INTO test_table 
SELECT $"id" as i, 
    EXPLODE_OUTER($"int_array"[]) as j, 
    EXPLODE_OUTER($"string_array"[]) as v; 
```

*Output*

```none
i j v 
------------------------------------------------------------------- 
1 1 a 
1 2 b 
1 3 NULL 
Fetched 3 rows
```

The output shows that the inline array values are expanded across the individual rows.

# Special Functions for Array Transformations in Pipelines

The data pipeline functionality supports special transformation functions for arrays that use lambda functions or function expressions to apply SQL operations to individual elements in an array. These functions are not supported in standard Ocient SQL queries.

The supported special functions are:

- `TRANSFORM` — Transform individual elements in an array. Apply a transformation expression to every element in the array.
- `FILTER` — Filter the elements in an array. Apply a filter function to each element in an array and return an array of elements matching the filter function.
- `ZIP_WITH` — Combines multiple arrays using a zipping function. Apply a zipping function to `N`-wise pairs of elements from `N` arrays and return an array with the results from the zipping function.&#x20;

Each of these functions takes one or more arrays as input arguments and a lambda or other reference function for applying to the elements in the array. A lambda function is an anonymous function that must have a specific signature and return type for the array transformation function where you use the lambda function.&#x20;

## TRANSFORM

Transforms an array based on the logic in a lambda expression. The first argument is an expression that evaluates to an array that contains the elements to transform. The second argument is a lambda expression or other reference function that defines how to transform each element.

The second argument must be of the form `(x T) -> U`. This specified form returns a value of type `U` for a specified element `x`. The lambda function specifies the type `T` of the element `x`.

The second argument can also be another reference function for a single argument function.

`TRANSFORM` returns an array of elements with the same length as the array argument. The returned array has the type returned by the lambda function. For multi-dimensional arrays, the `TRANSFORM` function applies to the outermost dimension. You can nest `TRANSFORM` operations to apply them over inner dimensions.

**Syntax**

```sql
TRANSFORM(array, function)
```

| **Argument** | **Data** **Type**                                                         | **Description**                                                                                                                                                                                                                                            |
| ------------ | ------------------------------------------------------------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `array`      | `T[]`                                                                     | The array of type `T` elements can be single or multi-dimensional. The `TRANSFORM` function only iterates over the first dimension.                                                                                                                        |
| `function`   | Lambda function<br /> `(x T) -> U`<br />or<br />Reference function <br /> | A lambda function (e.g., `(elem CHAR) -> LEFT(elem, 2)`) that takes an element of type `T` and returns a value of type `U`, which can be the same type as `T` or a different type, or a reference function with the correct argument list (e.g., `UPPER`). |

**Examples**

**Extract the First Initial from Each Element in an Array Using a Lambda Function**

With the specified data in the pipeline source record `{ "data": { "names": ["Jane", "Charles", "Kim"] }}`, create a data pipeline using the `TRANSFORM` function in the `SELECT` SQL statement with the lambda function `(el CHAR) -> LEFT(el,1)`. This function returns the first character of each element in the input array `$data.names[]`. The example focuses on the `SELECT` statement and does not show the rest of the data pipeline definition.

```sql
CREATE PIPELINE 
...
SELECT 
    TRANSFORM($data.names[], (el CHAR) -> LEFT(el,1)) as first_initial_array
```

*Output*: `["J", "C", "K]`

**Convert All Elements in an Array to Lowercase Using a Reference Function**

With the specified data in the pipeline source record `{ "data": { "names": ["Jane", "Joe", "Jill"] }}`, create a data pipeline using the `TRANSFORM` function in the `SELECT` SQL statement with the `LOWER` reference function. This function converts the input strings in `$data.names[]` to lowercase. The example focuses on the `SELECT` statement and does not show the rest of the data pipeline definition.

```sql
CREATE PIPELINE 
...
SELECT 
    TRANSFORM($data.names[], LOWER) as lower_case_name_array
```

*Output*: `["jane", "joe", "jill"]`

## FILTER

Filters elements in an array based on the logic in a lambda expression. The first argument is an expression that evaluates to an array that contains the elements to filter. The second argument is a lambda expression or reference function that returns `true` if the system should retain the element in the returned array.

The second argument must be of the form `(x T) -> BOOL`. This form returns a specified value for the specified element `x`. The lambda function specifies the type `T` of the element `x`.

The `FILTER` function includes elements in the first argument array in the resulting array when the lambda function returns `true`. Otherwise, the function excludes the elements.

**Syntax**

```sql
FILTER(array, function)
```

| **Argument** | **Data** **Type**                                                                  | **Description**                                                                                                                                                                                                                 |
| ------------ | ---------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `array`      | `T[]`                                                                              | The array of type `T` elements can be single or multi-dimensional. The `FILTER` function only iterates over the first dimension.                                                                                                |
| `function`   | Reference function <br />or<br />Lambda function<br /> `(x T) -> BOOL`<br /><br /> | A function that takes an element of type `T` and returns a `BOOL`.<br />The function can be a lambda function (e.g., `(elem CHAR) -> length(elem) > 10`) or a function name that has the correct argument list (e.g., `UPPER`). |

**Example**

With the specified data in the pipeline source record `{ "data": { "names": ["Jane", "Charles", "Kim"] }}`, filter the names in the input array for elements that are greater than five characters long using the Lambda function `(name CHAR) -> length(name) > 5`. The example focuses on the `SELECT` statement and does not show the rest of the data pipeline definition.

```sql
CREATE PIPELINE 
...
SELECT 
    FILTER($data.names[], (name CHAR) -> length(name) > 5) as long_names
```

*Output:&#x20;*`["Charles"]`

## ZIP\_WITH

Merges two or more input arrays, element-wise, into a single array using the trailing combining function. If one array is shorter than the other, the function appends NULLs at the end to match the length of the longest array at the start.

Returns an `array` with elements that result from applying the function to each pair of elements from `array1` and `array2`. If the arrays are not the same length, the function pads the shorter array with NULL values. The function returns an array of length `max(length(array1), length(array2))` containing elements of type `V` as returned by the lambda function.

`function` must be of the form `(x T, y U) -> V` where `x` is the element from `array1` and `y` is the element from `array2`. The lambda function specifies the types `T` and `U`. When zipping more arrays together, use the format `(x T1, y T2, z T3) -> V` where the arguments to the lambda function align with the order of the input arrays of the `ZIP_WITH` function.

**Syntax**

```sql
ZIP_WITH(array1, array2 [, ...], function)
```

| **Argument** | **Data** **Type**                                                            | **Description**                                                                                                                                                                                                                 |
| ------------ | ---------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `array1`     | `T1[]`                                                                       | The array of type `T1` elements can be single or multi-dimensional. The `ZIP_WITH` function only iterates over the first dimension.                                                                                             |
| `array2`     | `T2[]`                                                                       | The array of type `T2` elements can be single or multi-dimensional. The `ZIP_WITH` function only iterates over the first dimension.                                                                                             |
| `function`   | Lambda function<br /> `(x T) -> BOOL`<br />or<br />Reference function <br /> | A function that takes an element of type `T` and returns a `BOOL`.<br />The function can be a lambda function (e.g., `(elem CHAR) -> length(elem) > 10`) or a function name that has the correct argument list (e.g., `UPPER`). |

**Example**

With the specified data in the pipeline source record `{ "line_items": [{ "product_name": "suede shoes", "sku": "FT-1023-BLU" }, { "product_name": "cheap sunglasses", "sku": "ZZ-7821-K" }] }`, concatenate the product name `$line_items[].product_name` strings with the SKU `$line_items[].sku` strings as the format `"name(sku)"`. The example focuses on the `SELECT` statement and does not show the rest of the data pipeline definition.

```sql
CREATE PIPELINE 
...
SELECT ZIP_WITH(
  $line_items[].product_name, $line_items[].sku, 
  (name CHAR, sku CHAR) -> concat(name, '(', sku, ')')
)
```

*Output:&#x20;*`["suede shoes (FT-1023-BLU)", "cheap sunglasses (ZZ-7821-K)"]`

## Lambda Functions

A lambda function is a temporary function that takes zero or more named arguments as a parenthesis-enclosed list and returns an expression. The expression can reference objects defined within the closure of the expression, which includes locally named arguments that are locally defined within an enclosing scope, field references, literals, and so on.

**Syntax**

Define lambda functions using this syntax where `T1` represents an element of type `T1` and `U` represents the return type of the function.

```sql
(T1,...,Tn) -> U
```

When you use this function in a SQL statement, the syntax requires an argument name and the explicit designation of the type of each argument, but the return type `U` can be inferred. For example, define a lambda function that accepts two arguments of type `CHAR` and `INT` named `x` and `y`, respectively.

```sql
(x CHAR, y INT) -> LEFT(x, y)
```

**Examples**

**String Manipulation Using a Lambda Function**

Convert `x` to uppercase and return the first character.

```sql
(x CHAR) -> LEFT(UPCASE(x),1)
```

**Concatenate Using a Lambda Function**

Concatenate two strings (e.g., first and last name) with a space between them.

```sql
(x CHAR, y CHAR) -> CONCAT(x, ' ', y)
```

## Reference Functions

A reference function points to an existing function in the Ocient function list. Specifying the function name allows you to use that function in contexts where you would otherwise need to create a full lambda function.

The signature of the function must match the argument structure and return value requirements.

**Examples**

The reference function `UPCASE` is equivalent to the lambda function `(x CHAR) -> UPCASE(x)` because the syntax of `UPCASE` accepts one argument of type `CHAR` and returns a value of type `CHAR`.

Use the `UPCASE` function with the `TRANSFORM` function to capitalize all names in an array. These two SQL statements are equivalent.

`TRANSFORM($names[], UPCASE)`

`TRANSFORM($names[], (n CHAR) -> UPCASE(n))`

# Related Links

[Data Pipelines](<./././SQL Reference/Data Definition Language _DDL_ Statement Reference/Data Pipelines.md>)&#x20;

[CREATE TABLE](<./././SQL Reference/Data Definition Language _DDL_ Statement Reference/Tables.md>)&#x20;

[PREVIEW PIPELINE](<./././SQL Reference/Data Definition Language _DDL_ Statement Reference/Data Pipelines.md>)&#x20;

[Alphabetical SQL Functions List](<./././SQL Reference/Alphabetical SQL Functions List.md>) &#x20;

[Categorized SQL Functions List](<./././SQL Reference/Categorized SQL Functions List.md>)&#x20;



