Azure Data Factory json manipulation

MS Prog 471

Hi,

Good day.

We have an input JSON that is of the following schema:

{
  "result": [
    {
      "table": "test1",
      "field": "completed_on",
      "sys_id": "7d6a0a84dc0",
      "created_by": "user1"
    },
    {
      "table": "test2",
      "field": "statew",
      "sys_id": "a0a84dc0",
      "created_by": "user2"
    }
  ]
}

This needs to be transformed to a array of objects as below:

[{"table": "test1","field": "completed_on","sys_id": "7d6a0a84dc0","created_by": "user1"},
 {"table": "test2","field": "statew","sys_id": "a0a84dc0","created_by": "user2"}
]

As you can see, we removed the "result" element from the input.

Any way I could achieve this transformation in ADF please?

MS Prog 471 Reputation points

2023-03-24T04:16:56.5933333+00:00

Thanks for this @Sedat SALMAN . on step 3) you say to set the array path.

I can't see a property called Array path in the Source tab of Copy Data.

Please can you elaborate.
Sedat SALMAN 13,075 Reputation points

2023-03-24T12:31:14.1733333+00:00
Yes, you can achieve this transformation in Azure Data Factory (ADF) using the Mapping Data Flow component. Here's a step-by-step guide on how to transform the input JSON to the desired output:

Create a new pipeline in ADF.

Add a "Copy Data" activity to read the input JSON file from the source datastore (e.g., Blob Storage or Azure Data Lake Storage).

In the "Source" tab of the "Copy Data" activity, configure the source dataset with your JSON file format and settings. Make sure to set the "Array path" to "$.result" to read the array of objects.

Add a "Data Flow" activity in the pipeline.

Inside the "Data Flow" activity, add a "Source" transformation and configure it with the output dataset from the "Copy Data" activity.

Add a "Sink" transformation to the "Data Flow" and connect it to the "Source" transformation.

In the "Sink" tab of the "Data Flow" activity, configure the output dataset where you want to write the transformed JSON file. Make sure to set the "Output format" to "Array of objects" to achieve the desired output.

Publish the pipeline and execute it.

This pipeline will read the input JSON, extract the "result" array, and write it as an array of objects to the destination datastore without the "result" element.

Note: If your input JSON is small, you can skip the "Copy Data" activity and directly read the input JSON file in the "Data Flow" activity using the "Source" transformation. However, using a "Copy Data" activity can be beneficial when dealing with large input files, as it allows ADF to read the data more efficiently.
MS Prog 471 Reputation points

2023-03-24T12:31:59.5533333+00:00

Thanks for this @Sedat SALMAN . on step 3) you say to set the array path.

I can't see a property called Array path in the Source tab of Copy Data.

Please can you elaborate.
ShaikMaheer-MSFT 37,896 Reputation points Microsoft Employee

2023-03-24T12:34:04.3366667+00:00

Hi MS Prog,

Thank you for posting query in Microsoft Q&A Platform.

Could you please elaborate, your ask more here? From where you are getting this json here? If it from some lookup activity in ADF pipeline by reading some file? Also, where you want to store that array? In some variable in pipeline? Right now result property on above json will give you exactly same result what you are expecting right? Kindly help to understand where you want to store your array here?

1 answer

MS Prog 471 Reputation points

2023-03-27T00:20:09.94+00:00

Hey @ShaikMaheer-MSFT

The requirement is to read an json from a blob container, transform it and store it another container. I need help to do the transformation i.e. remove the root node "result" so we get an json array as described in my orignal post. This transformed json needs to be stored inanother blob container.

I need help to understand how i can achieve this transformation please
Please sign in to rate this answer.

0 comments No comments
Sign in to comment