Merging Arrays inside Synapse / Azure Knowledge Manufacturing facility Pipeline

Drawback Assertion

In my earlier Weblog Itemizing Distinctive Data Inside an Array in Azure Knowledge Manufacturing facility, we recognized that the UNION operate merges all of the arrays by eradicating the duplicates and itemizing solely distinctive data.

Is it doable to merge arrays throughout the Synapse / Azure Knowledge Manufacturing facility pipeline by retaining all values (together with duplicates)?

Conditions

Azure Knowledge Manufacturing facility / Synapse

Answer

1. The “be a part of” operate returns a string that has all the objects from an array, separated by the required character. We will leverage this operate to transform the Array right into a String.
2. The “concat” operate combines two or extra strings and returns the mixed string. We will leverage this operate to mix all values obtained in #1.
3. The “break up” operate returns an array containing substrings separated by commas from a bigger string based mostly on a specified delimiter character within the unique string. We will leverage this operate to rework the String obtained in #2 again to an Array.
4. Let’s say now we have a listing of values in an Array variable.

GitHub code

UtilizingSet Variable exercise and Be a part of Perform ( Related for JoinVar1 and Joinvar2).

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

Output

Word. One may also do it by leveraging a single Set Variable Exercise.

@break up(concat(be a part of(variables('Array1'),';'),';',be a part of(variables('Array2'),';')),';' )