JSON Pivoting in Power Automate

JSON Pivoting in Power Automate

Transforming objects in Power Automate is mostly a challenge. Here is an example for you. Let’s imagine, I want to convert a JSON array of key-value pairs into an object in my Power Automate Flow. Correct, I want some kind of pivoting.

In detail, here is my JSON array which I want to pivot:

[
  {
    "name": "title",
    "value": "The Great Gatsby"
  },
  {
    "name": "author",
    "value": "F. Scott Fitzgerald"
  },
  {
    "name": "year",
    "value": 1925
  },
  {
    "name": "genre",
    "value": "Fiction"
  }
]

The result should be a single object:

{
  "title": "The Great Gatsby",
  "author": "F. Scott Fitzgerald",
  "year": 1925,
  "genre": "Fiction"
}

Ok, let’s start.

JSON Array pivot into an Object as Power Automate Flow

First, I’m creating a new Power Automate Flow. As you see, I usually use a manual trigger for such a demo flow. Furthermore, I add a Parse JSON data operation as action. I copy my array into the Content field and initialize a simplified Schema:

For my result I add also an Initialize Variable action to my Flow. For the property Type i use Object and for my initial Value I use {} which is an empty object:

Perfect, my prerequisites are done. In other words, my array and my result variable are in place.

Now I can loop over my array with an Apply to each action:

Inside of my loop, I’m adding another Compose action. This is because I want to store temporary a modified version of my variable. In detail, I use the addProperty expression to add a property and its value to my object. My expression is this:

addProperty(variables('result'), items('Apply_to_each')?['name'], items('Apply_to_each')?['value'])

Afterwards, I update my variable with the new value. In other words, I have pivoted my key-value pair into a property of my object.

Note: The expression addProperty might fail, when the property already exist.

My Apply to each loop is now this:

Finally, I output my variable into another Compose action at the end of my Power Automate Flow:

Let’s test my Flow. Yes, you see everything worked as expected. This means I have successfully converted my array into an object:

Summary

To sum up, my solution transforms a JSON array of key-value pairs as pivot into an object in Power Automate. I accomplish this by using a loop to iterate over the key-value pairs, and within the loop I use the “addProperty” expression to store the value as a new property with the given name in the resulting object.

Share
Comments are closed.