Documentation Experience Platform Destinations Guide

Perform transformations on data exported to cloud storage destinations using calculated fields

Last update: May 15, 2025

Topics:
Destinations

CREATED FOR:

Admin
User

AVAILABILITY

The functionality to perform transformations on data exported to cloud storage destinations is generally available for the following destinations: Azure Data Lake Storage Gen2, Data Landing Zone, Google Cloud Storage, Amazon S3, Azure Blob, SFTP, as well as any custom partner-authored cloud storage destinations authored through Destination SDK.

To perform various transformations on data exported to cloud storage destinations, you must use the calculated fields functionality in the mapping step of the export workflow. For detailed information about calculated fields, visit the pages linked below. These pages include an introduction to calculated fields in Data Prep and more information about all the available functions:

Prerequisites

To use calculated fields for data transformations:

Connect to a desired cloud storage destination. When connecting to the desired cloud destination, toggle the Export arrays, maps, objects option off.
Go through the activation steps for cloud storage destinations and get to the mapping step.

How to work with calculated fields

IMPORTANT

Whenever you work with calculated fields, in addition to any data transformation function that you apply, you must also use the array_to_string function to concatenate fields into a string.

In the mapping step of the activation workflow for cloud storage destinations, select Add calculated field.

TIP

The Add calculated field control is disabled for destination connections where the Export arrays, maps, and objects control was toggled off. Read more.

Add calculated field highlighted in the mapping step of the batch activation workflow.

This opens a modal window where you can select functions and fields to export attributes out of Experience Platform.

Modal window of the calculated field functionality with no function selected yet.

For example, use the array_to_string function on the organizations field as shown below to export the organizations array as a string in a CSV file. View more information about this and other examples further below.

Modal window of the calculated field functionality with the array-to-string function selected.

Select Save to keep the calculated field and return to the mapping step.

Modal window of the calculated field functionality with the array-to-string function selected and the Save control highlighted.

Back in the mapping step of the workflow, fill in the Target field with a value of the column header you want for this field in the exported files.

Mapping step with the target field highlighted.

Select target field 2

When ready, select Next to proceed to the next step of the activation workflow.

Mapping step with the target field highlighted and a target value filled in.

Sample supported functions to perform data transformations

All the documented Data Prep functions are supported when activating data to file-based destinations.

The functions below, specific to handling exports of arrays, or applying hashing to fields, are documented along with examples.

array_to_string
flattenArray
filterArray
transformArray
coalesce
size_of
iif
index-based array access
add_to_array
to_array
first
last

Examples of functions used to perform data transformations

See examples and further information in the sections below for some of the functions listed above. For the rest of the listed functions, refer to the general functions documentation in the Data Prep section.

`array_to_string` function to export arrays

Use the array_to_string function to concatenate the elements of an array into a string, using a desired separator, such as _ or |. This function is useful when you want to export the elements of an array from Experience Platform into a CSV file.

For example, you can combine the following XDM fields below as shown in the mapping screenshot by using an array_to_string('_',organizations) syntax:

organizations array
person.name.firstName string
person.name.lastName string
personalEmail.address string

Mapping example including the array_to_string function.

In this case, your output file looks like below. Notice how the elements of the array are concatenated into a single string using the _ character.

First_Name,Last_Name,Personal_Email,Organization
John,Doe,johndoe@acme.org, "{'id':123,'orgName':'Acme Inc','founded':1990,'latestInteraction':1708041600000}_{'id':456,'orgName':'Superstar Inc','founded':2004,'latestInteraction':1692921600000}_{'id':789,'orgName':'Energy Corp','founded':2021,'latestInteraction':1725753600000}"

`filterArray` function to export filtered arrays

Use the filterArray function to filter the elements of an exported array. You can combine this function with the array_to_string function described further above.

Continuing with the organizations array object from above, you can write a function like array_to_string('_', filterArray(organizations, org -> org.founded > 2021)), which returns the organizations with a value for founded in the year 2021 or more recent.

Example of the filterArray function.

In this case, your output file looks like below. Notice how the two elements of the array that meet the criterion are concatenated into a single string using the _ character.

John,Doe,johndoe@acme.org, "{'id':123,'orgName':'Acme Inc','founded':1990,'latestInteraction':1708041600000}_{'id':789,'orgName':'Energy Corp','founded':2021,'latestInteraction':1725753600000}"

`transformArray` function to export transformed arrays

Use the transformArray function to transform the elements of an exported array. You can combine this function with the array_to_string function described further above.

Continuing with the organizations array object from above, you can write a function like array_to_string('_', transformArray(organizations, org -> ucase(org.orgName))), returning the names of the organizations converted to all uppercase.

Example of the transformArray function.

In this case, your output file looks like below. Notice how the three elements of the array are transformed and concatenated into a single string using the _ character.

John,Doe,johndoe@acme.org,ACME INC_SUPERSTAR INC_ENERGY CORP

`iif` function to export arrays

