Handling JSON files in Power Automate

Table of content

Handling JSON files in Power Automate
Array Filter Action in Power Automate
Select Action in Power Automate
Parse JSON in Power Automate

Handling JSON files in Power Automate

In this article, we'll understand the basics of JSON and how we are going to implement it in Power Automate.

JSON (JavaScript Object Notation) is a lightweight data interchange format. It is simple for humans to read and write. It is simple for machines to parse and generate.

Example: 
myJSON = '{"name":"John", "age": 31, "city":"New York"}';

myJSON is the object. The object is always enclosed in curly brackets {...}.
"name": "John" is called the property of the object.

Go to Microsoft Power Automate.
Create a Manually Triggered Flow. Click on the three dots on the right-hand side top corner.
Click on Peek code.
Here, is our JSON code.
"kind": "Button" is a property that has an object inside that is "schema": {....}
Now, click on the New step.
Select Initialize variable from actions. Name the object, choose type as Object and add value to it. The value should be in JSON or Array format. Select Peek code again.

As you can see our JSON code.
peek-code-object-initialise-var-power-automate

Here, "inputs" have an array of "variables".

 "inputs": {
        "variables": [....]
}

Inside the array of "variables", we have another object.

"variables": [
            {
                "name": "JSON_OBJ",
                "type": "object",
                "value": {....}
            }
]

Array Filter Action in Power Automate

Array Filter Action goes through an array and returns a new array with the elements that pass the filtering condition. We are going to create a separate or filtered array in which has "country": "Canada".

Below is the JSON object we are going to use for the Array Filter action.

[
    {"firstname":"Mike", "lastname":"Timber","age":"22","country":"Canada"},
    {"firstname":"Sarah", "lastname":"Johns","age":"34","country":"USA"},
    {"firstname":"James", "lastname":"Li","age":"23","country":"Cameron"},
    {"firstname":"Donald", "lastname":"Shaw","age":"12","country":"Canada"},
    {"firstname":"Nancy", "lastname":"Rowley","age":"33","country":"USA"},
    {"firstname":"David", "lastname":"Smith","age":"23","country":"UK"}
]

Creating a flow with Array Filter Action

Go to Microsoft Power Automate.
Create a Manually Triggered Flow. Click on the New step.
Select Compose (Data Operation) from actions. Add the JSON array input to Compose. Click on the New step.
Select the Initialize variable from actions. Name the variable, choose type as an Array and add value as Outputs from dynamic content. Click on the New step.
Select Filter array(Data Operation) from actions.
Add our array variable. For value, we choose from Expression in dynamic content. We use the item() function to get items from the array.
We add the element as 'country' to the filter. Click on OK.
We are supposed to filter an array in which property "country" is equal to Canada. Click on Save.
Save and Test the flow.
We have the new array which is filtered with property "country" like Canada.

Select Action in Power Automate

Select is a control that transforms a JSON array from one format to another format.
For example, we have a JSON object format like below:

[
    {"firstname":"Mike", "lastname":"Timber","age":"22","country":"Canada"},
    {"firstname":"Sarah", "lastname":"Johns","age":"34","country":"USA"},
    {"firstname":"James", "lastname":"Li","age":"23","country":"Cameron"},
    {"firstname":"Donald", "lastname":"Shaw","age":"12","country":"Canada"},
    {"firstname":"Nancy", "lastname":"Rowley","age":"33","country":"USA"},
    {"firstname":"David", "lastname":"Smith","age":"23","country":"UK"}
]

By using Select Action we can convert it to the below format:

[
  {"Name": "Mike Timber", "Year_Born": 1999},
  {"Name": "Sarah Johns","Year_Born": 1987},
  {"Name": "James Li","Year_Born": 1998},
  {"Name": "Donald Shaw","Year_Born": 2009},
  {"Name": "Nancy Rowley","Year_Born": 1988},
  {"Name": "David Smith","Year_Born": 1998}
]

Creating a flow with Select Action

Go to Microsoft Power Automate.
Create a Manually Triggered Flow. Click on the New step.
Select Compose (Data Operation) from actions. Add the JSON array input to Compose. Click on the New step.
Select the Initialize variable from actions. Name the variable, choose type as an Array and add value as Outputs from dynamic content. Click on the New step.
Select Select(Data Operation) from actions.
Add the My_ARRAY variable with new properties as Name and Year_Born.
As we want to combine the first and last names, we use the concat() function. To get elements from the array we use the item() function.
Here, is the expression that concat item first name and last name from the array.

concat(item()['firstname'], ' ', item()['lastname'])

We add this expression to the Name property from Expression (Add dynamic content). Click on OK.
concat_power_automate

As we want to show the birth year in Year_Born, we subtract age from the current year.
Following is the expression where we used the sub() function to subtract age from the current year 2021.
```
sub(2021, int(item()['age']))
```
We add this expression to the Year_Born property from Expression (Add dynamic content). Click on OK.
After this, click on Save and Run flow.
Here, is the Output in Select Action, where we have our new array.

Parse JSON in Power Automate

JSON receives a string that has a JSON object and converts it into the parts that expose the properties of the JSON object.
For example, we have a string as a JSON object in which Parse JSON will disassemble the string into parts.

{"firstname":"David", "lastname":"Smith","age":"23","country":"UK"}

Creating a flow to Parse JSON

Go to Microsoft Power Automate.
Create a Manually Triggered Flow. Click on the New step.
Select the Initialize variable from actions. Name the variable, choose type as a String and add value as a JSON object. Click on the New step.
Select Parse JSON(Data Operation) from actions.
We don't have a schema yet so, click on Generate from sample.
Enter our string. Click on Done.
We have generated Schema from a string sample. Choose our variable string from dynamic contents. Click on the New step.
Now, the Parse JSON knows the schema. So, the message that comes from the string variable will be disassembled into parts.
Select Compose(Data Operation) from actions. Click on Add dynamic content.
In the dynamic contents, you can see that Parse JSON has disassembled the message into parts or properties.
Add parts as inputs to Compose. Click on Save.
Click on Compose. Here, we have generated output from the string variable with the help of Parse JSON.

About Author :

I am Sharvari Raut, having sound knowledge and experience in technical writing. Currently, pursuing my B.Tech in Computer Science and Engineering.

Comment / Suggestion Section

Point our Mistakes and Post Your Suggestions