Having issues trying to access nested data from a parsed JSON

[mikraphne]

I am fairly new to really digging into PowerAutomate, but I have tried so many different methods and cannot seem to get this one figured out. I have a JSON that is coming in from an HTTP Recieve, then being parsed. I can easily access most data and create variables and throw the data into other areas where needed. However, I am having issues accessing a few pieces of data that is tested within my JSON. I've tried a ton of different expressions to get this, as well as trying to set up a For each, and I think I am out of depth on this one for the time being. If anyone can help take a look, I'd greatly appreciate it!

I am trying to take the ouput of our phishing submission system (KnowBe4), then grab specific data to send over to our help desk software for metrics. If I can grab Subject and Message-ID, I can store it in Log Analytics to be cross referenced later, when the ticket is to be closed (I am trying to make it so we can open/close tickets without having to go to the help desk system). The field for "trigger_name" is what I will tell the system who to close the ticket as. Lastly, the "field": "clean", "value": "0.00" (near the bottom of the JSON) both are connected and I would rather it just store the value as "clean":"value". This is more for adding reference in the ticket body for fanciness, so is not totally needed.

Also, I don't know if it will help, but most of these fields will always be static and will be there for every message, outside of the "trigger_name". Unfortunately, KnowBe4 doesn't just put 'Closed by' for me to go off of. Instead, they use this "trigger_name" all over the place in the history section of a ticket, which will vary in size. It is always "null" unless there is human interaction that closed the ticket, if that makes sense.

{
    "body": {
        "headers": [
            {
                "sha1": "08f96a6ac4300",
                "headers": [
                    {
                        "Subject": "You missed a VM= | Transcription Available Play Now 02min57se=  3pm|Friday-February-2024 12:21 PM"
                    },

                    {
                        "Message-Id": "<170811486211.6420.16697328371498069029@takahashibussho.co.jp>"
                    },
                    {
                        "Content-Transfer-Encoding": "7bit"
                    }
                ],
                "filename": "rawHeaders.txt",
                "sha256": "89525511f796c492eb",
            }
        ],
        "addresses": {
            "to": [
                "jeom"
            ]
        },
        "links": [],
        "phishml": {
            "confidence_spam": "0.0002689999237190932",
            "confidence_clean": "0.00001319524653808912",
            "category": "threat",
            "confidence_threat": "0.9997478127479553"
        },
        "history": [
            {
                "date": "2024-02-16T16:08:21-05:00",
                "events": {
                    "phishrip_started": {
                        "queried_fields": [
                            "from",
                            "subject"
                        ],
                        "time_range": {
                            "start_time": "2024-02-15T16:08:15-05:00",
                            "end_time": "2024-02-16 21:08:15 +0000"
                        },
                        "quarantine": "true",
                        "id": "7dd22796-c400-4b9f-9c74-3f198272a36d",
                        "status": "processing"
                    }
                },
                "causer_name": "Threat Email Notification for Reporter"
            },
            {
                "date": "2024-02-16T16:08:15-05:00",
                "trigger_name": "One of three different names here",
                "causer_type": "Action",
                "event_type": "other",
                "trigger_type": "User",
                "events": {
                    "emails": [
                        {
                            "action_email_id": "c4ba53c9-8932-40a7-a347-f0cb74d04f15",
                            "to": [
                                ""
                            ],
                            "email": "Threat Email Notification for Reporter",
                            "status": null
                        }
                    ],
                    "changed_fields": {
                        "action_status": [
                            "received",
                            "resolved"
                        ]
                    }
                },
                "causer_name": "Threat Email Notification for Reporter"
            },
            {
                "date": "2024-02-16T16:07:56-05:00",
                "trigger_name": null,
                "causer_type": "User",
                "event_type": "other",
                "trigger_type": null,
                "events": {
                    "changed_fields": {
                        "viewed": [
                            false,
                            true
                        ]
                    }
                },
                "causer_name": "NAME HERE"
            },
            {
                "date": "2024-02-16T15:22:35-05:00",
                "trigger_name": null,
                "causer_type": "Action",
                "event_type": "other",
                "trigger_type": null,
                "events": {
                    "emails": [
                        {
                            "action_email_id": "0ebd75cc-23ea-4f91-8459-023f81e6c891",
                            "to": [
                                "",
                            ],
                            "email": "Threat Email Notification for Admin",
                            "status": null
                        }
                    ],
                    "tags": {
                        "added": [
                            "MANUAL"
                        ]
                    },
                    "changed_fields": {
                        "severity": [
                            "unknown_severity",
                            "critical"
                        ],
                        "category": [
                            "unknown",
                            "threat"
                        ]
                    }
                },
                "causer_name": "Threat Notification for Admin"
            },
            {
                "date": "2024-02-16T15:22:34-05:00",
                "trigger_name": null,
                "causer_type": "Rule",
                "event_type": "other",
                "trigger_type": null,
                "events": {
                    "tags": {
                        "added": [
                            "KB4:SPF_PASS"
                        ]
                    },
                    "changed_fields": {
                        "pipeline_status": [
                            "processing",
                            "processed"
                        ]
                    }
                },
                "causer_name": "KB4:URGENCY"
            },
            {
                "date": "2024-02-16T15:22:26-05:00",
                "trigger_name": null,
                "causer_type": "Intert",
                "event_type": "other",
                "trigger_type": null,
                "events": {
                    "report": {
                        "name": "VirusTotal",
                        "results": [
                            {
                                "field": "attachment",
                                "value": "JPQN.png"
                            },
                            {
                                "field": "autoscan",
                                "value": "SHA-256 not found."
                            }
                        ]
                    },
                    "tags": {
                        "removed": [
                            "VT_PENDING"
                        ],
                        "added": [
                            "VT_HASH_NOT_FOUND"
                        ]
                    }
                },
                "causer_name": "VirusTotal"
            },
            {
                "date": "2024-02-16T15:22:22-05:00",
                "trigger_name": null,
                "causer_type": "Integrations::PhishMl::Report",
                "event_type": "other",
                "trigger_type": null,
                "events": {
                    "report": {
                        "name": "Phish ML",
                        "results": [
                            {
                                "field": "clean",
                                "value": "0.00"
                            },
                            {
                                "field": "spam",
                                "value": "0.03"
                            },
                            {
                                "field": "threat",
                                "value": "99.97"
                            }
                        ]
                    },
                    "tags": {
                        "added": [
                            "PML:THREAT"
                        ]
                    }
                },
                "causer_name": "Phish ML"
            },
            {
                "date": "2024-02-16T15:22:10-05:00",
                "events": {
                    "part": {

                        "name": "JPQN.png"
                    }
                },
                "causer_name": null
            },
            {
                "date": "2024-02-16T15:22:10-05:00",
                "causer_name": null
            }
        ],
        "bad_links": [],
        "tags": [
            "SUCCESS_TAG"
        ]
    }
}

