Walkthrough on how to dynamically parse JSON as object or array in logic apps.

While working as a consultant for an integration consultancy, we had a client who wanted to be able to ingest the client list data set from WorkflowMax into SQL. The client data set consists of basic client information (name, email etc) and a list of the client contacts, of which there might be one or more.

WorkflowMax exposes its data sets in XML, the process of getting the data set from WorkflowMax is quite trivial,

1. Get an access token,
2. Get the client list.
3. Store the result in blob storage.
4. Trigger an Azure Data Factory pipeline a. To convert the XML to JSON. b. Store th JSON file in blob storage. c. Within the pipeline, insert or upsert top level client details into the database. In order to handle the list of contacts, the outputed json file has to be processed. However for some reason, the azure data factory copy activity, produces an array of contacts when there are multiple contacts, but an object of contacts when there is just one contact.

{
    "_id": "64fafdab0148d179b4bad3d6",
    "index": 0,
    "guid": "a5a30726-560b-4548-9219-d3e579ff4df0",
    "name": "Joanna Stephenson",
    "company": "SYNKGEN",
    "contacts": [
    {
        "id": 0,
        "name": "Church Cleveland",
        "email": "churchcleveland@synkgen.com",
        "age": 37
    },
    {
        "id": 1,
        "name": "Tabitha Gentry",
        "email": "tabithagentry@synkgen.com",
        "age": 26
    }     
    ]    
}

or

{
    "_id": "64fafdabea7f6eecf0e80b44",
    "index": 1,
    "guid": "3f273e26-2dec-4a56-a139-760977630590",
    "name": "Lynnette Guerra",
    "company": "COMTOURS",    
    "contacts": {
    "id": 1,
    "name": "Marcie Spence",
    "email": "marciespence@comtours.com",
    "age": 40
    }
}  

The json file can be processed by a logic app, however there is no simple way to handle processing elements where the schema contains an object or an array for the same element.

A possible solution can be found at Dynamically Parse JSON as object or Array where the question was answered and a solution proposed.

Creating a logic app to process these records is not complicated, the following page is a walkthrough on how this can be done.

The idea is to read the json file and parse the json payload, for each client, process all the contacts. A solution is to check the length of the contacts element. If it is an array, the number of elements will be returned otherwise it is an object.

However, there is no simple way to implement this logic in logic app. The images below shows what happens if this process is implemented as is.

Contact List is an Array.

Contact List is an Object.

A better solution will be to use the scope widget as try, catch and finally blocks.

Try Scope

This contains the condition to check that the element is an array and if it is then compose an output with all the elements.

Catch Scope

This must be configured to run only if the try scope fails and it involves creating an array of the single element.

Finally Scope.

In the finally scope, the output from the previous steps have to be coalesced and the scope must be configured to run after the previous step is skipped or is successful.

The approach outlined works better than that proposed in the solution to the original question, since it can take advantage of for each loop concurrency.