azure-data-factory

Web Activity contruct body of multiple row from database table- ADF


I have a lookup in an adf pipeline. This looks up data of thousands of rows from a database table and gives the output

Output of lookup activity:

{ "count": 1099, "value": [ { "VERSION": "current", "DATE": "202412", "AMT" :"11.22"

    },
{
        "VERSION": "current",
        "DATE": "202411",
        "AMT":"12.11"
        
    },

similarly, it has array elements for 1000 more.

I need to read this 1000+ rows and reconstruct this array and pass it as a body in web activity because this code didnt work inside web activity (which uses the input of lookup)

 "Data": [
  {
  "@{activity('Lookup1').output.value}"
}
]

I need to able to reconstruct and map column name to value. Is there any method to do this?

my body inside web activity needs to be like

"Data":[
{
            "VERSION": "current",
            "DATE": "202412",
            "AMT" :"11.22"
        
        },
    {
            "VERSION": "current",
            "DATE": "202411",
            "AMT":"12.11"
            
        },

and 1000 more rows from the database name ]


Solution

  • After the lookup activity, you can use the below expression in the web activity body to get the required JSON format.

    @json(concat('{"Data":',string(activity('Lookup1').output.value),'}'))
    

    enter image description here

    Upon running the pipeline run, you can see the generated body JSON in the web activity input.

    enter image description here