[cchannon]

Well, that's a lot of questions all rolled into one, so I don't think I will be able to answer all of them at once for you but let me try to point you in the right direction.

Assuming you are using the ParseJSON Action to convert this JSON into a deserialized Power Automate object, then accessing its members is as simple as walking the JSON tree; even if sometimes the editor window doesn't show you the members.

So, for example, to access Subject and Message-Id in Headers[], you will just want to ApplyToEach loop over

MyJSON.Body.Headers

then check each item for the key of "Subject" or "Message-Id" and write those off into a variable or wherever you want to store them.

Likewise, to access Trigger-name you would do a ApplyToEach loop over 

MyJSON.Body.History

and, I assume, append each one to an array variable so you can extract them one at a time.

And lastly, to access those field/value objects you would nest ApplyToEach loops. Start with a Loop on MyJSON.Body.History, then create another loop inside there for items('Apply_to_each') (or whatever the generated syntax is for your iterable) so that inner loop would be on something like 

items('Apply_to_each').events.report.results

[sandeepstw]

Hi @mikraphne ,

You have already parsed the incoming JSON data.

Ensure that you have used the 'Parse JSON' action to convert the raw JSON into a format that Power Automate can handle.

To extract specific values from nested data, use the 'Compose' action to create variables for the desired fields like Subject, Message-ID, trigger_name etc.

In the 'Compose' action, use expressions to access the nested properties within your parsed JSON.

For Subject:
outputs('Parse_JSON')['Subject']
For Message-ID:
outputs('Parse_JSON')['Message-ID']
For trigger_name:
outputs('Parse_JSON')['trigger_name']

Refer this youtube video - https://www.youtube.com/watch?v=sqx8in2PJlA

Please give kudos and mark as solution if it helps.  

Thanks, 

Sandeep Mishra 

[mikraphne]

I guarantee this is all user error on my part, but I keep trying to do this, and set a variable for Subject.

Whenever it gets to that step it throws this error:

I know I have tried this process a few times with For Each, since that seemed to be the suggested thing to do, but I feel like I am setting something incorrectly. It is also a little confusing because there is a "headers" under "headers"

Is this the one I need, or do I need another For Each loop to go one layer down?

Sorry, I know this is probably super simple for everyone else, but I think I have tried so many different things that I am kinda losing track at this point haha

[mikraphne]

I tried using outputs('Parse_JSON')['Subject'] and still have no luck. Is it because it is nested in another 'headers'?

When it runs, I get:

I even tried what the video said and haven't had any luck.

I am simply getting JSON from a HTTP Connector, then immediately running Parse_JSON on the body of the HTTP connector:

[sandeepstw · Accepted solution]

HI,

This is the path to get subject as string variable - 

{

  "type": "InitializeVariable",

  "inputs": {

    "variables": [

      {

        "name": "varSubject",

        "type": "string",

        "value": "@{variables('varObject')['body']['headers'][0]['headers'][0]['Subject']}"

      }

    ]

  },

  "runAfter": {

    "Initialize_variable": [

      "Succeeded"

    ]

  }

}

Please give kudos and mark as solution if it helps.  

Thanks, 

Sandeep Mishra

[mikraphne]

To make it even more confusing, I got it finally running, but then sent another payload over to it and it failed. It is coming from a system I don't have any access to changing, and I wish they would just put in all the fields with "" for the values that are not there. Instead, the values of "Subject" and "Message-ID" end up moving around. I cut a large portion of the data out for my example, but at one point, I had "Subject" working at [17], then the next run with a different payload ended up being [9]. However, these two fields are guaranteed to always be in the array. I unfortunately can't format them to be in a static location though.

[sandeepstw]

hi @mikraphne ,

Step 1- put output in a variable "varObject", then create another variable and use this expression to get the subject. that's it 

variables('varObject')['body']['headers'][0]['headers'][0]['Subject']

[mikraphne]

I feel like this is as close as I have gotten! The JSON above is the raw, so the body part is no longer part of it after I parse it. So, I tried it without [body] and it tells me that Subject does not exist, and told me that a different property "Received" was available, since in this run, it was the first property in the array instead of Subject (which, in this run was actually [20], but that will be different every time..

So, I played around with it and made it:

variables('varObject')['headers']?[0]?['headers']?[0]?['Subject']

Since, I figure I just want it to grab the first instance of headers, then the first instance of Subject. However, the value is coming up blank. It is at least marking the step as successful, instead of failing at it.. Not sure if this is a good or a bad sign at this point.