I am new to ADF and looking to flatten the data of a complex JSON object. The JSON comes in from Genesys API endpoint. Although Genesys has defined a response schema, there is no guarantee that all the keys defined in the schema will be part of the response JSON. Following is an example of the JSON object with a different set of keys. I have replaced identifiable information with "hidden" for relevant keys.
{
"conversationEnd": "2022-03-20T18:53:14.696Z",
"conversationId": "hidden",
"conversationStart": "2022-03-20T18:52:43.441Z",
"divisionIds": [
"hidden"
],
"mediaStatsMinConversationMos": 4.882435906757346,
"mediaStatsMinConversationRFactor": 92.44477081298828,
"originatingDirection": "inbound",
"participants": [
{
"participantId": "hidden",
"participantName": "hidden",
"purpose": "external",
"sessions": [
{
"ani": "hidden",
"direction": "inbound",
"dnis": "hidden",
"edgeId": "hidden",
"mediaType": "voice",
"protocolCallId": "hidden",
"provider": "Edge",
"recording": true,
"remoteNameDisplayable": "hidden",
"sessionDnis": "hidden",
"sessionId": "hidden",
"mediaEndpointStats": [
{
"codecs": [
"audio/opus"
],
"eventTime": "2022-03-20T18:53:14.724Z",
"maxLatencyMs": 30,
"minMos": 4.882435906757346,
"minRFactor": 92.44477081298828,
"receivedPackets": 950
}
],
"metrics": [
{
"emitDate": "2022-03-20T18:52:55.715Z",
"name": "nConnected",
"value": 1
}
],
"segments": [
{
"conference": false,
"segmentEnd": "2022-03-20T18:52:55.715Z",
"segmentStart": "2022-03-20T18:52:43.442Z",
"segmentType": "system"
},
{
"conference": false,
"disconnectType": "endpoint",
"q850ResponseCodes": [
16
],
"segmentEnd": "2022-03-20T18:53:14.695Z",
"segmentStart": "2022-03-20T18:52:55.715Z",
"segmentType": "interact"
}
]
}
]
},
{
"participantId": "hidden",
"purpose": "hidden",
"userId": "hidden",
"sessions": [
{
"ani": "hidden",
"direction": "inbound",
"dnis": "",
"edgeId": "hidden",
"mediaType": "voice",
"peerId": "",
"protocolCallId": "hidden",
"provider": "hidden",
"remote": "hidden",
"sessionDnis":"hidden",
"sessionId": "hidden",
"metrics": [
{
"emitDate": "2022-03-20T18:52:55.468Z",
"name": "tAlert",
"value": 12000
}
],
"segments": [
{
"conference": false,
"disconnectType": "noAnswerTransfer",
"segmentEnd": "2022-03-20T18:52:55.468Z",
"segmentStart": "2022-03-20T18:52:43.468Z",
"segmentType": "alert"
}
]
}
]
},
{
"participantId": "hidden",
"purpose": "hidden",
"userId": "hidden",
"sessions": [
{
"ani": "hidden",
"direction": "hidden",
"dnis": "hidden",
"edgeId": "hidden",
"mediaType": "hidden",
"peerId": "hidden",
"protocolCallId": "hidden",
"provider": "hidden",
"remote": "hidden",
"sessionDnis": "hidden",
"sessionId": "hidden",
"metrics": [
{
"emitDate": "2022-03-20T18:53:14.696Z",
"name": "tVoicemail",
"value": 19022
}
],
"segments": [
{
"conference": false,
"segmentEnd": "2022-03-20T18:52:55.674Z",
"segmentStart": "2022-03-20T18:52:55.518Z",
"segmentType": "system"
},
{
"conference": false,
"disconnectType": "peer",
"q850ResponseCodes": [
16
],
"segmentEnd": "2022-03-20T18:53:14.696Z",
"segmentStart": "2022-03-20T18:52:55.674Z",
"segmentType": "interact"
}
]
}
]
}
]
}
The data flow is unable to create a projection for the above JSON. Could any of you please help?
You can achieve this by using Flatten Transformation in Mapping data flow.
To handle data which is not compulsorily available in the file, use
Unroll byfeature. This will select an array to unroll. The output data will have one row per item in each array. If the unroll by array in the input row is null or empty, there will be one output row with unrolled values as null.Refer this official document to learn more and refer the given example to accomplish the flatten transformation.