How to Create and append a column from one JSON to another in Azure Data Factory ?

Sudarshan Kumar 20 Reputation points

This is my Pipeline User's image The Output from LookupDatModel is Below

{ "count": 1, "value": [ { "ID": 71431952, "isProtected": null } ], "effectiveIntegrationRuntime": "selfhostedintegrationRuntime1", "billingReference": { "activityType": "PipelineActivity", "billableDuration": [ { "meterType": "SelfhostedIR", "duration": 0.016666666666666666, "unit": "Hours" } ], "totalBillableDuration": [ { "meterType": "SelfhostedIR", "duration": 0.016666666666666666, "unit": "Hours" } ] }, "durationInQueue": { "integrationRuntimeQueue": 2 } }

and the Output from DowdloadTextract is below


The Output we need is to append DownloadTextract columns value to LookupDataModel So the Final Output that i would like to save in Storage is

{ "count": 1, "value": [ { "ID": 71431952, "isProtected": null, "Response": "Research Report:   " } ] }

Please suggest is there any way to do this . For now I am saving both output to Storage because i did not get any way to combine then . When i append the complete json gets appended .

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,072 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Azar 14,020 Reputation points


    I guess, you can use the "Derived Column" activity within your pipeline.

    In your pipeline, add a "Derived Column" activity after both the LookupDataModel and DownloadTextract activities.

    • Open the settings for the Derived Column activity.
    • Define a new column in the "Derived Column" settings. Let's name it "Response" and set its value to "Research Report: ".
    • Make sure the output column is added to the existing schema. You should have a mapping similar to "Response": "Research Report: ". This will append the "Response" column to your existing data.

    Connect the output of the Derived Column activity to your sink dataset, which is your storage destination.

    By doing this, you'll create a new column called "Response" with the value "Research Report: " and append it to each row of your existing data from LookupDataModel. like this.

    1. LookupDataModel
    2. DerivedColumn1
    3. DownloadTextract
    4. DerivedColumn2
    5. Sink (Storage Destination)

    If this helps kindly accept the answer thanks much.