Create lightening fast flows with XPath

Now that we understand the basics of JSON data structure, and how to work with them in Power Platform, let’s delve a little deeper into how to work with it in Power Automate

Using the Parse JSON Action

The Parse JSON action in Power Automate is essential for converting JSON strings into JSON objects that can be easily manipulated within your flow. Here’s a step-by-step guide to using this action:

1. Obtain JSON Data: The JSON data can come from various sources such as HTTP requests, files, or other actions within your flow (e.g., from SharePoint or emails)​
2. Add the Parse JSON Action:
   • Insert the Parse JSON action in your flow.
   • In the Content field, provide the JSON string that you want to parse. This can often be the body of a previous HTTP action or another JSON output​
3. Generate Schema: Click the “Generate from sample” button within the Parse JSON action to automatically generate the schema.
   • Paste a sample JSON payload into the schema generation window. This helps Power Automate understand the structure and types of data it should expect​.
4. Refine Schema: Adjust the generated schema as necessary to ensure all data types and fields are correctly defined. This may involve adding or removing properties and setting data types correctly (e.g., string, integer, Boolean)
5. Use Parsed Data: Once the JSON is parsed, the resulting data can be accessed and used as dynamic content in subsequent actions within your flow. This allows for more straightforward manipulation and utilization of specific JSON values​.

Writing Expressions to Work with JSON

Directly working with JSON using expressions can sometimes be more efficient, especially for simple tasks where you don’t need the full parsing capabilities. Here are some common expressions and techniques:

1. Accessing Values:

1. • Using Keys and Indices: To access specific items in a JSON object or array, use expressions that navigate through the structure. For example:

outputs(‘Parse_JSON’)?[‘students’][0]?[‘name’]

This expression retrieves the name of the first student from a parsed JSON object​

1. • Handling Nested Structures: When dealing with nested arrays or objects, chain the key and index references appropriately to drill down to the desired value.

body(‘Get_items’)?[‘value’][rand(0,length(body(‘Get_items’)?[‘value’]))]?[‘Title’]

This example selects a random item from an array and retrieves its title​

Power Platform API Limits

Power Platform, including Power Automate, imposes several types of limits to ensure service stability and fair usage across all users. These limits are categorized primarily into request limits and service protection limits.

Request Limits: Request limits define the maximum number of API requests a user can make within a specific period, typically a 24-hour cycle. These limits vary based on the licensing plan:

• Standard Users: Up to 40,000 requests per day.
• Non-licensed Users: Aggregated request limits based on product subscriptions.
• Capacity Add-ons: Additional capacity can be purchased in increments of 50,000 requests per 24 hours

Service Protection Limits: These limits ensure that no single user can monopolize resources and degrade service quality for others. The primary facets of service protection limits include:

• Number of Requests: Up to 6,000 requests per user in a 5-minute sliding window.
• Execution Time: Combined execution time of 20 minutes (1200 seconds) within a 5-minute window.
• Concurrent Requests: A maximum of 52 concurrent requests per user​.

Techniques to Handle API Limits and Throttling

To efficiently manage these limits and avoid throttling, implement the following strategies in your Power Automate workflows:

1. Optimize Data Retrieval and Processing:

1. • Filter and Sort Data at Source: Use query parameters to fetch only the necessary data, minimizing the payload and reducing the number of API calls.
   • Batch Requests: Combine multiple operations into a single batch request to reduce the number of individual API calls. Be mindful of the execution time limits for batch operations​.

2. Implement Retry Policies:

1. • Use retry policies to handle transient errors and throttling. Configure retry intervals and maximum retry attempts to ensure smoother operation under high load conditions. Power Automate provides built-in options to set these parameters.

3. Distribute Workloads:

1. • Multiple Flows: Split complex or long-running workflows into smaller, asynchronous flows. This helps distribute the workload and prevents any single flow from hitting the limits.
   • Delay Actions: Introduce delays between successive API calls within a flow to avoid hitting the rate limits. This is particularly useful in loops or bulk processing scenarios.

4. Monitor and Adjust Usage:

1. • Admin Center Reports: Utilize the Power Platform Admin Center to monitor API usage and identify flows that are approaching or exceeding limits. This helps in proactive capacity planning and adjustments.

5. Handle Specific Query Throttling:

1. • Optimize queries to reduce database load, such as avoiding leading wildcards in filters and using indexed columns. For high-frequency automated operations, consider reducing batch sizes or adding delays between requests.

Expressions in Power Automate

Expressions in Power Automate allow users to perform complex data manipulations and transformations within their flows. These expressions are built using the Workflow Definition Language (WDL) and can handle a variety of tasks, including string operations, mathematical calculations, and data conversions.

Key Expressions:

1. first()

• • Purpose: Returns the first item from an array or string.
  • Example: first(outputs(‘Compose_YourArray’))

2. length()

• • Purpose: Returns the number of items in an array or the number of characters in a string.
  • Example: length(outputs(‘Compose_XPath_Result’))

3. concat()

• • Purpose: Combines multiple strings into one string.
  • Example: concat(‘{ “root”: ‘, outputs(‘HTTP_Body’), ‘ }’)

4. xpath()

• • Purpose: Applies an XPath expression to XML content to extract data.
  • Example: xpath(xml(outputs(‘Compose_with_XML’)), ‘//members/mail’)

5. xml()

• • Purpose: Converts a JSON string formatted correctly into XML.
  • Example: xml(outputs(‘Compose_JSON_to_XML’))
  • Context: Crucial for converting JSON to XML format after adding a root element.

6. coalesce()

• • Purpose: Returns the first non-null value from a list of arguments.
  • Example: coalesce(outputs(‘Compose_YourArray’)?[99], ‘No value’)

7. addProperty()

• • Purpose: Adds or updates a property in an existing JSON object.
  • Example: addProperty(outputs(‘Compose_YourObject’), ‘newProperty’, ‘newValue’)

8. variables()

• • Purpose: Retrieves the value of a specified variable.
  • Example: variables(‘index’)

9. replace()

• • Purpose: Replaces occurrences of a specified string within a given string.
  • Example: replace(outputs(‘Compose_String’), ‘[‘, ”)
  • Context: Useful for cleaning up strings, especially when formatting output from XML.

10. sort()

• • Purpose: Sorts an array in ascending or descending order based on a specified property.
  • Example: sort(outputs(‘Compose_YourArray’), ‘propertyName’, ‘ascending’)

11. chunk()

• • Purpose: Splits an array into chunks of a specified size.
  • Example: chunk(outputs(‘Compose_YourArray’), 2