Hi,
I'm using ADF to get some JSON, then I'm using a Copy Activity to transfer these information to a table on a SQL Database.
Some lines have nested JSON arrays, that have to result in multiple lines in the table. For this, I'm using the "Collection reference" to execute a Cross Apply for every row, as we can see:
Here you can see a sample of the JSON:
[
{
"Id": "65fda560-......",
"Name": "MAX",
"IsReadOnly": false,
"IsOnDedicatedCapacity": true,
"CapacityId": "91B5D117-......",
"Description": null,
"Type": "Workspace",
"State": "Active",
"IsOrphaned": false,
"Users": [
{
"AccessRight": "Admin",
"UserPrincipalName": "10396864@......",
"Identifier": "10396864@......",
"PrincipalType": 2
},
{
"AccessRight": "Member",
"UserPrincipalName": "10450693@......",
"Identifier": "10450693@......",
"PrincipalType": 2
},
{
"AccessRight": "Viewer",
"UserPrincipalName": "10652184@......",
"Identifier": "10652184@......",
"PrincipalType": 2
}
],
},
{
"Id": "371edcdc-......",
"Name": "Adm......",
"IsReadOnly": false,
"IsOnDedicatedCapacity": true,
"CapacityId": "91B5D117-......",
"Description": null,
"Type": "Workspace",
"State": "Deleted",
"IsOrphaned": false,
"Users": []
}
]
The second one has a empty Users ("Users": []) array. I was expecting that this empty array would result on columns with no data (NULL), BUT the entire line is skipped!!
For this moment, I'm using a second Copy Activity, to access the same resource and then I leave the Collection empty. This way I have to "distinct" the information duplicated on the destination. Very gross solution.
Any suggestion?