JSON Processing in ADF - Copy Activity

Question

Hey

Please i need some help here.

we have an ADF pipeline with a Copy Activity.

The source is a rest api which returns a JSON response.

The sink is a blob connector of type json.

I tested the rest api independently and the rest api returns a response like below:

{"result":[{"level":"1","source":"Remote Process Sync","created":"2023-05-10 02:51:59","created_by":"system","application":null},
           {"level":"1","source":"Remote Process Sync","created":"2023-05-10 02:59:00","created_by":"system","application":null}]
		   }

BUt after running the pipeline and i look at the output json file in the blob container, it looks like this :

[
{"result":[{"level":"1","source":"Remote Process Sync","created":"2023-05-10 02:51:59","created_by":"system","application":null},
           {"level":"1","source":"Remote Process Sync","created":"2023-05-10 02:59:00","created_by":"system","application":null}]
		   }
]

If you noticed, It appears like the SINK Blob connector of type JSON encloses the api response in [ ]

I have 2 requirements( I am fine with building 2 different pipelines for these 2 requirements)

1. I need to get the json blob file exactly like the api response i.e. without the outer [ ]
2. I need to get the json blob file from the result array in the api response . see below

[{"level":"1","source":"Remote Process Sync","created":"2023-05-10 02:51:59","created_by":"system","application":null},
           {"level":"1","source":"Remote Process Sync","created":"2023-05-10 02:59:00","created_by":"system","application":null}]

Please let me know if either or both are possible in ADF.

Answer 1

for requirement 1

you can use flatten transformation as in the link

https://learn.microsoft.com/en-us/azure/data-factory/data-flow-flatten

for requirement 2

Add a mapping between the 'result' array in the source JSON and the target JSON sink.

https://learn.microsoft.com/en-us/azure/data-factory/concepts-data-flow-overview