How to Dynamically Process Nested JSON in Power Apps Without Predefined Schema?

Jerry
January 17, 2025
191 views
0 votes
2 Answers

I am working on a Power Apps application where I need to handle data returned from an API dynamically. The API response contains nested JSON objects, arrays, and sometimes objects within arrays. The challenge is that the structure of the JSON can change over time, making it impractical to hardcode field names or manually map properties.

Here are two examples of the API response structures I receive:

Example 1:

{
  "content": [
    {
      "name": "Marywood University",
      "alpha_two_code": "US",
      "domains": ["marywood.edu"],
      "country": "United States",
      "web_pages": ["https://www.google.com/"],
      "state-province": null
    },
    {
      "name": "Lindenwood University",
      "alpha_two_code": "US",
      "domains": ["lindenwood.edu"],
      "country": "United States",
      "web_pages": ["https://www.google.com/"],
      "state-province": null
    }
  ]
}

Example 2:

{
  "results": [
    {
      "gender": "female",
      "name": {
        "title": "Ms",
        "first": "Jessica",
        "last": "Perry"
      },
      "location": {
        "street": {
          "number": 8932,
          "name": "Manor Road"
        },
        "city": "Kilcoole",
        "state": "Meath",
        "country": "Ireland"
      },
      "email": "[email protected]"
    }
  ]
}

I need to dynamically process this data in Power Apps without hardcoding field names, while still accommodating changes in the API’s response format.

What did you try and what were you expecting?

Tried Flattening in Power Apps:
- I used functions like ParseJSON, Table, and ForAll to dynamically extract the properties of the JSON. However, Power Apps lacks native support for iterating over object properties or handling arrays within nested JSON dynamically.
Tried Key-Value Pair Mapping:
- I attempted to extract key-value pairs from the JSON using nested ForAll loops, but encountered errors like Name isn't valid or Text function has invalid arguments when processing dynamic fields like PropertyName and PropertyValue.

Expected Outcome:
I want to create a solution where Power Apps can dynamically process JSON data (including nested objects and arrays), regardless of the structure, and display it in a gallery or collection without requiring manual adjustments each time the API changes.

Answers

- hamzamalik
- January 17, 2025 at 3:02 pm
- 0 votes
0
APIs are handled by Power Automate;

If you’re using the standard HTTP Response block you can’t have dynamic API responses with this block as it requires a response schema. Best bet would be to have multiple blocks designed for each response format and return the correct one to PowerApps.

However if you’re handling the API response inside Power Automate and manipulating it, then returning the contents to PowerApps as in the below image. You have more flexibility as to what is being returned to PowerApps, if you’re calling an endpoint with a HTTP block then you’ll still need to have a schema which handles the response.

JSON contents being returned to PowerApp

Inside PowerApps you can then handle JSON and untyped objects.
```
{
  "Version" : 1,
  "RootElement" : {
    "Parent" : {
      "Name" : "This is the parent",
      "Child" : {
        "Name" : "This is the child"
      }
    }
  }
}
```
Then you can use the dot notation to extract fields.
```
Set( jsonObject, ParseJSON( jsonStringVariable ) );

Set( parentName, Text( jsonObject.RootElement.Parent.Name ) ); // "This is the parent"

Set( childName, Text( jsonObject.RootElement.Parent.Child.Name ) ); // "This is the child"
```
Example with an array
```
[
  {
    "firstName": "John",
    "lastName": "Doe",
    "age": 30
  },
  {
    "firstName": "Jane",
    "lastName": "Smith",
    "age": 25
  }
]
```
Parsing the above response from a Power Automate flow.
```
Collect(
    colPersons,
    ForAll(
        Table(ParseJSON(GetPersonItems.Run().result).body),
        {
            firstName: Text(Value.firstName),
            lastName: Text(Value.lastName),
            age: Value(Value.age),
        }
    )
)
```
Login or Signup to reply.

- carlosfigueira
- January 17, 2025 at 8:26 pm
- 0 votes
0
To be able to display an arbitrary response in a gallery, you need to know something about its shape. If the possible responses follow the pattern you showed above (an object, with a single property, whose value is an array of records), then you can use something like the expression below to convert from that JSON into a table with nested data:
```
ForAll(
    With(
        { myJson:ParseJSON(myInput) },
        Column(myJson, First(ColumnNames(myJson)).Value)
    ) As jsonRecord,
    {
        record: ForAll(
            ColumnNames(jsonRecord) As propName,
            {
                Name: propName.Value,
                Value: With(
                    { propValue: Column(jsonRecord, propName.Value) },
                    IfError(Text(propValue), JSON(propValue)))
            }
        )
    }
)
```
Notice the IfError usage above. If the value of the property is a primitive type (number, boolean, text), then we can convert it directly to text. If not (i.e., it’s a nested record or array), then I’m using the JSON function to stringify it so that we have a consistent type to use later. You could also use some other logic to convert those values to text.

If you use that expression in a gallery, then inside of it you can access the members of the record property, for example, to display in a label:
```
Concat(
    ThisItem.record As pair,
    $"{pair.Name}: {pair.Value}",
    Char(10))
```
Some examples from your input data:

Example 1:

Example 2:
Login or Signup to reply.

Please signup or login to give your own answer.

Click here to cancel reply.