Blog

Formula - How to parse JSON in Power Apps- 4 examples

Up until last week, support for JSON in Power Apps was limited. For a long time, it was possible to convert records and table objects/collections to JSON by calling the JSON function.


However, there was no built-in way to perform the reverse operation - that is, to convert JSON to tables/records/objects that we can use in our apps.

The great news is that Microsoft has started to address this limitation by introducing the parseJSON function. The documentation for this function is here

In the remainder of this post, we cover the formula we can use to parse the most typical JSON structures.

What are the use case scenarios of parseJSON?

Why would we want to call parseJSON? A typical scenario is where we want to parse JSON that's stored in a database, or JSON that we retrieve from a web service.

Prior to parseJSON, it was difficult to carry out this task because it relied on using tricky text manipulation functions, and regular expressions.

1 - How to parse a single JSON record

Let's start with a simple example of how to parse a single JSON record. The source JSON looks like this.

{
  "title": "Mr",
  "firstName": "Tim",
  "lastName": "Leung",
  "age": 40,
  "email":"[email protected]"
}
To demonstrate a typical use case, we'll extract the firstName and lastName values and display the concatenated values in a label.

To make it easier to illustrate the formula in this post, we'll place the input JSON in a text input control called txtJSON. This then avoids the need to escape the double quotes in the JSON that's posted here.

The parseJSON function accepts a single value (the source JSON). This function parses the JSON at run time. Because of this, the designer has no knowledge of the schema/structure of the return value of the parseJSON function at design time.

Therefore, parseJSON returns an UntypedObject, as shown in the screenshot of the designer below.



The difficulty with an UntypedObject is that the designer provides no IntelliSense to indicate the field names from the parsed JSON object. Also in order to use the parsed JSON, we must explicitly convert the field values to known types such as text or numbers (by calling the Text or Value functions).

Returning to our example, here's a formula we can use to parse a single JSON record.

With({obj:ParseJSON(txtJSON.Text)},
     Text(obj.firstName) & " " & Text(obj.lastName)
)
The screenshot beneath shows the result when we apply this formula to a text label.
 


To extend this example further, here's an example of how to use the same approach to parse the input JSON and to use the HTML text control to build an email hyperlink.


With({obj:ParseJSON(txtJSON.Text)},
     $"<a href=""mailto:{Text(obj.email)}"">
       {Text(obj.firstName)} {Text(obj.lastName)}
      </a>"
)
Here's a screenshot of the end result.

2 - How to parse an array of multiple JSON records

The second typical scenario is to parse an array of input records and to display the result in a control such as a gallery, data table, or combobox.

Here's an example of how the input data would look.

[
    {
    "title": "Mr",
    "firstName": "Tim",
    "lastName": "Leung",
    "age": 40,
    "email":"[email protected]"
    },
    {
    "title": "Mrs",
    "firstName": "Sally",
    "lastName": "Smith",
    "age": 30,
    "email":"[email protected]"
    },
    {
    "title": "Mr",
    "firstName": "John",
    "lastName": "Taylor",
    "age": 45,
    "email":"[email protected]"
    }
]


Again, we call parseJSON on the input data. For the return value to be usable, we call the Table function to convert the result to a table.

To illustrate the return value of this call to parseJSON, let's store the return value in a variable.

Set(varRecords, 
    Table(ParseJSON(txtJSON2.Text))
)

The result of this is a table variable with multiple rows - each row is an untyped object.


To extract the field values (such as title, firstName, lastName), we call the ForAll function and carry out the explicit conversion of the field values to text like so.

ForAll(
    Table(ParseJSON(txtJSON2.Text)),
    {
        title:Text(Value.title),
        firstName:Text(Value.firstName),
        lastName:Text(Value.lastName)
    }
)

To illustrate the end result, here's what we see when we set the Items property of a data table control to this formula.


3 - How to parse a single JSON record with nested multiple records

Extending the above example of parsing multiple records, another common structure we can encounter is a single record that contains nested records.

Here's how the sample JSON looks.

{
    "clients":
    [
        {
        "title": "Mr",
        "firstName": "Tim",
        "lastName": "Leung",
        "age": 40,
        "email":"[email protected]"
        },
        {
        "title": "Mrs",
        "firstName": "Sally",
        "lastName": "Smith",
        "age": 30,
        "email":"[email protected]"
        }
    ]
}


In this scenario, we can adapt our previous formula like so.

ForAll(
    Table(ParseJSON(txtJSON3.Text).clients),
    {
        title:Text(Value.title),
        firstName:Text(Value.firstName),
        lastName:Text(Value.lastName)
    }
)

Here's an illustration of the result when we set the Items property of a data table control to the formula.


4 - How to parse multiple JSON records with multiple nested records

|n this last example, we look at how to parse an array of records, each with nested records.

Here's how the input data looks.

[
   {
      "firstName":"Tim",
      "lastName":"Leung",
      "phoneNumbers":[
         {
            "type":"home",
            "number":"098322256"
         },
         {
            "type":"work",
            "number":"098322085"
         }
      ]
   },
   {
      "firstName":"Sally",
      "lastName":"Smith",
      "phoneNumbers":[
         {
            "type":"home",
            "number":"098324566"
         },
         {
            "type":"work",
            "number":"098389089"
         }
      ]
   },
   {
      "firstName":"John",
      "lastName":"Taylor",
      "phoneNumbers":[
         {
            "type":"home",
            "number":"093445345"
         },
         {
            "type":"home",
            "number":"098354244"
         }
      ]
   }
]


To parse the multiple parent records, we use the ForAll method as described above.

To parse the child 'phone number' records within each parent record, we nest a call to ForAll like so:

ForAll(
    Table(ParseJSON(txtJSON4.Text)),
    {
        firstName:Text(Value.firstName),
        lastName:Text(Value.lastName),
        phoneNumbers: ForAll(
            Table(Value.phoneNumbers),
            {
                type:Text(Value.type),
                number:Text(Value.number)
            }
        )
    }
)

To illustrate how to display this data, we can take the typical approach of setting the Items property of a gallery to the above formula.


Within the gallery template, we can then add a nested child gallery to display the phone numbers for each parent record. Here, we set the items property of the child gallery to ThisItem.phoneNumbers, and we can then reference ThisItem.type and ThisItem.number from the child gallery.

Conclusion

The ability to parse JSON was one of the most requested features and we can now do this with the parseJSON function. This post demonstrated the formula to parse 4 common JSON structures.

Microsoft are still developing the parseJSON function. Whilst parseJSON provides an enormous improvement over what we had, the challenge is that it's difficult to work with untyped objects because it's necessary to explicitly cast each field value to it's data type. Microsoft have annouced that a future release will include the ability to pass a JSON schema as a second argument. When this happens, the process of parsing JSON in Power Apps will become even simpler.
Previous
Next
Related posts