ADF Copy Activity suppressing empty arrays from JSON to table

Question

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?

Answer 1

Hi @Alexandre Von Mühlen ,

Thank you for posting query in Microsoft Q&A Platform.

If we get users array with nested properties defined as null as shown below. Then, it will work as you expected. I tried it. Its working fine

 "Users": [  
        {  
         "AccessRight": null,  
         "UserPrincipalName": null,  
         "Identifier": null,  
         "PrincipalType": null  
       }  
     ]  

In our case, users property has only array without any properties in it("Users": []). Hence, Collection reference skipping that entire row.

Try to update your source json "users" array with nested properties in it with null values as shown in above code snippet and then try to copy data to SQL table. That way you no need to use second copy activity. You leverage Azure Functions to write code to get that job done. Please check below link as well for an idea
https://learn.microsoft.com/en-us/answers/questions/193423/adding-element-to-a-json-object-in-adf.html

Hope this will help. Thank you.

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

• Please accept an answer if correct. 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 email-notifications.