ADF Pipeline Script Activity output - set variables

I have pretty easy select to Azure SQL DB which return 2 rows with 2 attributes. In output it looks like this:

{
    "resultSetCount": 1,
    "recordsAffected": 0,
    "resultSets": [
        {
            "rowCount": 2,
            "rows": [
                {
                    "Name": "Adam",
                    "Surname": "Black"                  
                },
                {
                    "Name": "Richard",
                    "Surname": "Nolan"
                }
            ]
        }
    ],

And I want to Set Variable to get value {"Name":["Adam","Richard"]} but I am not able to achieve it at all.

@activity('Script1').output.resultSets[0].rows[0].Name - does not work as I expected

Any advice, please?

You need to use combination of For-each activity and append variable activity to achieve your requirement.

First create an array variable with no values in the pipeline variables section. Here, I have created two array variables where the second variable is just for showing result and it's not required.

Take a for-each activity and give the rows array @activity('Script1').output.resultSets[0].rows from the Script activity to it and check the Sequential checkbox in this as it preserves the order of the array.

Inside for-each, take an append variable activity and select the array variable that created earlier. Give the expression @item().Name to it. It appends the Name value to the created array in each iteration.

At the end of the for-each the result array will be stored in the mynames array and you can use it in further activities as per your requirement. For just showing the result, I have assigned this array variable to another array variable res_display in a set variable activity after the for-each activity.

Debug the pipeline and you will get the expected array: