Manipulating Json in Azure Data Factory activities

Question

I'm pulling data from an API which requires several calls to make happen. Firstly I make a request which gives me a response body in the form:

{
"code": "200",
"body": { 
         "date1": "date",
         "date2": "date"
}

This response body is then used as part of a POST request body to execute a query. The POST body is is required in the format of:

{
"queryId": "id",
"queryBody": "{ResponseBody}",
"type": "type"
}

I've tried creating a string object and concatinating the queryId and Type around the body but I can't seem to get this to work in ADF. I've tried writing the json body to files and merging in a copy task which just creates a 2 line json ie seperate objects in 1 file, not a single object, unioning in a dataflow which seems to do the same, and I can't seem to get adf to cooperate.

Does anyone have any ideas?

Answer 1

You can use @json() function to build the required JSON.

After getting first response JSON, use a string set variable activity and build the required JSON string.

@concat('{"queryId":"id","queryBody":',string(pipeline().parameters.response.body),',"type": "type" }')

Here, I have stored the response JSON in a pipeline parameter for demo. In your case, you will be getting the response JSON from a web activity. So, the expression will be like this.

@concat('{"queryId":"id","queryBody":',string(@activity('Web1').output.body),',"type": "type" }')

You can change the JSON string as per your requirement.

It will generate the required JSON string like below.

Now, when you are posting this JSON to another API, use @json(variables('json_string')) in the POST request body like below.

This will generate the required JSON. Here, you can see it in the activity input.