Products

Openetl keyboard_arrow_right Documentation keyboard_arrow_right Transformations

Transformations

Transformations modify data during pipeline execution. OpenETL provides built-in transformations and supports custom transformation functions. This document covers transformation types, configuration, and implementation.

Built-in Transformations

OpenETL provides built-in transformations identified by string type names:

Transformation Description
concat Concatenates multiple fields into one with a glue.
renameKey Renames a field in the data object.
uppercase Converts a field's value to uppercase.
lowercase Converts a field's value to lowercase.
trim Trims whitespace from a field's value.
split Splits a field's value into an array by delimiter.
replace Replaces a search string in a field.
addPrefix Adds a prefix to a field's value.
addSuffix Adds a suffix to a field's value.
toNumber Converts a field's value to a number.
extract Extracts a substring or pattern match from a field.
mergeObjects Merges multiple fields into a single object.

Transformations are applied via the transform array in connector or pipeline configurations.

String-Based Transformations

Built-in transformations use string identifiers to enable JSON serialization. This allows transformation configurations to be stored in databases or configuration files:

transform: [{
  type: 'uppercase',
  options: { field: 'name', to: 'name_upper' }
}]

Can be saved as:

{
  "transform": [
    {
      "type": "uppercase",
      "options": { "field": "name", "to": "name_upper" }
    }
  ]
}

JSON-serialized transformations can be stored and retrieved from external systems. Custom transformation functions provide additional flexibility but cannot be serialized to JSON.

Transformation Examples

1. Concatenate Fields (concat)

Combine first_name and last_name:

transform: [{
  type: 'concat',
  options: {
    properties: ['first_name', 'last_name'],
    glue: ' ',
    to: 'full_name'
  }
}]

Input:

{
    first_name: 'John',
    last_name: 'Doe'
}

Output:

{
    first_name: 'John',
    last_name: 'Doe',
    full_name: 'John Doe'
}

2. Rename a Field (renameKey)

Rename id to user_id:

transform: [{
  type: 'renameKey',
  options: {
    from: 'id',
    to: 'user_id'
  }
}]

Input:

{
    id: 123,
    name: 'John'
}

Output:

{
    user_id: 123,
    name: 'John'
}

3. Convert to Uppercase (uppercase)

Uppercase a field:

transform: [{
  type: 'uppercase',
  options: {
    field: 'name',
    to: 'name_upper'
  }
}]

Input:

{
    name: 'john'
}

Output:

{
    name: 'john',
    name_upper: 'JOHN'
}

4. Custom Transformation (Function)

Add a timestamp to each record with a custom function:

const addTimestamp = (data: any[]) => {
  return data.map(item => ({
    ...item,
    timestamp: new Date().toISOString()
  }));
};

transform: [addTimestamp]

Input:

[
    {
        name: 'John'
    },
    {
        name: 'Jane'
    }
]

Output:

[
    {
        name: 'John',
        timestamp: '2023-10-05T12:00:00.000Z'
    },
    {
        name: 'Jane',
        timestamp: '2023-10-05T12:00:00.000Z'
    }
]

Custom transformation functions operate on the entire dataset and can implement logic not available in built-in transformations.

Additional Resources

Edit this page on GitHub