Power Automate is a very useful tool for data migration and transformation in certain contexts. However, it falls short when it comes to providing an easy solution for extracting keys from JSON objects, which can pose challenges for many users looking to work with structured data.

As every step output in Power Automate is essentially a JSON format behind the scenes, understanding how to extract its keys can be helpful. This feature enables users to pull column names from an Excel table, a CSV file, or a SharePoint list, as well as retrieve property names from the outputs of various actions within a flow. In this article, we will guide you through accessing JSON object keys in Power Automate, using a real-life example to illustrate the process.

Use case overview

For the purposes of this article, we will focus on a real-life use case: the extraction of the column names from the following Excel table:

The flow will process the data returned by Excel and generate an array containing the column names from the table, along with any metadata related to a table record. In this case, the array will include properties such as the ItemInternalId (highlighted in yellow), which represents the internal identifier for each record in the table:

During this process, we will need to convert the outputs from one of the table records to a XML format, extract its node names and append it to an array variable.

JSON and object data type in Power Automate

A JSON object is structured a way to organize and store information using key-value pairs, making it clear and easy-to-read for both computers and humans. It’s widely used in programming and data sharing, and particularly in Power Automate, where almost all action outputs are formatted as JSON objects. You can find more details on how this JSON operates in the backend of Power Automate in this article about how to access the raw outputs.

Power Automate includes a specific data type to handle JSON formatting: the object. Similar to JSON objects, the object data type stores complex data structures organized in key-value pairs. In this format, the key represents a property name, and the value represents the corresponding data or information. The objects are enclosed in curly braces (“{}”), and each key-value pair separated by a colon.

Here is an example of a JSON (or object) representing the entire value retrieved by a List rows present in a table action, pulling data from the Excel table used as sample in this article. You can notice that it starts with a curly brace, and contains pairs of keys (such as statusCode) and their corresponding values (such as 200). Although the keys are always strings, the values support any data type, like numbers, strings, arrays or other objects:

Step 1: Initialize an array variable and return data from Excel

Let’s start by simply creating an empty array variable named keys. This variable will store all column names from the Excel table:

Next, we use a List rows present in a table action to return the records from the Excel table. Since we only need a single record for this solution, you can set the Top Count input to 1 (highlighted in blue), ensuring that your action will be executed quickly. Alternatively, you can also use a Get a row action, which guarantees that only one specific record is retrieved:

Step 2: Assign JSON to a root key

As previously mentioned, we will need to access only one record returned by List rows present in a table action, which value property corresponds to an array of objects, even when the Top Count condition is set to 1 (in this case, the array will contain a single object). We can add a Compose action and set its inputs as a first() expression, passing the value property as unique input to access the first element of the array:

If you need more instructions on how to work with expressions in Power Automate, refer to this blog.

After that, we still need to perform some transformation on the Compose. Since we need to convert a JSON object into XML notation to extract the keys, it’s essential for the object to have a single root (or parent) key. However, the Excel table record object has multiple properties, so we must encapsulate it into another object with a single key, creating a parent-child relationship in the structure.

We can achieve this by simply enclosing our Compose within curly braces and adding a single key (in this case, named “data”):

By performing these changes, we transformed the object corresponding to the Excel table record into the value of the data key, within an outer object. This restructuring is essential for the next step, as it aligns our JSON object with the requirement of having a single root key:

Step 3: Converting JSON to XML

Now we will convert the JSON from Compose action into XML, extract its path and loop through each of its nodes (which are the XML equivalents of JSON object key/value pairs). To accomplish this, we will add an Apply to each loop, and use the following expression as input: xpath(xml(outputs(‘Compose’)), ‘/data/*’). This expression will retrieve an array containing all the child nodes under the data key.

From your end, make sure to replace outputs(‘Compose’) with the dynamic content from your Compose action, and adjust data to match the parent key you used within your Compose:

Step 4: Extracting the XML node name (or JSON key) with XPATH

Inside the Apply to each loop, add an Append to array variable action. Select the variable keys in the dropdown and set the following expression as value: xpath(item(), ‘name(/*)’). This expression retrieves the name property from each XML node, which corresponds to the original keys from the JSON object:

The flow is ready to run as it is. However, to enhance visibility, let’s include a final Compose that receives the keys variable, so we can visualize its outputs after the Apply to each:

Testing the flow: Array output

The final result will be an array containing all the properties from the Excel record object, excluding @odata.etag. This is a limitation from the xml() expression in Power Automate, which cannot process keys started with @ sign:

Conclusion

Extracting JSON keys in Power Automate can be challenging for beginners, as it there is no built-in action specifically designed for this purpose. However, it’s possible to combine different actions to perform this task. This article provided a practical example of retrieving column names from an Excel table with Power Automate.

Understanding how to navigate and manipulate JSON objects is a valuable skill that can enhance your data workflows in Power Automate. Whether you’re working with Excel tables, SharePoint lists, or other data sources, this knowledge allows you to streamline processes and extract information effectively.