Use the iif function to export elements of an array under certain conditions. For example, continuing with the organizations array object from above, you can write a simple conditional function like iif(organizations[0].equals("Marketing"), "isMarketing", "isNotMarketing").

Mapping example including the iif function.

In this case, your output file looks like below. In this case, the first element of the array is Marketing, so the person is a member of the marketing department.

`First_Name,Last_Name, Personal_Email, Is_Member_Of_Marketing_Dept
John,Doe, johndoe@acme.org, "isMarketing"

`add_to_array` function to export arrays

Use the add_to_array function to add elements to an exported array. You can combine this function with the array_to_string function described further above.

Continuing with the organizations array object from above, you can write a function like source: array_to_string('_', add_to_array(organizations,"2023")), returning the organizations that a person is a member of in the year 2023.

Mapping example including the add_to_array function.

In this case, your output file looks like below. Notice how the three elements of the array are concatenated into a single string using the _ character and 2023 is also appended at the end of the string.

`First_Name,Last_Name,Personal_Email,Organization_Member_2023
John,Doe, johndoe@acme.org,"Marketing_Sales_Finance_2023"

`flattenArray` function to export flattened arrays

Use the flattenArray function to flatten an exported multidimensional array. You can combine this function with the array_to_string function described further above.

Continuing with the organizations array object from above, you can write a function like array_to_string('_', flattenArray(organizations)). Note that the array_to_string function flattens the input array by default into a string.

The resulting output is the same as for the array_to_string function described further above.

`coalesce` function to export arrays

Use the coalesce function to access and export the first non-null element of an array into a string.

For example, you can combine the following XDM fields below as shown in the mapping screenshot by using a coalesce(subscriptions.hasPromotion) syntax to return the first true of false value in the array:

"subscriptions.hasPromotion": [null, true, null, false, true] array
person.name.firstName string
person.name.lastName string
personalEmail.address string

Mapping example including the coalesce function.

In this case, your output file looks like below. Notice how the first non-null true value in the array is exported in the file.

First_Name,Last_Name,hasPromotion
John,Doe,true

`size_of` function to export arrays

Use the size_of function to indicate how many elements exist in an array. For example, if you have a purchaseTime array object with multiple timestamps, you can use the size_of function to indicate how many separate purchases were made by a person.

For example, you can combine the following XDM fields below as shown in the mapping screenshot.

"purchaseTime": ["1538097126","1569633126,"1601255526","1632791526","1664327526"] array indicating five separate purchase times by the customer
personalEmail.address string

Mapping example including the size_of function.

In this case, your output file looks like below. Notice how the second column indicates the number of elements in the array, corresponding with the number of separate purchases made by the customer.

`Personal_Email,Times_Purchased
johndoe@acme.org,"5"

Index-based array access

IMPORTANT

Unlike the other functions described on this page, to export individual elements of an array, you do not need to use the Calculated fields control in the UI.

You can access an index of an array to export a single item from the array. For example, similar to the example above for the size_of function, if you are looking to access and export only the first time that a customer has purchased a certain product, you can use purchaseTime[0] to export the first element of the timestamp, purchaseTime[1] to export the second element of the timestamp, purchaseTime[2] to export the third element of the timestamp, and so on.

Mapping example showing how an element of an array can be accessed.

In this case, your output file looks like below, exporting the first time that the customer has made a purchase:

`Personal_Email,First_Purchase
johndoe@acme.org,"1538097126"

`first` and `last` functions to export arrays

Use the first and last functions to export the first or last element in an array. For example, continuing with the purchaseTime array object with multiple timestamps from the previous examples, you can use these to functions to export the first or the last purchase time made by a person.

Mapping example including the first and last functions.

In this case, your output file looks like below, exporting the first and the last time that the customer has made a purchase:

`Personal_Email,First_Purchase, Last_Purchase
johndoe@acme.org,"1538097126","1664327526"

Hashing functions

Other available functions are specific for exporting arrays or elements from an array. You can use hashing functions to hash attributes in the exported files. For example, if you have any personally identifiable information in attributes, you can hash those fields when exporting them.

You can hash string values directly, for example md5(personalEmail.address). If desired, you can also hash individual elements of array fields, assuming elements in the array are strings, like this: md5(purchaseTime[0])

The supported hashing functions are:

Function

Sample expression

sha1

sha1(organizations[0])

sha256

sha256(organizations[0])

sha512

sha512(organizations[0])

hash

hash("crc32", organizations[0], "UTF-8")

md5

md5(organizations[0], "UTF-8")

crc32

crc32(organizations[0])

recommendation-more-help

Perform transformations on data exported to cloud storage destinations using calculated fields

Prerequisites

How to work with calculated fields

Sample supported functions to perform data transformations

Examples of functions used to perform data transformations

array_to_string function to export arrays

filterArray function to export filtered arrays

transformArray function to export transformed arrays

iif function to export arrays

add_to_array function to export arrays

flattenArray function to export flattened arrays

coalesce function to export arrays

size_of function to export arrays