How to query JSON data using JSON notation with Power Automate❔❔❓

Image showing whenever there’s an output from any Power Automate action behind the scenes it will be JSON.

JSON stands for JavaScript Object Notation. JSON is a text-based format for encoding data in a human readable form. JSON stores information in an organised and easy-to-access way. 

JSON data is a very important part of web programming because it allows an easy mechanism for transporting data between programs and programming languages. Although Power Automate is a low-code application, having a good working knowledge of JSON and knowing how to create and use JSON data is very important when working with flows. Whenever there’s an output from any Power Automate action behind the scenes it will be JSON.

The purpose of this post is to illustrate through examples, some JSON notation that often confuses beginners. I’m assuming that you are already familiar with the basics of JSON, if not, see “Learn everything you need to know about JSON in 10 minutes” JSON Crash Course.

Why querying JSON can be confusing!

There are two common ways to access properties in JSON objects:

  • Dot Notation: object.name
  • Bracket Notation: object[‘name’]

New developers and flow builders (including myself) tended to stick with the method that was taught to us on courses or tutorials. We are generally taught these notations are more or less the same; it’s just a matter of personal preference. As neither notations are complicated we never needed to concern ourselves with the different notations.

But as you start to read posts on Power Automate and complete online courses or tutorials, one thing you notice is that you can use different JSON notations syntax for querying JSON data and sometimes these notations do make a difference to how your flows run.

Eventually the question is asked “which one should I use with Power Automate?“. I mostly use the bracket notation but read the examples below and decide for yourself.

Example 1: Initialize a variable of type object

I will create and use the following simple JSON object in examples below:

Initialize a variable of type object
JSON Objects are surrounded by curly braces “{ }” and are written in key/value pairs
JSON Objects are surrounded by curly braces “{ }” and are written in key/value pairs

I’ve deliberately made mass (which should really be a number) a string just to show that sometimes the data you get may not be in right format you need it to be, so that means you will need to convert it to another data type.

Here is the runtime output of the Initialize a variable action:

the runtime output of the Initialize a variable action

Example 2: Accessing JSON properties

To access the JSON properties (or key/value pairs) we can use the dot or bracket notation. There is also a variation of the bracket notation that uses a question mark which I’ll explain later:

variables('varData').name       ➡️ "C-3PO"
variables('varData')['name']    ➡️ "C-3PO"
variables('varData')?['name']   ➡️ "C-3PO"

variables('varData').height     ➡️ 167
variables('varData')['height']  ➡️ 167
variables('varData')?['height'] ➡️ 167

variables('varData').mass       ➡️ "75"
variables('varData')['mass']    ➡️ "75"
variables('varData')?['mass']   ➡️ "75"

variables('varData').faulty     ➡️ false
variables('varData')['faulty']  ➡️ false
variables('varData')?['faulty'] ➡️ false

The dot notation is a lot easier to read and faster to type.

Example 3What happens when you try to access a missing property?

In the JSON object below the name property (key) has been removed and is now missing, there’s no C-3P0!:

JSON object initialised but with the name property (key) removed

If you try and access the missing property using the dot or bracket notation the flow stops with an error.

variables('varData').name   ➡️ ⛔Flow stops with an Error ❌
variables('varData')['name']➡️ ⛔Flow stops with an Error ❌

So when you reference a property that is missing from the JSON data (object) the flow stops with an error:

when you reference a property that is missing from the JSON data (object) the flow stops with an error:

Although the error message is useful in testing and debugging your flows, you don’t want your business-critical flows to stop once you move them or use them in your live environment (the production environment). In the examples below we’ll use the bracket notation with a question mark to stop the flow from generating an error and stopping at this point.

Example 4What happens when you try accessing a missing property using the ?[bracket] notation with a question mark?

If you reference a property that is missing from the JSON object using the bracket notation with a question mark (query method), the flow does not stop with an error:

variables('varData')?['name']  ➡️ ✅Flow continues running
the flow does not stop will an error: when using the bracket notation with a question mark
The query method – using the bracket notation with a question mark

The use of the question mark in JSON specifies that if the property being referenced does not exist then a null value will be returned instead of throwing an error. The flow does not produce an error and the flow continues. Your flow may need some logic to handle the empty value (e.g. if it’s a value like age or currency, how should your flow handle this null value?).

variables('varData').name     ➡️ ❌Flow stops with an Error
variables('varData')['name']  ➡️ ❌Flow stops with an Error
variables('varData')?['name'] ➡️ ✅Flow continues running

Example 5: Handling NULL or Empty values

You can use a condition action that checks if the query variables(‘varData’)?[‘name’] results in a null value. The null value in the condition is entered as an expression:

The null value in the condition is entered as an expression

You can use a the null value in an expression to check if the query variables(‘varData’)?[‘name’] results in a null or empty value:

if(equals(variables('varData')?['name'], null), 'Name was empty', 'Name was not empty')
if(equals(variables('varData')?['name'], null), 'Name was empty', variables('varData')?['name'])

You can also use the empty function to shorten the above expression to:

if(empty(variables('varData')?['name']), 'Name was empty', variables('varData')?['name'])

Although technically not a value type, null is a special value in JSON. When there is no value to assign to a key, it can be treated as null.

Example 6: What about JSON Arrays?

The example JSON array below has three array items (or elements):

✅ Here are some examples using the bracket query notation with a JSON array:

variables('varArray')?[0] ➡️ {"fruit": "Apple", "quantity": 10}
variables('varArray')?[1] ➡️ {"fruit": "Mango", "quantity": 7 }
variables('varArray')?[2] ➡️ {"fruit": "Pear",  "quantity": 3 }

✅ Accessing the properties of each item in the JSON array:

variables('varArray')?[0]?['fruit']    ➡️ "Apple"
variables('varArray')?[0]?['quantity'] ➡️ 10

variables('varArray')?[1]?['fruit']    ➡️ "Mango"
variables('varArray')?[1]?['quantity'] ➡️ 7

variables('varArray')?[2]?['fruit']    ➡️ "Pear"
variables('varArray')?[2]?['quantity'] ➡️ 3

✅ Accessing a missing property of an array item we get null:

variables('varArray')?[0]?['colour']        ➡️ null
empty(variables('varArray')?[0]?['colour']) ➡️ true

✅ Accessing a missing or non existent item of an array:

variables('varArray')?[11]           ➡️ null
empty(variables('varArray')?[11])    ➡️ true

⛔ A Final Note: Sometimes you need your flow to stop with an Error!

So most of the time in your flows you would use the bracket notation with a question mark ‘?’.

However, there are times when using another notation style can be very useful in testing and debugging your flows. You may want to generate an error and stop the flow so that you can identify the root cause of the error and correct it. You may have mistyped a property or the property may be missing and you are seeing empty data values in your flow. 

The property colour (British English) was mistyped as color (US English). By stopping the flow run can help you identify the root cause of the error.

Hope this helps!


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Built with WordPress.com.

Up ↑

%d bloggers like this: