Array functions in mapping data flow

APPLIES TO: Azure Data Factory Azure Synapse Analytics

Tip

Try out Data Factory in Microsoft Fabric, an all-in-one analytics solution for enterprises. Microsoft Fabric covers everything from data movement to data science, real-time analytics, business intelligence, and reporting. Learn how to start a new trial for free!

Data flows are available both in Azure Data Factory and Azure Synapse Pipelines. This article applies to mapping data flows. If you are new to transformations, please refer to the introductory article Transform data using a mapping data flow.

The following articles provide details about array functions supported by Azure Data Factory and Azure Synapse Analytics in mapping data flows.

Array function list

Array functions perform transformations on data structures that are arrays. These include special keywords to address array elements and indexes:

• #acc represents a value that you wish to include in your single output when reducing an array
• #index represents the current array index, along with array index numbers #index2, #index3 ...
• #item represents the current element value in the array

Array function	Task
array	Creates an array of items. All items should be of the same type. If no items are specified, an empty string array is the default. Same as a [] creation operator.
at	Finds the element at an array index. The index is 1-based. Out of bounds index results in a null value. Finds a value in a map given a key. If the key is not found it returns null.
contains	Returns true if any element in the provided array evaluates as true in the provided predicate. Contains expects a reference to one element in the predicate function as #item.
distinct	Returns a distinct set of items from an array.
except	Returns a difference set of one array from another dropping duplicates.
filter	Filters elements out of the array that do not meet the provided predicate. Filter expects a reference to one element in the predicate function as #item.
find	Find the first item from an array that match the condition. It takes a filter function where you can address the item in the array as #item. For deeply nested maps you can refer to the parent maps using the #item_n(#item_1, #item_2...) notation.
flatten	Flattens array or arrays into a single array. Arrays of atomic items are returned unaltered. The last argument is optional and is defaulted to false to flatten recursively more than one level deep.
in	Checks if an item is in the array.
intersect	Returns an intersection set of distinct items from 2 arrays.
map	Maps each element of the array to a new element using the provided expression. Map expects a reference to one element in the expression function as #item.
mapIf	Conditionally maps an array to another array of same or smaller length. The values can be of any datatype including structTypes. It takes a mapping function where you can address the item in the array as #item and current index as #index. For deeply nested maps you can refer to the parent maps using the #item_[n](#item_1, #index_1...) notation.
mapIndex	Maps each element of the array to a new element using the provided expression. Map expects a reference to one element in the expression function as #item and a reference to the element index as #index.
mapLoop	Loops through from 1 to length to create an array of that length. It takes a mapping function where you can address the index in the array as #index. For deeply nested maps you can refer to the parent maps using the #index_n(#index_1, #index_2...) notation.
reduce	Accumulates elements in an array. Reduce expects a reference to an accumulator and one element in the first expression function as #acc and #item and it expects the resulting value as #result to be used in the second expression function.
size	Finds the size of an array or map type
slice	Extracts a subset of an array from a position. Position is 1 based. If the length is omitted, it is defaulted to end of the string.
sort	Sorts the array using the provided predicate function. Sort expects a reference to two consecutive elements in the expression function as #item1 and #item2.
unfold	Unfolds an array into a set of rows and repeats the values for the remaining columns in every row.
union	Returns a union set of distinct items from 2 arrays.

Related content

• List of all aggregate functions.
• List of all cached lookup functions.
• List of all conversion functions.
• List of all date and time functions.
• List of all expression functions.
• List of all map functions.
• List of all metafunctions.
• List of all window functions.
• Usage details of all data transformation expressions.
• Learn how to use Expression Builder.