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

[natvar]

Hello @jainnishant 

From what I can gather from you question. Here is the code I would use :

ClearCollect(
    MyData,
    MatchAll(
        JsonString,
        "\{""Capacity"":(?<Capacity>\d*),""ClientContactName"":""(?<ClientContactName>[^""]*)"",""Site_Details"":""(?<Site_Details>[^""]*)"",""Type"":""(?<Type>[^""]*)"",""projectItemType"":""(?<projectItemType>[^""]*)"",""strategy"":""(?<strategy>[^""]*)""\}"
    )
);

Important note that is not in my first comment : the example show a JSON with only strings. If you don't have any knowledge about regex and matching I would recommend heavily to read documentation on the subject.  Here is the documentation about MatchAll . 

But for integer value you can use (?<MyInteger>\d*) instead of ""(?<MyString>[^""]*)"". Or for any number, simply remove the two "".

Be aware that the value in the collection of Power Apps will be as a string. Use Value() to fix that.

[mirfro]

Hi @vladimir84, could you please provide your setup for the response part in your solution? I tried your flow, but end up with a collection with only the value "true" and not the information from my JSON. So I am doing something wrong when setting up my flow. 
This is how my flow looks at the moment:

Thank you for your help!

[vladimir84]

Hi @mirfro 

Here is mine, and unfortunately it takes a bit of tweaking. I had the same issue:

If I remember correctly, in Parse JSON you can have the type ["string", "null"], but you can't have it in the Response.

To get the correct JSON structure, I'd do a Set(varJSONtable, JSON(collectionName)) and then assign to the Label the varJSONtable. Double-click into the label to select all and copy-paste the JSON into Flow.

Hope it helps.

[mirfro]

Thanks @vladimir84, seeing your solution and your mention that the Response cannot have the type ["string", "null"] made it function! Had to do a bit of tweaking and update the flow reference in PowerApps, but it worked in the end!

[LaelH]

Thanks for your imperfect solution. It works PERFECTLY for me! Gotto love this community!

[TedBabcock]

Mr Dang has addressed this issue. He provides a clear explanation and, even better, an app that will create the complicated formula you need to parse JSON in PowerApps:

https://powerapps.microsoft.com/en-us/blog/return-an-array-from-a-sql-stored-procedure-to-powerapps-...

[josephspengler]

Natvar's solution is brilliant. I had to adapt it a little and ran into some issues, I thought I'd share to help others.

Although I was creating JSON with no whitespace, I was repeatedly loading it in power automate, parsing it, adding to it, then saving it back to sharepoint list. That introduced whitespace. No big deal, just modify regex to tolerate whitespace. But that didn't work. Turns out the whitespace Power Automate/Sharepoint introduced included a newline char that powerapps does not consider whitespace. So my ultimate solution was to first use Substitute to get rid of those newline Chars, then do regex that tolerates whitespace. Here's my adapted version of Natvar's original:

ClearCollect(
    CommentsData,
    MatchAll(
        Substitute(Input.Text,Char(10),""),
        "\{\s*""Count"":\s*(?<count>[^""]*),\s*""type"":\s*""(?<curr>[^""]*)""\}"
    )
);

Also, in my example Count is a number, so has no quotes around it, so I adapted the regex for that as well.

[ShradhaS]

Try using below method to convert JSON array to collection in power Apps
lblJASON.Text: 
[{"SERIES_ID":"1234","PROGRAM_TITLE":"GameOfThornes","SEASON":"Season_10"},{"SERIES_ID":"2222","PROGRAM_TITLE":"Friends","SEASON":"Season_2"},{"SERIES_ID":"5566","PROGRAM_TITLE":"HarryPotter","SEASON":"Season_5"}]

Note: Make sure JSON should be in single line and without spaces.

Parse_JASON Function:

ClearCollect( varParsedJson, MatchAll( lblJASON.Text, "\{""SERIES_ID"":""(?[^""]*)"",""PROGRAM_TITLE"":""(?[^""]*)"",""SEASON"":""(?[^""]*)" ) )

Note: To remove space and bring text inline use below Substitute() function

Set(varResponse,Substitute(lblJASON.Text,Char(10),""));  //--- To remove new line from JSON
Substitute(varResponse," ","")   //--- To to remove spaces

[TedBabcock]

I've tried a couple of those ideas using regex and couldn't get it to work quickly, or at all. It's beyond me. Shane Young has a video on copying-and-pasting from Excel that does some similar things, but it too requires lots of tweaking, so I'll be lazy and wait for the official function. 🙂 It would also be nice if a flow could just return an array to the app -- then nothing would have to be parsed, it'd be just, "Gimme an array," and done.

[ganeshsanap]

@TedBabcock @ShradhaS @josephspengler @LaelH @mirfro 

Power apps now supports experimental ParseJSON function which might be helpful to you. Check below links for detailed information:

1. Power Fx: Introducing ParseJSON 
2. ParseJSON function in Power Apps (experimental) 

---

Please click Accept as solution & 👍 if my answer helped you to solve your issue. This will help others to find the correct solution easily. If the answer was useful in other ways, please consider giving it 👍