How to Query JSON Data Using JSON Notation with Power Automate❔❔❓

This post was Last Updated:

This post illustrates through examples JSON notation that often confuses beginners. You will learn how to read JSON values and stop your flows from throwing an error!

CONTENTS

1. Why you need to know (some) JSON!
2. Why querying JSON can be confusing!
3. Example 1: JSON object, properties, keys and values
4. Example 2: Initialize a variable of type Object
5. Example 3: Accessing JSON properties
6. Example 4: What happens when you try to access a missing property?
7. Example 5: What happens when you try accessing a missing property using the [BRACKET] notation with a QUESTION mark?
8. Example 6: Handling NULL or Empty values
9. Example 7: What about JSON Arrays?
10. ⛔ A Final Note: Sometimes you NEED your flow to stop with an Error!

Why you need to know (some) 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, JSON notation that often confuses beginners. You will learn how to read JSON values and stop your flows from throwing an error!

I’ll introduce the basics of JSON, but I’m assuming that you some familiarity with 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: JSON object, properties, keys and values

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

Example 2: Initialize a variable of type Object

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:

Example 3: 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. Note that JSON key (column) names are case sensitive, so [‘name’] and [‘Name’] are different key names.

Example 4: What 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!:

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:

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 prevent the flow from generating an error and stopping at this point.

Example 5: What 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 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 6: 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:

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')

And in this example, if the expression evaluates to null we return the text ‘Name was empty’. But if the expression does not evaluate to null we return the value of name property:

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 7: 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 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 name or the property may be missing and you are seeing empty data values in your flow. 

Hope this helps!

---