Parse JSON string in Power APPS

[Anonymous]

Hello,

Is there a way to parse a JSON string directly in PowerApps, and not in Flow? I have succeeded in parsing it in Flow, but I need to parse it in PowerApps directly.

Thank you.

Oana

[RusselThomas · Accepted solution]

Hi @Anonymous ,

I spent some time looking at this a while ago, so if the answer has changed I'd like to know 🙂

But at the time, the answer was 'no' - or at least, 'not easily'.  The JSON function currently represents data structures in JSON, but I couldn't find an easy way to parse JSON coming in or convert it into a data structure on the fly.  

I started writing some pretty complicated formulas to do it, but as you end up literally having to write a JSON parser with expressions and regex matching, it ended up being way to laborious.

Would be more than happy to be proven wrong on this though - you can in the meantime UPVOTE the idea in the ideas form;

https://powerusers.microsoft.com/t5/PowerApps-Ideas/Parse-JSON-in-PowerApps/idi-p/359563

Kind regards,

RT

[v-siky-msft]

Hi @Anonymous ,

As RusselThomas said, I'm afraid that PowerApps can't parse JSON directly right now.

You can provide the feedback to PowerApps Ideas Forum, if a feedback is high voted there by other customers, it will be promising that Microsoft Product Team will take it into consideration when designing the next version in the future.

Please follow up this Idea, lots of people are pushing this forward.

https://powerusers.microsoft.com/t5/PowerApps-Ideas/Parse-JSON-in-PowerApps/idi-p/359563

Best regards,

Sik

[Anonymous]

Thank you @v-siky-msft , @RusselThomas . I have voted the idea. Let's hope that it will be implemented.

[Anonymous]

@Anonymous How did you succeed parsing JSON using flow? Can you share more details?

[vladimir84]

I'll provide my workaround, if it can be of help, until the direct implementation of converting JSON inside PowerApps becomes a reality:

Create a flow like this:

Then, in PowerApps use a ClearCollect(colName, FlowName.Run("JSON Data you want to convert to a collection"))

(quick tip: to generate JSON data to use as an example for generating JSON in Flow, create a new label and use this formula:

JSON("JSON Data you want to convert to a collection"), then double-click on the label element and copy the selection)

[natvar]

As I didn't saw anything on the subject elsewhere, I'm pulling back this old question to give a very unperfect solution to parse JSON directly into a collection in PowerApps using MatchAll.

Note that there are limitations :

• The regex is simple so no quotes (") in the value of key
• To be able to parse back the json the same way, the keys in the object must be ordered alphabetically
• No embedded objects, it might be possible via multiple manipulation but the idea is to keep this as simple as possible

First let's look at the example JSON (it is beautified for visual purpose but must be compact for the match) :

{
   "comments":[
      {
         "date":"15/05/2020",
         "message":"Hi I'm me",
         "user":"p70389@nrb.be"
      },
      {
         "date":"02/07/2020",
         "message":"Hello, me too !",
         "user":"p58947@nrb.be"
      },
      {
         "date":"26/06/2020",
         "message":"Bye bye guys",
         "user":"p68925@nrb.be"
      }
   ],
   "status":[
      {
         "date":"27/02/2020",
         "status":"Available",
         "user":"p57896@nrb.be"
      },
      {
         "date":"17/08/2020",
         "status":"Unavailable",
         "user":"p45893@nrb.be"
      },
      {
         "date":"01/12/2020",
         "status":"Holiday",
         "user":"p45236@nrb.be"
      },
      {
         "date":"31/08/2020",
         "status":"Retired",
         "user":"p45869@nrb.be"
      }
   ]
}

Here is how you parse comments into a collection :

ClearCollect(
    CommentsData,
    MatchAll(
        Input.Text,
        "\{""date"":""(?<date>[^""]*)"",""message"":""(?<message>[^""]*)"",""user"":""(?<user>[^""]*)""\}"
    )
);

 The Input.Text store the data string. The MatchAll function is going to match each instance of our JSON object and convert it into an entry of the collection. The result of MatchAll is then stored into a collection called CommentData.

To go into more details about the regex : 

(?<StartDate>[^""]*)

This is a catch that has a property name assign to it. Normally, all the values caught are stored into an sub array of each match called SubMatches. But you can assign a property name to a catch with ?<propertyName>. Now in the collection returned by MatchAll, each match will have the property and the value of this property will be the data that was caught.

You can delete the unnecessary properties by creating a second collection only with the wanted fields (columns of the result of MatchAll cannot be deleted with DropColumns for some reason):

ClearCollect(
    Comments,
    ShowColumns(
        CommentsData,
        "date",
        "message",
        "user"
    )
);

With that, Comments will contain the perfect result of the JSON parsed into a collection. That mean that you can then very easily convert the collection back to a JSON !

Set(
    JsonString,
    JSON(
        Comments,
        JSONFormat.Compact
    )
);

This will store the collection as a correct JSON string in the variable JsonString. The JSON will only have the right properties. If you do not select the correct properties with the method seen above, your Json will have properties that comes from the MatchAll function.

Again since you cannot rearrange the order of the collection columns, they will always be alphabetically ordered so you JSON must be stored this way. 

As such you can parse JSON into a collection, read and edit it's data, then return the collection as a JSON string.

[xecho]

Hi @natvar ,

I am trying to work your solution, but it doesn't want to parse the Json string correctly.

This is what I have:

ClearCollect( CityPopulations,
    { City: "London",    Country: "United Kingdom", Population: 8615000 },
    { City: "Berlin",    Country: "Germany",        Population: 3562000 },
    { City: "Madrid",    Country: "Spain",          Population: 3165000 },
    { City: "Hamburg",   Country: "Germany",        Population: 1760000 },
    { City: "Barcelona", Country: "Spain",          Population: 1602000 },
    { City: "Munich",    Country: "Germany",        Population: 1494000 }
);

Set(
    JsonString,
    JSON(
        CityPopulations,
        JSONFormat.Compact
    )
);
ClearCollect(
    CityData,
    MatchAll(
        JsonString,
        "\{""City"":""(?<city>[^""]*)"",""Country"":""(?<country>[^""]*)"",""Population"":""(?<population>[^""]*)""\}"
    )
);

But my collection CityData remains empty.

Any idea what can be the issue?

Thanks, 

Bart

[xecho]

Hi,

I solved it already, the number for the population in the collection is not in between quotes and then the regex doesn't work :-).

Cheers,

Bart

[jainnishant]

Hello,
I am using your method to get the json data in powerapp collection from a sharepoint list. 
Collection is reeturning true i.e. boolean type. Help me with this. 
Schema of JSON is :
{
"type": "array",
"items": {
"type": "object",
"properties": {
"Capacity": {
"type": "integer"
},
"ClientContactName": {
"type": "string"
},
"Site_Details": {
"type": "string"
},
"Type": {
"type": "string"
},
"projectItemType": {
"type": "string"
},
"strategy": {
"type": "string"
}
},
"required": [
"Capacity",
"ClientContactName",
"Site_Details",
"Type",
"projectItemType",
"strategy"
]
}
}