Azure data factory copy activity cosmos db error

Question

Hello

I'm getting the following mistake when I try to make a mapping of an array on copy data activity from csv file to cosmos db.
The structure on cosmos db for the item should be as follows:

"InsuredItems": [
{
"Accomodation": {
"TypeOfAccomodation": "nothing",
"AccommodationAddress": {
"StreetAddress": "something",
"PostalCode": "08036",
"City": "BAAAA",
"State": "CAAAAA",
"Country": "DHJSDHJSHD"
}
},
"HouseholdAppliance": {
"Type": "1",
"Brand": "2",
"Model": "3",
"PurchaseDate": "2020-07-06T19:07:05.418Z",
"PurchaseValue": "434",
"PurchaseCurrency": "DOLLARS"
}
}
],

i¡M TRYING TO DO THIS -->
{
"source": {
"name": "HeatingType",
"type": "String"
},
"sink": {
"path": "$['InsuredItems'][0]['HouseholdAppliance']['Type']"
}
},

I'm getting this mistake, how could I on copy activity to cosmos db define an array even if always i will have one item?? this is because the json format structure is defined in this way, I don't know what to do, this is making me crazy please help me.

JSON path "$['InsuredItems'][0]['HouseholdAppliance']['Type']" is not correct. Array accessor like [0] is not supported in the schema mapping sink.

Answer 1

Hello
Sorry until today I can check your response, unfortunately I cannot just remove the array accesor, the way that the information is gonna be retrieve(from an APIi requires to have the data defined with tha model --> complex object with arrays inside and maybe another complex object inside the array.

However prior to your response I've tryed to implemeent another solution ->I've create a Json file that allows to me to manipuaate the format and then I've create a pipeline connected with my main pipeline to load that Json file within cosmos DB....the solution seems to work properly

Thanks again and off course I'm going to ask to microsoft to support array accessor in schema mapping sink of Copy activity.

Answer 2

Hi @CORONADO GRANADOS Diana Milena ,

Thank you for your query. Unfortunately array accessor is not supported in schema mapping sink of copy activity, which is why you are seeing the error message.

If you have any flexibility with your sink JSON structure, you could try removing the array accessor [0]. For example if your csv input has data as below:

TypeOfAccomodation,StreetAddress,PostalCode,Type,PurchaseValue  
nothing,something,08036,34,8999  

Then have your mapping as below:

"mappings": [  
							{  
								"source": {  
									"name": "DDTypeOfAccomodation",  
									"type": "String"  
								},  
								"sink": {  
									"path": "$['InsuredItems']['Accomodation']['TypeOfAccomodation']"  
								}  
							},  
							{  
								"source": {  
									"name": "DDStreetAddress",  
									"type": "String"  
								},  
								"sink": {  
									"path": "$['InsuredItems']['Accomodation']['AccommodationAddress']['StreetAddress']"  
								}  
							},  
							{  
								"source": {  
									"name": "DDPostalCode",  
									"type": "String"  
								},  
								"sink": {  
									"path": "$['InsuredItems']['Accomodation']['AccommodationAddress']['PostalCode']"  
								}  
							},  
							{  
								"source": {  
									"name": "DDType",  
									"type": "String"  
								},  
								"sink": {  
									"path": "$['InsuredItems']['HouseholdAppliance']['Type']"  
								}  
							},  
							{  
								"source": {  
									"name": "DDPurchaseValue",  
									"type": "String"  
								},  
								"sink": {  
									"path": "$['InsuredItems']['HouseholdAppliance']['PurchaseValue']"  
								}  
							}  
						]  

This will output as below JSON structure:

{  
	"InsuredItems": {  
		"Accomodation": {  
			"AccommodationAddress": {  
				"StreetAddress": "something",  
				"PostalCode": "08036"  
			},  
			"TypeOfAccomodation": "nothing"  
		},  
		"HouseholdAppliance": {  
			"Type": "34",  
			"PurchaseValue": "8999"  
		}  
	}  
}  

Let us know if the above solution helps. And also I would recommend you to please provide a feedback in ADF user voice forum for a feature request to support array accessor in schema mapping sink of Copy activity.

ADF user voice forum: https://feedback.azure.com/forums/270578-azure-data-factory

----------

Thank you