Merging Arrays within Synapse / Azure Data Factory Pipeline

Problem Statement :

In my previous Blog Listing Unique records within an Array in Azure Data Factory , we identified that the UNION function merges all the arrays by removing the duplicates and listing only unique records.

Is it possible to merge arrays within Synapse / Azure Data Factory pipeline by retaining all values (including duplicates)

Prerequisites :

1. Azure Data Factory / Synapse

Solution :

1. The “join” function returns a string that has all the items from an array, separated by the specified character. We can leverage this function to convert the Array into String.
2. The “concat” function combines two or more strings, and return the combined string. We can leverage this function to combine all values obtained in #1
3. The “split” function returns an array that contains substrings, separated by commas, from a larger string based on a specified delimiter character in the original string. We can leverage this function to transform the String obtained in #2 back to an Array.
4. Let’s say we have a list of values in an Array variable

5.

GitHub Code

Using Set Variable activity and Join Function ( Similar for JoinVar1 and Joinvar2)

6.

@concat(variables('String1'),';',variables('String2'))

7.

Output :

Note : One can also do it leveraging a single Set Variable Activity

@split(concat(join(variables('Array1'),';'),';',join(variables('Array2'),';')),';' )