Merging 2 Array variables

Nandan Hegde 29,896 MVP

Hello All.

I might be missing some simple aspect here but wanted to confirm :

Is there any way in ADF to merge 2 Array variables without the use of any iteration activity.

Like ask is as below:

Input1= [1,2,3]

Input2=[1,4,5]

Output=[1,2,3,1,4,5]

Note: the union function would only take the unique records but here the need is to have all values (which includes similar values)

1 answer

Dillon Silzer 54,731

Hey Nandan,

You could convert the array(s) to string using the join() function, then concat() the strings, then convert the finalString back to an array using map().

Join

https://learn.microsoft.com/en-us/azure/data-factory/control-flow-expression-language-functions#join

Concat

https://learn.microsoft.com/en-us/azure/data-factory/control-flow-expression-language-functions#concat

Map

https://learn.microsoft.com/en-us/azure/data-factory/data-flow-expressions-usage#map

If this is helpful please accept answer.

Nandan Hegde 29,896 MVP

Hey, I leveraged JOIN, CONCAT and SPLIT

{
    "name": "MergeArray_copy1",
    "properties": {
        "activities": [
            {
                "name": "Set variable1",
                "type": "SetVariable",
                "dependsOn": [],
                "userProperties": [],
                "typeProperties": {
                    "variableName": "v1",
                    "value": {
                        "value": "@join(pipeline().parameters.test1,';')",
                        "type": "Expression"
                    }
                }
            },
            {
                "name": "Set variable1_copy1",
                "type": "SetVariable",
                "dependsOn": [],
                "userProperties": [],
                "typeProperties": {
                    "variableName": "v2",
                    "value": {
                        "value": "@join(pipeline().parameters.test2,';')",
                        "type": "Expression"
                    }
                }
            },
            {
                "name": "Set variable2",
                "type": "SetVariable",
                "dependsOn": [
                    {
                        "activity": "Set variable1",
                        "dependencyConditions": [
                            "Succeeded"
                        ]
                    },
                    {
                        "activity": "Set variable1_copy1",
                        "dependencyConditions": [
                            "Succeeded"
                        ]
                    }
                ],
                "userProperties": [],
                "typeProperties": {
                    "variableName": "v3",
                    "value": {
                        "value": "@concat(variables('v1'),';',variables('v2'))",
                        "type": "Expression"
                    }
                }
            },
            {
                "name": "Set variable3",
                "type": "SetVariable",
                "dependsOn": [
                    {
                        "activity": "Set variable2",
                        "dependencyConditions": [
                            "Succeeded"
                        ]
                    }
                ],
                "userProperties": [],
                "typeProperties": {
                    "variableName": "a1",
                    "value": {
                        "value": "@split(variables('v3'),';')",
                        "type": "Expression"
                    }
                }
            }
        ],
        "parameters": {
            "test1": {
                "type": "array",
                "defaultValue": [
                    1,
                    2,
                    3
                ]
            },
            "test2": {
                "type": "array",
                "defaultValue": [
                    2,
                    4,
                    5
                ]
            }
        },
        "variables": {
            "v1": {
                "type": "String"
            },
            "v2": {
                "type": "String"
            },
            "a1": {
                "type": "Array"
            },
            "v3": {
                "type": "String"
            },
            "l1": {
                "type": "String"
            },
            "a2": {
                "type": "Array"
            },
            "l2": {
                "type": "String"
            }
        },
        "annotations": [],
        "lastPublishTime": "2023-01-30T07:24:12Z"
    },
    "type": "Microsoft.Synapse/workspaces/pipelines"
}