Help with Regex to cleanup data and parse JSON

[BIT90]

Hello,

I am attempting to accept API data on a few of our aircraft and post that to an SP List.  I'm new to this process and am having a hard time parsing the data and getting the properties I want because of the format:

{

    "N840G": {

        "tail_number": "N840G",

        "airport_code": "",

        "pilot": [

            "Joe Smith"

        ],

        "fleet": "Air Attack",

        "contract": "",

        "contract_contact": "",

        "hobbs_start": 2842.6,

        "hobbs_end": 2844.3,

        "landings": 0,

        "cycles": 0,

        "drops": 0,

        "gallons": 0,

        "available_hours": 0,

        "status_totals": {

            "off_available": 24,

            "off_schedule_mx": 0,

            "off_unscheduled_mx": 0,

            "off_contract_issue": 0,

            "on_available": 0,

            "on_scheduled_mx": 0,

            "on_unscheduled_mx": 0,

            "on_weather": 0

        },

        "flight_hours_rev": 0.8,

        "flight_hours_non_rev": 0.9,

        "flight_hours_total": 1.7,

        "aircraft_status": "Off Contract - Available",

        "maintenace_next_mx_event": "",

        "maintenace_next_mx_interval": ""

    },

    "N95LF": {

        "tail_number": "N95LF",

        "airport_code": "",

        "pilot": [

            "Joe Smith",

            "Jane Smith"

        ],

        "fleet": "Air Attack",

        "contract": "",

        "contract_contact": "",

        "hobbs_start": 5866.9,

        "hobbs_end": 5870.5,

        "landings": 0,

        "cycles": 0,

        "drops": 0,

        "gallons": 0,

        "available_hours": 0,

        "status_totals": {

            "off_available": 24,

            "off_schedule_mx": 0,

            "off_unscheduled_mx": 0,

            "off_contract_issue": 0,

            "on_available": 0,

            "on_scheduled_mx": 0,

            "on_unscheduled_mx": 0,

            "on_weather": 0

        },

        "flight_hours_rev": 3.6,

        "flight_hours_non_rev": 0,

        "flight_hours_total": 3.6,

        "aircraft_status": "Off Contract - Available",

        "maintenace_next_mx_event": "",

        "maintenace_next_mx_interval": ""

    },

There are quite a few more aircraft but the format is the same for them all.  I have been attempting to follow the advice here: 

https://powerusers.microsoft.com/t5/Building-Flows/Parse-JSON-Dynamic-Object-as-field-name/td-p/1346..., but I get an error on the JSON step: 

Unable to process template language expressions in action 'Compose_JSON' inputs at line '0' and column '0': 'The template language function 'JSON' parameter is not valid. The provided value '[{"N840G":....cannot be parsed: 'Invalid property identifier character: {. Path '[0].N840G', line 1, position 431.'

It doesn't seem to like the "status totals" object but I'm at a loss for how to handle this.   Below is my flow, and regex used is the same as the example posted, except I moved the first JSON conversion to a separate compose action, just for visualization:  

Compose Reformat = concat('[',replace(outputs('Compose_String'),'},', '}},{'),']')

Select = json(concat('{',replace(last(split(string(item()),'{')), '}}','}')))

Any help would be greatly appreciated! 

[edgonzales]

@BIT90 

Hey there.  Have you tried the Parse JSON step?  I dropped your sample data into a Compose, and then used a Parse JSON action and it seemed to like it ok.

Let me know.

-Ed

If you liked this reply, please give it a thumbs up! If this reply has answered your question or resolved your challenge, please consider marking it as a Solution. This helps other users find it more easily via search.

[BIT90]

I have tried that but then it creates multiple properties for reference.  Instead of being able reference outputs('tail_number'), it creates, for example, outputs('N840G')?['tail_number'].  It will do this for each tail number and I haven't found a workaround to make the 'N840g' section a wildcard, so I thought I'd try this method.  

[BIT90]

I see what's going wrong but I'm struggling to come up with a fix.

The 'Compose - Reformat' is removing the '{' for the nested object 'status_totals' which breaks everything else.  I've been playing with reinserting it or changing the expression, but so far nothing has worked. 

[edgonzales]

Weird, I didn't get that at all.  Are you clicking "Generate from sample" and then pasting your output JSON before clicking ok? 

[BIT90]

Yes, I am.  Honestly, I took this on thinking it would be as easy as getting the data, parsing it, then selecting my fields, but this is turning out to be a difficult flow to learn as I go.    

It doesn't always group the similar fields together, so here's a shot showing many 'pilot' variables: 

There's nothing to help distinguish one from the other during selection, but I have done a compose with all of the tail_numbers that were parsed.  They all take on the following format: 

I've tried using a wildcard for the path, which I've seen others do on PA Desktop while retrieving Excel files that have dates in the name, but I have had no luck with them.  Some formats I've tried are:

 body('parse_json')?[]?[tail_number]

body('parse_json')?[*]?[tail_number]

body('parse_json')?[%]?[tail_number]

If there is a way to have that path be wild,  I haven't had any luck.

[BIT90 · Accepted solution]

Alright I was able to get it working, although it is rather messy.  I had to add variables and compile the structure based on Array indexes.  

First, I have to initialize variables I'll use later: 

Then I query the API, start manipulating the results, and initialize a few more variables I'll need in my upcoming Do until Loop: 

Now I'll enter the Do until loop.  For some reason, the condition is skipped and I receive an error if I use a condition or number other than ArrayCount less than 3.  It was working fine with less than or equal to 1, but that broke when we added a few more aircraft and it ran 60 times, no matter how many objects were passed in. 

Here's the loop with the steps expanded: 

I'm still working on making it more efficient where I can and trying to sneak a condition in there so I'm not clearing the List and recreating it each iteration, but it at least removes the path and allows me to parse it.