Extracting keys of nested json in PowerAutomate

Question

I'm trying to use Microsoft Power Automate to extract the values for the property "name" from the JSON object shown below:

{"sites": {
            "1": {
                "id": "1",
                "name": "Staff"
            },
            "7": {
                "id": "7",
                "name": "Children"
            },
            "10": {
                "id": "10",
                "name": "Visitors"
            }
}

such that I end up with an array ["Staff","Children","Visitors"], or a list of keys/IDs ["1","7","10"] would also be acceptable. Above is just a simple example. In reality the number of nested objects can vary and the integer values for the keys range between, say, 1-30.

I've seen several blog posts talking about converting the JSON to XML to extract the nodes, though as a Power Automate beginner I've struggled to implement this.

Would someone be able to suggest a simple straight-forward way to extract this information in Power Automate?

Thanks.

Answer 1

If you don't want to use the XML approach then you can use the Advanced Data Operations connector and the Json Properties to Name Value Pair Array operation.

It's a very versatile connector that allows you to perform all sorts of data manipulation. It has a free trial available to it as well if that helps.

https://statesolutions.com.au/json-properties-to-name-value-pair-array/

https://statesolutions.com.au/pricing/

Let me run you through the steps taken in the image above ...

Compose Data

That contains the data you provided.

{
  "sites": {
    "1": {
      "id": "1",
      "name": "Staff"
    },
    "7": {
      "id": "7",
      "name": "Children"
    },
    "10": {
      "id": "10",
      "name": "Visitors"
    }
  }
}

Json Properties to Name Value Pair Array

This does the heavy lifting to break out the properties into a structure that you can loop over and all it takes in is the output from the previous step (i.e. your data). This is the result ...

[
  {
    "propertyName": "sites",
    "propertyType": "Object",
    "propertyValue": [
      {
        "propertyName": "1",
        "propertyType": "Object",
        "propertyValue": [
          {
            "propertyName": "id",
            "propertyType": "String",
            "propertyValue": "1"
          },
          {
            "propertyName": "name",
            "propertyType": "String",
            "propertyValue": "Staff"
          }
        ]
      },
      {
        "propertyName": "7",
        "propertyType": "Object",
        "propertyValue": [
          {
            "propertyName": "id",
            "propertyType": "String",
            "propertyValue": "7"
          },
          {
            "propertyName": "name",
            "propertyType": "String",
            "propertyValue": "Children"
          }
        ]
      },
      {
        "propertyName": "10",
        "propertyType": "Object",
        "propertyValue": [
          {
            "propertyName": "id",
            "propertyType": "String",
            "propertyValue": "10"
          },
          {
            "propertyName": "name",
            "propertyType": "String",
            "propertyValue": "Visitors"
          }
        ]
      }
    ]
  }
]

For Each Sub Object

This is just a For each action that is configured with this expression ...

first(body('Json_Properties_to_Name_Value_Pair_Array'))['propertyValue']

... so it's looping through all of the sub objects of the sites node in your JSON, i.e. 1, 7 and 10.

Compose Sub Property Name

This is a Compose action which extracts the values, 1, 7 and 10 as it loops through.

THIS IS WHAT YOU NEED!!! This is the expression ...

item()['propertyName']

Compose Property Data

This is where we take the name of the property derived in the previous step and go and refer to that node in the original JSON. That then gives us the resulting data that is contained within that dynamically named property. This is the expression ...

outputs('Compose_Data')['sites'][outputs('Compose_Sub_Property_Name')]

... you can see, I'm using the results from the previous step to dynamically reference the object in the original JSON.