Power Fx: Introducing ParseJSON

We are pleased to announce the experimental release of our ParseJSON function, which can parse JSON strings into Power Fx objects and types.

This new function dramatically simplifies working with JSON in Canvas apps. For example, we recently worked with a customer who needed to extract information from a relatively simple JSON string stored in a Dataverse table:

[    
    {
        "PredictionID": "e438ec93-dee2-4a6c-92d1-8c9e87a1b1d3",
        "CustomerID": "2c9c3dae-5113-4b6b-9cc5-ad25b955b463",
        "id": "b8cf0ea6-b6f5-46c3-9fc2-5403fb7fdd2d",
        "Score": 0.7848321,
        "ProductID": "628065",
        "ProductName": "Large Widget"
    },
    {
        "PredictionID": "5ac7b6aa-d069-4c2d-b593-bda5bf3e2f56",
        "CustomerID": "2c9c3dae-5113-4b6b-9cc5-ad25b955b463"
        "id": "b8cf0ea6-b6f5-46c3-9fc2-5403fb7fdd2d",
        "Score": 0.82974886,
        "ProductID": "527174",
        "ProductName": "Small Widget"
    },
    ...

Their first solution was to use a Microsoft Power Automate flow to do the parsing which has excellent JSON support. However, the volume and latency of these calls didn’t work for their scenario. So, like many of you, we turned to regular expression matching using this formula:

MatchAll( ProductRecommendationsJSON, 
          """Score""\s*:\s*(?<Score>[^,]*)\s*,\s*" & 
          """ProductID""\s*:\s*""(?<ProductID>[^""]*)""\s*,\s*" & 
          """ProductName""\s*:\s*""(?<ProductName>[^""]*)""", MatchOptions.Multiline ) 

This works and is what the customer shipped their app with. However, it is hard to write and verify with that complex regular expression syntax and is fragile, for example it will break if the order of the JSON fields is changed or another field inserted.

A better answer is a dedicated JSON parser, a highly requested feature on our community ideas forum. With the new ParseJSON function, this can be written as:

ForAll( Table( ParseJSON( ProductRecommendationsJSON ) ), 
        { Score: Value( Value.Score ), 
          ProductID: Text( Value.ProductID ), 
          ProductName: Text( Value.ProductName ) } )

This is both easier to read and more robust, tolerating reordered and additional fields. We have plans to further simplify this with better untyped object array support and casting as a whole via a schema rather than field by field. But this will get you started and will continue to be supported with those enhancements.

Note that this is an experimental feature. This functionality will change. We are releasing it now for your feedback and for use in proof-of-concept, non-production situations. To use this functionality, you must opt-in to the feature under Settings > Upcoming features > Experimental. Please post your feedback in our experimental features forum.

To learn more, keep reading, and check out our Working with JSON documentation.

JSON and Power Apps

The JSON format has seen widespread adoption in serializing application objects for systems communications and saving of state. Many services today offer RESTful services that communicate via JSON payloads, including services such as SharePoint and Azure DevOps. Power Apps provides a large amount of out of the box connectors, many of which talk to services via JSON and provide Power Fx types as input and output.

But there are cases where either a service can provide very dynamic JSON payloads, or the data is provided as text but in practice contains JSON objects.

In 2020 we released an experimental feature called Dynamic Schema which addresses specific scenarios such as custom fields on Azure DevOps work items. The standard connector for Azure DevOps can only know about standard Azure DevOps fields, but a “capture schema” feature allows a maker to have Power Apps capture the output of the connector call and adapt the schema based on what the output provides. A maker can subsequently work with the connector in Power Fx as if the fields were always part of the connector’s schema. This is a fairly static “update” to the schema that can be made when authoring the app.

Today’s experimental release of ParseJSON addresses the other end of the spectrum, and provides an important base in the Power Fx language and runtime to bridge the areas in between.

Untyped Object

To handle the most dynamic scenarios with a JSON string, we need to address the fact that Power Fx is a strongly-typed language. The JSON format, in comparison, offers very few data types. Additionally, in a scenario where the JSON payload can change when the Power App is run (as opposed to when it is built) we must have the ability to read the JSON and convert into the types we need from the formulas that need them.

To support this most dynamic of scenarios, we have introduced a new type called untyped object. It is, in effect, a wrapper for a value or object that can be converted to a concrete Power Fx type at the time the app runs. When authoring the app, makers can use the untyped object to write formulas that make assumptions about the nature of the actual object or value when a user will use the app. This does mean the maker has some knowledge about the incoming data and its structure, as this metadata is not available during authoring and the untyped object cannot provide any help (such as IntelliSense). Untyped objects can contain values (numbers, text, Boolean, dates, etc.), records and tables. To use any underlying values of untyped objects, they have to be cast explicitly to their respective Power Fx types using the type construction functions such as Boolean (a new function), Text, Value, DateTimeValue, etc.

ParseJSON

The ParseJSON function will be the first (and for now the only) function to return an untyped object. It accepts a text value that is a valid JSON string.

For example, assume a custom connector “myConnector” that provides a GetJSONString() function:

ParseJSON( myConnector.GetJSONString() )

Now assume the connector returns the following JSON:

{
    "TextField" : "Hello, World!",
    "Version" : 1.1
}

We can store the untyped object that ParseJSON returns in a variable called “untypedVariable”, and access the individual fields with the regular dot notation:

Set( untypedVariable, ParseJSON( myConnector.GetJSONString() ) )
untypedVariable.TextField
untypedVariable.Version

However, the fields on the untypedVariable (TextField, Version) are also untyped objects and have to be explicitly converted to a type that can be used in formulas for Power Apps properties. For example, to use the TextField in the text property of a label control, the Text() function has to be used to convert the untyped object value to a text value:

Text( untypedVariable.TextField )

Similarly, a JSON array of values or records can be converted to a table directly with the Table function. That will result in a single-column table of untyped objects, requiring each untyped object value or record field in the table to be converted to a proper type.

For example, we can parse the following JSON string into an untyped object variable named “untypedVariable”.

[
    { "IndexField" : 1, "Title" : "One" },
    { "IndexField" : 2, "Title" : "Two" }
]

We can now convert this JSON array of records into a table with the Table function:

Table( untypedVariable )

If you wish to use this table in, for example, a collection and gallery, you have two options. Either use the table as-is, and in the gallery convert any property values to specific Power Fx types. Or, convert the table to a typed table with ForAll prior to assigning to the gallery.

To create the collection of untyped objects, you can use the following formula:

ClearCollect( collection, Table( untypedVariable ) )

Inside the gallery using this collection, each field will need to be converted into the correct Power Fx type:

Text( ThisItem.Title )

To convert the untyped object array directly into a typed Power Fx table, you can use ForAll which can return a table:

ClearCollect(
    collection,
    ForAll(
        Table(untypedVariable),
        {
            IndexField: Value(Value.IndexField),
            Title: Text(Value.Title)
        }
    )
)

Now the gallery can use the collection with an entirely typed record.

For more information and examples, see the Working with JSON article on the docs site.

What’s Next

The ParseJSON function with untyped objects provides a very generic way to handle dynamic incoming JSON through Power Fx formulas. We are considering next steps to bridge the scenarios in between dynamic schema capture during authoring, and untyped objects in formulas. Imagine declaring your own schema through Power Fx formulas or YAML definitions, and having connectors or ParseJSON automatically convert the incoming payload to the Power Fx typed schema you defined. This would provide a broad spectrum of options from capture schema from a sample payload, to defining schemas that can automatically convert, all the way to code-first handling with untyped objects.

While we work out what, how, and when we can introduce this spectrum of features, we plan to move the current ParseJSON and untyped object features to preview and GA quickly as we believe the flexibility it provides will allow makers to address their JSON parsing needs.

Additionally, untyped objects provide a basis for other potential features and functions that deal with external data structures, such as CSV, XML, etc.

As always, we value your feedback and suggestions on the currently released features and future roadmap. Join the discussion in our experimental features forum.