question

AlexandreVonMhlen-9519 avatar image
0 Votes"
AlexandreVonMhlen-9519 asked ShaikMaheer-MSFT commented

ADF Copy Activity suppressing empty arrays from JSON to table

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:
108998-image.png

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?


azure-data-factory
image.png (42.8 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

ShaikMaheer-MSFT avatar image
0 Votes"
ShaikMaheer-MSFT answered ShaikMaheer-MSFT commented

Hi @AlexandreVonMhlen-9519 ,

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://docs.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.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @ShaikMaheer-MSFT .

Actually I would like to have a option to choose between an CROSS APPLY (option that is currently used) and an OUTER APPLY.

Well, I changed my solution to bypass this limitation.


Thank you by your time.

0 Votes 0 ·

Hi @AlexandreVonMhlen-9519 ,

Thank you for accepting answer.

0 Votes 0 ·