Converting JSON with nested arrays into CSV in Azure Logic Apps by using Array Variable

Recently our company needed to rebuild the internal BI solution.The change was caused by an upgrade of some external APIs that our BI solution uses to collect timesheet data. In the new solution we use Azure Logic Apps to call Timesheet APIs and it returns all required timesheet data in form of JSON objects. One of the problems that we encountered during the work, was how to convert the JSON response objects that the API returns into delimited flat files. The solution that we found is trivial, but not immediately obvious for the person with little experience in Azure Logic Apps, so that’s why I decided to share it with you.

This blog is going to explain how we can easily convert complex JSON objects into a flat CSV Table by using Azure Logic App’s. This blog is not going to cover how to use Logic Apps from scratch. I will assume that you have basic knowledge about how this service work, and you can create simple logic flows yourself. I am going to focus more on what components we should link together, and how to solve the problem of flattening JSON objects, rather than on technical details how particular logic components works.

If you think that you would require some lectures before, please visit Azure Logic App Documentation website.

To start with we would need an example of the JSON object that we will try to convert to CSV table.

So lets dive in!

The picture below shows a JSON object that we want to convert to a CSV table. The snippet below shows only the first element, but in reality this JSON array would be composed of multiple objects like this. You can find the download link to the full object in the download section on the bottom of this blog.

We can decompose the object from above picture as shown below:

Please note that everything in red is only for annotation.

This object is constructed of multiple levels

• (Level 0) The root of the object is an Array of Project objects (Each Project object in the Array of Projects Objects have two properties):

  • (Level 1-Project level properties) Project property that describes project name
  • (Level 1-Project level properties) AssignedEmployees array that are assigned to the project (Each Employee in AssignedEmployees array contain also two properties):
    • (Level 2-Employee level properties) EmployeeName that contains Name of the employee
    • (Level 2-Employee level properties) Rates array that stores information about rate for that particular employee on that project (Each Rate in Rates array contain two properties):
      • >(Level 3-Rate level properties) RateAmount
      • >(Level 3-Rate level properties) RateEffectiveDate
        

Lets focus for a moment on the Logic app side and try to present the components that we would use for the  CSV conversion.

Logic apps have a Create CSV Table action component that can produce a CSV table based on the JSON array of an object (see screenshot below)

Looking at the Text box above, if we pass JSON object reference into “FROM” to above component the output from that component will be a CSV Table, but there are some serious limitations.

This component expects a JSON array of simple objects. This means that each object of the array should be composed only from single level of properties, like on the screenshot below:

Complex objects are not supported at present, and if we provide an array of objects which we had in our example at the start, everything below “level 1”  i.e. the Employees and Rate information will be treated as a single string, like in the “Outputs” of the “Create CSV table” component on the picture below:

The solution is to “flatten” our complex object (multi level) into a flat object (single level) and pass it into the CSV Table component.

The rest of the blog is going to be focussed on how to convert the complex object into simple JSON so we can leverage the CSV Table component to create JSON.

Solution

As we have just discussed above, in order to convert the JSON object into a CSV we would need to flatten the JSON object first.

In order to flatten this object, we use the combination of a few Azure logic app components:

Our solution is composed of the components listed below:

1. Http trigger (“When a HTTP request is received”) – this will run when the http uri is called (It is not relevant to the solution, we can use any type of trigger).
   
   
2. Compose action component – this creates an example array of JSON Project Objects. In a real world example, that would come as a part of the HTTP request, Service bus message, or any other way we can read the JSON data in logic apps. The picture below shows only part of the whole object. You can view the whole object by downloading the example of the file in the Download section at the bottom of this blog.
   
   

    

3. Parse JSON action component- this component parses the JSON array and translates it into Logic Apps Objects, so we can access objects and properties  within our Logic app flow. To generate the validation and parsing schema, we use the option “Use sample payload to generate schema” (at the bottom of the component) and paste an example of the JSON that our application will take. In our case this is going to be the example from the component from the picture above.
   
   
   
4. Initialize Variable action component – This component is going to be our temporary storage for newly created flattened objects. We name it Rates Array and It need to be of type Array. We do not require to set any initial value as we are going to populate this array in further steps. Please do not confuse Rates Array with Array of rates from Initial Json. This is a variable to hold newly created objects.
   
   
   

    

5. Use Nested For each Loop flow component (For each Project, For each Employee, For Each Rate) in order to iterate through all levels of our object.
   
   
6. Compose action component  and Append to Array component – On the lowest level of the array (inside Rate loop) we create a new flatten Rate JSON Object with “Compose” type component (This object contains properties from all levels – Project Level, Employee Level, Rate Level).
   Next we use Append to Array component and paste the newly created Rate object like on the picture below
   
   
   
7. Create CSV Table component – This component will perform all the heavy lifting for us. It gets the already populated Array of Rate objects as a parameter (From) and converts it into CSV table. Note that RatesArray contains the already flattened rate object. Each property of that object will produce one column in the CSV table.
   
   
   
   We can use +New Step on the picture above to create additional steps if we would like. For example, save the created CSV into blob storage or data lake.
   
   
   The full logic app will look like on the picture below
   
   
   Now when we have everything for the logic app in place, lets see the result of the execution of this logic app
    
   
   
   As we can see in the Output section, the conversion resulted a CSV table being created successfully for our example of the Array of Complex project objects. Please note that the input section of above picture. It takes flattened input objects that we created in the Rates Array variable.
   
   

Conclusion

Logic apps provide a simple way of converting an array of JSON objects into a CSV Table. The  advantage of using logic apps for this type of conversion is that there are inbuilt actions components to perform the task. Moreover, we have different types of triggers available that we can configure this web service with, and chose different varieties of connectors for our input and output data.

There are certain constraints and drawbacks to consider before we use this in our workflow.

This method is not the most efficient as we nest “for each” in another “for each” etc. The more nesting the worse performance. In addition, Logic apps have execution time limits. E.g. HTTP Request trigger which we used, is limited to 120s of executions, therefore we shouldn’t use this method to convert JSON arrays composed of millions of objects.

If we need to convert very large files or files with a very complicated structure it would be probably be more suitable to use other types of services like e.g. Azure Functions instead and perform all the heavy lifting in c#.

Example Code

If you want to see above working solution in action you will need to:

1. Get Azure Subscription (Free trial if you do not own one)
2. Create Empty Logic app (Empty template) with name of your choice
3. Download logic app working Azure Logic App JSON code from the download section
4. Open code view and replace empty template with the copied code
5. You can run the trigger manually or by calling Logic App http trigger address in browser or by using Postman HTTP GET request to the logic app http address which is specific to your application. Request do not require any body parameters or additional headers.

Download

1. Click here to download Logic App JSON code
2. Click here to download complex JSON example
3. Click here to download flatten complex JSON example