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
Power Automate – Parse JSON action

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

Reference values from JSON data

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

This is a simple example on how to use Power Automate Parse JSON action.

We will call the API of CoinGecko to get the current US dollar value of one Bitcoin. We will sent an email to us, when the value is below $20.000.

  1. Create a new Instant cloud flow

    Power Automate Create Instant Cloud Flow

    Create Instant cloud flow

  2. Name your flow, select Manually trigger a flow and click ‘Create’

    Power Automate Create Flow

    Create flow with manual trigger

  3. Add an HTTP action (Note: The HTTP action is premium )

    Power Automate HTTP Action

    Add HTTP action

  4. Configure HTTP action: Set method to ‘GET’ and URI to ‘https://api.coingecko.com/api/v3/simple/price?ids=bitcoin&vs_currencies=usd

    Power Automate HTTP Action Configuration

    Configure HTTP action

  5. Add a Parse JSON action

    Parse JSON action

  6. Set Parse JSON Content to Body of HTTP action by clicking in Content text field and clicking on body

    Power Automate Parse JSON Content

    Parse JSON Content

  7. Click Parse JSON ‘Generate from sample’ button

    Power Automate Parse JSON Schema Generate from sample button

    Parse JSON generate Schema

  8. Paste sample data into the text area

    Power Automate Parse JSON Schema Insert a sample JSON Payload

    Insert sample data for JSON schema generation

  9. Add a condition action

    Power Automate Add Condition Action

    Condition action

  10. Click on the left value text input and click on ‘usd’ on the right

    Power Automate Condition Setup

    Condition Value

  11. Select ‘is less than’ as condition and set the right text field to ‘20000’

    Power Automate Condition Setup 2

    Condition definition

  12. Add a ‘Send an email (V2)’ action in the ‘If yes’ branch

    Power Automate Send an email

    Add send an email (V2)

  13. Set To,Subject and Body as you like

    Power Automate Send an email setup

    Send an email (V2) setup

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:

nameagegender
John Smith23male
Sally Smith47
Persons table

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.

Parse JSON – Generate from sample
Parse JSON – Insert a JSON payload
Parse JSON generated schema

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.

Parse JSON – ValidationFailed. The schema validation failed.

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.

Access JSON data with an expression without Parse JSON

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:

Parse XML to JSON
json(xml(outputs('Compose_XML')))
Result of converting XML to JSON

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 array

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.

Parse JSON null values

Leave a Comment

Your email address will not be published.