Array functions in mapping data flow
APPLIES TO: Azure Data Factory Azure Synapse Analytics
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:
#accrepresents a value that you wish to include in your single output when reducing an array
#indexrepresents the current array index, along with array index numbers
#index2, #index3 ...
#itemrepresents the current element value in the array
|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
|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.|
- 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.