Json flattening in data factory

Question

I am unable to flatten the below format json. Appreciate any help on this.

{"id":"902100c4776e4b848f013d8e291cb9ac","subject":"Subject","data":{ "id": "4691824", "PatientNumber": 21385921, "FirstName": "Paul", "LastName": "Hill", "Email": "paul.hill@datacourse.com", "PatientGender": "Male", "PatientAge": 67, "City": "Longview", "State": "MA", "rid": "S3kfAIo4MWsBAAAAAAAAAA", "self": "dbsS3kfAAcollsS3kfAIo4MWsdocsS3kfAIo4MWsBAAAAAAAAAA", "etag": 320021f90000010000006539710e0000, "attachments": "attachments", "ts": 1698263310, "lsn": 1998},"eventType":"EventType","dataVersion":"1.0.0","metadataVersion":"1","eventTime":"20231025T19:48:31.4798298Z","topic":"subscriptionsd1d8529f7b0842cb8544d94c36ad8dc6resourceGroupskuppurilabfuncapptoblobprovidersMicrosoft.EventGridtopicspatientdataeventgrid"}

Answer 1

Hi Prasad Kuppuri ,

Welcome to Microsoft Q&A platform and thanks for posting your question here.

As per my understanding you are trying to flatten json data using dataflow in ADF , however, it's not doable .

Flatten transformation requires 'Array' data to 'unroll by' . Kindly use 'collect' function in 'aggregate' transformation to convert complex datatype into array format and then use it to unroll by.

For more details on the implementation , kindly check out the following post:

https://learn.microsoft.com/en-us/answers/questions/601502/convert-json-objets-to-array-using-azure-data-flow

Hope it helps. Kindly accept the answer if it was helpful. Thankyou