Skip to main content

Notifications

Community site session details

Community site session details

Session Id :
Power Apps - Building Power Apps
Answered

Regex and MatchAll to parse JSON without Flow

Like (0) ShareShare
ReportReport
Posted on 23 May 2022 21:54:21 by 41

I am trying to parse a JSON string which will contain upwards of 14 columns per object, but may contain fewer as some columns may be blank or null. Here is a section of my JSON (shown with IndentFour for visual purposes only, it is normally set to Compact):

 

[
 {
 "Title": "05/13/2022 00:43:53",
 "actionDate": "2021-12-03",
 "bandwidth_delta": 215,
 "floor_exit_date": "2021-12-31",
 "locationId": 108,
 "max_seats_actual": 275,
 "max_seats_leveraged": 275,
 "projectId": 2,
 "running_container_w_hc": 60,
 "sum_container_hc": 166,
 "sum_container_w_hc": -166,
 "unique_key": "20211203_108"
 },
 {
 "Title": "05/13/2022 00:43:53",
 "actionDate": "2022-01-28",
 "bandwidth_delta": 19,
 "floor_exit_date": "2024-02-28",
 "locationId": 134,
 "max_seats_actual": 68,
 "max_seats_leveraged": 68,
 "projectId": 2,
 "running_container_w_hc": 49,
 "sum_container_hc": 1,
 "sum_container_w_hc": -1,
 "unique_key": "20220128_134"
 },
 {
 "Title": "05/13/2022 00:43:53",
 "actionDate": "2021-11-01",
 "bandwidth_delta": 194,
 "floor_exit_date": "2021-12-31",
 "locationId": 107,
 "max_seats_actual": 202,
 "max_seats_leveraged": 202,
 "projectId": 2,
 "running_container_w_hc": 8,
 "sum_container_hc": 176,
 "sum_container_w_hc": -176,
 "unique_key": "20211101_107"
 }
]

 

Here is the code the I use to get this from JSON into my collection:

 

ClearCollect(
 colActionItemAnalysis,
 MatchAll(
 Substitute(
 gblActionItemAnalysisJSON,
 Char(10),
 ""
 ),
 "\{(\s*""Title"":\s*""(?<Title>[^""]*)"",)?(\s*""actionDate"":\s*""(?<actionDate>[^""]*)"",)?(\s*""bandwidth_delta"":\s*(?<bandwidth_delta>\d*),)?(\s*""floor_exit_date"":\s*""(?<floor_exit_date>[^""]*)"",)?(\s*""locationId"":\s*(?<locationId>\d*),)?(\s*""max_people_served"":\s*(?<max_people_served>\d*),)?(\s*""max_seats_actual"":\s*(?<max_seats_actual>\d*),)?(\s*""max_seats_leveraged"":\s*(?<max_seats_leveraged>\d*),)?(\s*""planned_bandwidth"":\s*(?<planned_bandwidth>\d*),)?(\s*""projectId"":\s*(?<projectId>\d*),)?(\s*""running_container_w_hc"":\s*(?<running_container_w_hc>\d*),)?(\s*""sum_container_hc"":\s*(?<sum_container_hc>\d*),)?(\s*""sum_container_w_hc"":\s*(?<sum_container_w_hc>[^""]*),)?\s*""unique_key"":""\s*(?<unique_key>[^""]*)*"
 )
);

 

The issue I'm having is that not all of my items are coming through. For example, if I have 10 items in my JSON array, only 8 of them will come into my collection. It's not an exact percentage, so I would guess that my issues lies in that my regex does not cover all conditions. You will notice that I've defined 14 columns for my collection (in the regex), but my JSON only shows 12 columns. Due to the way the JSON function works in Power Apps, null or Blank columns do not get populated in the JSON string. I need to be able to populate up to all 14 columns, and I need to be able to get all rows every time.

 

My ultimate goal here to to only store this content in a JSON string in SharePoint, and read that JSON into a collection in Power Apps, Update the collection as-needed, and then update the JSON string in SharePoint. Currently I am storing all of these values in SharePoint rows, but it's vastly inefficient and slow for my needs.

 

This is my first time using RegEx, so looking for any possible help here.

  • Verified answer
    jdarling20 Profile Picture
    41 on 24 May 2022 at 13:49:08
    Re: Regex and MatchAll to parse JSON without Flow

    I was able to resolve this issue using the regex below:

     

    "\{""Title"":""(?<Title>[^""]*)"",""actionDate"":""(?<actionDate>[^""]*)"",""bandwidth_delta"":\s*(?<bandwidth_delta>[^""]*),(?:""floor_exit_date"":\s*""(?<floor_exit_date>[^""]*)"",)?(?:""locationId"":\s*(?<locationId>[^""]*),)?(?:""max_people_served"":\s*(?<max_people_served>[^""]*),)?(?:""max_seats_actual"":\s*(?<max_seats_actual>[^""]*),)?(?:""max_seats_leveraged"":\s*(?<max_seats_leveraged>[^""]*),)?(?:""planned_bandwidth"":\s*(?<planned_bandwidth>[^""]*),)?(?:""projectId"":\s*(?<projectId>[^""]*),)?(?:""running_container_w_hc"":\s*(?<running_container_w_hc>[^""]*),)?(?:""sum_container_hc"":\s*(?<sum_container_hc>[^""]*),)?(?:""sum_container_w_hc"":\s*(?<sum_container_w_hc>[^""]*),)?""unique_key"":\s*""(?<unique_key>[^""]*)"""

     

    It appears that some of my patterns to match for \d were not applying to all rows, thus causing my match to fail. I have been able to use this expression to match with all 956 objects in my JSON, all of which include the final (required) column for unique_key.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Understanding Microsoft Agents - Introductory Session

Confused about how agents work across the Microsoft ecosystem? Register today!

Warren Belz – Community Spotlight

We are honored to recognize Warren Belz as our May 2025 Community…

Congratulations to the April Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard > Power Apps - Building Power Apps

#1
MS.Ragavendar Profile Picture

MS.Ragavendar 20

#2
BCBuizer Profile Picture

BCBuizer 10 Super User 2025 Season 1

#2
LC-26081402-0 Profile Picture

LC-26081402-0 10

Overall leaderboard