cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

Parse JSON string in Power APPS

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

Re: Parse JSON string in Power APPS

Hi @omh ,

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

View solution in original post

8 REPLIES 8
Highlighted
Microsoft
Microsoft

Re: Parse JSON string in Power APPS

Hi @omh ,

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

View solution in original post

Highlighted
Community Support
Community Support

Re: Parse JSON string in Power APPS

Hi @omh ,

 

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

Highlighted
Helper II
Helper II

Re: Parse JSON string in Power APPS

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

Highlighted
Helper IV
Helper IV

Re: Parse JSON string in Power APPS

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

Highlighted
Advocate II
Advocate II

Re: Parse JSON string in Power APPS

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:

vladimir84_0-1595765054617.png

 

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)

Highlighted
Frequent Visitor

Re: Parse JSON string in Power APPS

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.
Highlighted
Regular Visitor

Re: Parse JSON string in Power APPS

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

Highlighted
Regular Visitor

Re: Parse JSON string in Power APPS

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

Helpful resources

Announcements
Check this Out

Helpful information

Featuring samples like Return to the Workplace and Emergency Response Applications

August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

secondImage

Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

secondImage

Super Users Coming in August

We are excited for the next Super User season.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (6,885)