Power Automate Parse JSON Action Guide | When & How To Use

The Power Automate Parse JSON Action simplifies the processing of JSON data within your flow.

Within this article you will find an example step by step guide how to parse JSON data from an HTTP request. The need to parse JSON coming from a Rest API is common.

You will get an short introduction to the format JSON and to JSON schemas.

Beside learning how to parse JSON data in Power Automate, you will also learn when to avoid Parse JSON action.

What does Parse JSON do in Power Automate?

The JSON data format is omnipresent when you are working with Microsoft Flows. The return value of your HTTP call against a Rest API, items of a SharePoint, CSV data and many more are in JSON format.

The Parse JSON action needs two information to work:

1. JSON data
2. JSON schema

When you provide both you will be able to easily reference data from the JSON data within your flow.

So the Parse JSON action makes the data easier to access and to process.

Let’s dive into a real world example!

How To Use Parse JSON in Power Automate by Example

JSON Basics

JSON (JavaScript Object Notation) is a lightweight data-interchange format. If you already know XML, you can quickly undertsand what JSON is. It is basically a slim version of XML. I do not want to deep dive into JSON, but just provide the essential basics.

JSON Example

Imagine we have a table of persons:

name	age	gender
John Smith	23	male
Sally Smith	47

The table data can be described in JSON this way:

[
  {
    "name": "John Doe",
    "age": 23,
    "gender": "male"
  },
  {
    "name": "Sally Smith",
    "age": 47
  }
]

As you might have noticed by looking at the example:

• Collections are marked by square brackets: [ {person 1}, {person 2}, … ]
• Items within a collection are separated via commas: [ {person 1}, {person 2}, … ]
• Objects are always in curly brackets: { “name” : “John Doe” }
• Column names are always in quotation marks: { “name“ : “John Doe” }
• Text values are always in quotation marks: { “name” : “John Doe“ }
• Number values do not need quotation marks: { “age” :23}
• Properties of objects are operated by commas: { “name”: “John Doe”,“age” :23}

From my point of view, these are the most important concepts to understand.

JSON Schema Example

The Microsoft Power Automate Parse JSON Action needs a schema, but what is a schema. A JSON Schema describes the structure of the data. The schema itself is defined in JSON.

The Power Automate Parse JSON action let’s you generate a schema for your JSON by providing example JSON data.

So for our data structure, the schema looks like this:

{
    "type": "array",
    "items": {
        "type": "object",
        "properties": {
            "name": {
                "type": "string"
            },
            "age": {
                "type": "integer"
            },
            "gender": {
                "type": "string"
            }
        },
        "required": [
            "name",
            "age"
        ]
    }
}

So this way you can get easily a JSON schema.

But be careful, there are some risks to this approach!

The schema generator can only make sense of the given data. For instance the generator does list gender under required. The generator noticed that the second record has no gender, so the field must be optional.

If you create the schema based on a collection containing only the first record where gender is set, gender would be required in the schema. This would lead to ‘ValidationFailed. The schema validation failed.’, when you parse a record without gender.

You can easily fix this by adjusting your schema manually, but please be aware that your sample data might be not detailed enough to let the schema generator work perfectly.

Note, that the case of required properties missing from object is just one situation where the validation might fail.

In case you have a more complex data structure you should not rely on the schema generator.

When to avoid Power Automate Parse JSON and what to do instead

In case you have no control over a complex data structure, it might be better to not use the Parse JSON action.

You can extract the needed data of JSON data structure by just using expressions. This way you only rely on the data needed by your flow. You formulate no other expectations on the data you use.

This way your flow be less fragile in case the data changes. On the negative side the writing of expressions is more complex than using the Parse JSON action.

In the picture below you see, how to access the usd value of a bitcoin from our example at the start of this article. This way you need no Parse JSON action. You simply parse the needed JSON to a variable.

Even though our example is quite simple, you can already imagine how complex the expressions can get, when you have deeply nested data structure.

float(body('HTTP')['bitcoin']['usd'])

Json Function

The json function converts a string or XML to JSON.

Syntax

json( value )

Parameters

• value (mandatory): String or XML that should be converted to JSON.

Return value

• The JSON for the given value.

Parse XML to JSON Example

Use the Power Automate json function to convert XML to JSON.

This is how you convert XML to JSON with Power Automate:

1. Convert the XML string to XML: xml(‘<root><name>Joe</name></root>’)
2. Convert the XML of step 1 to json: json(xml(‘<root><name>Joe</name></root>’) )

See the example on how to parse XML to JSON with Power Automate below:

json(xml(outputs('Compose_XML')))

Parse JSON arrays

You might wonder, how to parse a JSON array and iterate over all items returned. Actually it is quite simple as you can see in the picture below:

Parse JSON null values

To mark null values in JSON the keyword ‘null’ is used. The Parse JSON action might fail, when you defined a property for instnce of type string and null is provided.

To handle these situations just adjust your schema like shown below:

"name" : {
  "type" : ["string","null"]
}

This way null values are allowed too.