How to convert json to collection in power apps

Question

I have a power app that using the flow from power automate.

My flow is doing an HTTP get and respond a JSON to power apps like below.

Here is the JSON as text:

{"value": "[{\"dataAreaId\":\"mv\",\"AccountNum\":\"100000\",\"Name\":\"*****L FOOD AB\"},{\"dataAreaId\":\"mv\",\"AccountNum\":\"100001\",\"Name\":\"**** AB\"},{\"dataAreaId\":\"mv\",\"AccountNum\":\"100014\",\"Name\":\"****(SEB)\"},{\"dataAreaId\":\"mv\",\"AccountNum\":\"100021\",\"Name\":\"**** AB\"},{\"dataAreaId\":\"mv\",\"AccountNum\":\"100029\",\"Name\":\"**** AB\"},{\"dataAreaId\":\"mv\",\"AccountNum\":\"500100\",\"Name\":\"**** AB\"},{\"dataAreaId\":\"mv\",\"AccountNum\":\"500210\",\"Name\":\"****\"}]"}

But when I try to convert this JSON to the collection, It doesn't behave like a list.

It just seems like a text. Here is how I try to bind the list.

How can I create a collection from JSON to bind to the gallery view?

Answer 1

I found the solution. I finally create a collection from the response of flow. The flow's name is GetVendor.

The response of flow is like this :

{"value": "[{\"dataAreaId\":\"mv\",\"AccountNum\":\"100000\",\"Name\":\"*****L FOOD AB\"},{\"dataAreaId\":\"mv\",\"AccountNum\":\"100001\",\"Name\":\"**** AB\"},{\"dataAreaId\":\"mv\",\"AccountNum\":\"100014\",\"Name\":\"****(SEB)\"},{\"dataAreaId\":\"mv\",\"AccountNum\":\"100021\",\"Name\":\"**** AB\"},{\"dataAreaId\":\"mv\",\"AccountNum\":\"100029\",\"Name\":\"**** AB\"},{\"dataAreaId\":\"mv\",\"AccountNum\":\"500100\",\"Name\":\"**** AB\"},{\"dataAreaId\":\"mv\",\"AccountNum\":\"500210\",\"Name\":\"****\"}]"}

Below code creates a list from this response :

ClearCollect(_vendorData, MatchAll(GetVendors.Run(_token.value).value, "\{""dataAreaId"":""(?<dataAreaId>[^""]*)"",""AccountNum"":""(?<AccountNum>[^""]*)"",""Name"":""(?<Name>[^""]*)""\}"));

And I could bind the accountnum and name from _vendorDatra collection to the gallery view

Answer 2

In my case I had the same issue as you, but couldn't manage to get data into _vendorData collection, because MatchAll regex part was not working correctly, even if I had exactly the same scenario and I could not make it work.

My solution was to modify the flow itself, where I returned Response instead of Respond to a Power app or Flow, so basically I could return full request from Http.

This caused me some issues also, because when I generated schema from sample I could not register the flow to the powerapp with the error Failed during http send request.

The solution was to manually review the response schema and change all column types to one of the following three, because other are not supported: string, integer or boolean. Object and array can be set only on top level items, but never on children, so if you have anything else than my mentioned three, replace it to string. And no property can be left with undefined type.

Basically I like this solution even more, because in powerapps itself you do not need to do any conversion or anything - simply use the data as is, because it is already recognized as collection in case of array and you have all the properties already named for you.

Response step schema example is below.

{
    "type": "object",
    "properties": {
        "PropertyOne": {
            "type": "string"
        },
        "PropertyTwo": {
            "type": "integer"
        },
        "PropertyThree": {
            "type": "boolean"
        },
        "PropertyFour": {
            "type": "array",
            "items": {
                "type": "object",
                "properties": {
                    "PropertyArray1": {
                        "type": "string"
                    },
                    "PropertyArray1": {
                        "type": "integer"
                    },
                    "PropertyArray1": {
                        "type": "boolean"
                    }
        }
}

Answer 3

It is easy now.

Power Apps introduced ParseJSON function which helps converting string to collection easily.

Table(ParseJSON(JSONString));

In gallery, map columns like - ThisItem.Value.ColumnName