JSON: Objects And Arrays

Objects And Arrays

What denotes a JSON array and what constitutes a single object?

Why when I create an ‘Add a new row’ to Dataverse action does Power Automate sometimes automatically create an ‘Apply to each’ loop, even though I only have one object?

These are frequently asked questions! When working with JSON it’s important to be clear on what you are dealing with. Arrays and objects are the topic of today’s blog.

Simplifying A JSON

The format of a JSON returned by an API is typically a lot more complex than just the data you are interested in. I’ve previously dicussed how to remove the unwanted content from a JSON so you can de-clutter and hone in on just the content that you require. Here are the links on how to do this:

• Flattening a Nested JSON With Power Automate
• Simplify JSON Content Before Parsing

Using the above techniques, you can simplify a JSON to make it much easier to work with. Once you have done this, you will typically be left with a single object or an array of objects.

An Object

Here is a single object consisting of 6 key/value pairs. It is not an array because it is not encapsulated in square brackets.

I can parse the above object and add the output to dataverse. As it is a single object an ‘Apply to each’ loop isn’t required.

An Array With A Single Object

Below is a single object as part of an array. The array is denoted by the square brackets.

[
   {
      "[Registration Number]": 12345,
      "[Company Name]": "ABC Ltd",
      "[Registration Year]": 2020,
      "[Company Status]": "Active",
      "[Company Address]": "123 Business St, City",
      "[Registered Address]": "456 Office Rd, City"
   }
]

In this case, when you parse and use the ‘Add a new row’ to Dataverse action, Power Automate will recognise that you are referencing an array and so it automatically adds an ‘Apply to each’ loop. The fact the array may contain only one object doesn’t matter. Power Automate recognises an array and automatically allows for multiple objects.

Apart from it being mildly irritating when you know your array will only ever contain one object, there is no issue with the ‘Apply to each’ loop being created. The flow will run just fine.

Converting An Array To An Object

To extract the object from the above array use this technique. Assuming the array is held in a Compose action, to extract the object create a second Compose action containing the expression below:

first(outputs('Compose'))

This removes the square brackets and returns the object:

{
   "[Registration Number]": 12345,
   "[Company Name]": "ABC Ltd",
   "[Registration Year]": 2020,
   "[Company Status]": "Active",
   "[Company Address]": "123 Business St, City",
   "[Registered Address]": "456 Office Rd, City"
}

This can now be parsed and added to Dataverse without an ‘Apply to each’ loop.

An Array Containing Multiple Object

Below is an array containing multiple objects. To parse and add each of these objects to Dataverse does require an ‘Apply to each’ loop to scroll through each of the objects in turn.

[
   {
      "[Registration Number]": 12345,
      "[Company Name]": "ABC Ltd",
      "[Registration Year]": 2020,
      "[Company Status]": "Active",
      "[Company Address]": "123 Business St, City",
      "[Registered Address]": "456 Office Rd, City"
   },
   {
      "[Registration Number]": 12346,
      "[Company Name]": "DEF Ltd",
      "[Registration Year]": 2020,
      "[Company Status]": "Active",
      "[Company Address]": "111 Business St, City",
      "[Registered Address]": "456 Office Rd, City"
   }, 
   {
      "[Registration Number]": 12347,
      "[Company Name]": "XYZ Ltd",
      "[Registration Year]": 2020,
      "[Company Status]": "Active",
      "[Company Address]": "222 Business St, City",
      "[Registered Address]": "456 Office Rd, City"
   }
]

Because the above array contains multiple objects, I cannot collectively extract all the objects from the array. If I remove the square brackets, the JSON format becomes invalid.

I can extract individual objects. The expressions below will extract the first, second and third objects respectively

First(outputs('Compose')) / outputs('Compose')[0]
outputs('Compose')[1]
last(outputs('Compose')) / outputs('Compose')[2]

What is Parsing?

Before we close, let’s clarify exactly what happens when the Parse JSON action is executed. Parsing is the process of analysing and converting JSON data into a structured format that Power Automate can easily reference.

The Parse JSON action involves specifying a schema that defines the structure against which the JSON is processed. Parsing generates dynamic content that is easily readable and selectable in actions used in the rest of the flow. It is not mandatory to parse JSON files, but it is highly recommended. The JSON mustbe able to be processed against the schema in the Parse JSON action. If it cannot then the action will fail.

Summary

In this short post I recapped on the definition of objects, arrays and parsing, in terms of Power Automate

Feel free to add any thoughts comments, or JSON manipulation or parsing tips of your own.