ADF Copy Data JSON Source dynamic schema mapping

Question

Hi
I am working on ADF Copy data activity. HTTP Dataset is returning a JSON with the following sample JSON output

{
"totalRowCount": 1,
"data": [
{
"ProductCode": "P - 1",
"ProductType": "CARS",
"fields": [
{
"fieldName": "CarType",
"label": "Is this an electric car?",
"dataTypeName": "RADIOBUTTON",
"fields": null,
"values": [
{
"value": "No"
}
]
},
{
"fieldName": "CarLocation",
"label": "Where is the Vendor located",
"dataTypeName": "SINGLESELECT",
"fields": null,
"values": [
{
"value": "123 NY Park NY City USA"
}
]
}
]
}
]
}

For ADF Copy data activity the dynamic mapping I am providing is this

{"type":"TabularTranslator",
"mappings":[
{"source":{"path":"['ProductCode']"},"sink":{"name":"ProductCode","type":"String"}}
,{"source":{"path":"['ProductType']"},"sink":{"name":"ProductType","type":"String"}}
,{"source":{"path":"['fields'][0]['fieldName']"},"sink":{"name":"fieldName","type":"String"}}
,{"source":{"path":"['fields'][0]['label']"},"sink":{"name":"label","type":"String"}}
,{"source":{"path":"['fields'][0]['dataTypeName']"},"sink":{"name":"dataTypeName","type":"String"}}
,{"source":{"path":"['fields'][0]['values'][0]['value']"},"sink":{"name":"value","type":"String"}}
,{"source":{"path":"['fields'][0]['fields']"},"sink":{"name":"Fields","type":"String"}}]
,"collectionReference":"$['data']"}

However it is copying only first element of the array but I am expecting 2 rows.
What needs to be changed in the mapping .
Thanks in advance as I am new to this

Answer 1

Hello @devhere,

Thanks for the ask and using Microsoft Q&A platform .

If my understanding is correct you are performing a copy activity from a JSON to sink. You are not able to encountering the issue.

You are likely to encounter the issue if you are not providing collection reference is incorrect. I looked at the mapping & understood that the collection reference you have provided as data.

The below would be solution :

Setting the collection reference as $['data'][0]['fields']

STEPS :

1. You could clear current mapping.
2. Enter $['data'][0]['fields'] manually
3. Click import schemas
4. Ensure that you are giving data type for mappings.

Output :

Please do let me know how it goes.

-------------------------------------------------------------------------------------------------------------------------

• Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification
• If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators

Answer 2

Another example from my JSON. It can have more than 1 row

{
"totalRowCount": 2,
"data": [
{
"ProductCode": "P - 1",
"ProductType": "CARS",
"fields": [
{
"fieldName": "CarType",
"label": "Is this an electric car?",
"dataTypeName": "RADIOBUTTON",
"fields": null,
"values": [
{
"value": "No"
}
]
},
{
"fieldName": "CarLocation",
"label": "Where is the Vendor located",
"dataTypeName": "SINGLESELECT",
"fields": null,
"values": [
{
"value": "123 NY Park NY City USA"
}
]
}
]
},
{
"ProductCode": "P - 2",
"ProductType": "BUS",
"fields": [
{
"fieldName": "BusType",
"label": "Is this an electric Bus?",
"dataTypeName": "RADIOBUTTON",
"fields": null,
"values": [
{
"value": "No"
}
]
},
{
"fieldName": "BusLocation",
"label": "Where is the Vendor located",
"dataTypeName": "SINGLESELECT",
"fields": null,
"values": [
{
"value": "4444 NY Park NY City USA"
}
]
}
]
}
]
}

Answer 3

You have to use just ['fields'] instead of $['data'][0]['fields']

if it is like $['data'][0][name]['fields'] then use ['name']['fields']