powershell parse Json file with wildcard in the key name

Question

I use powershell to loop through multiple Json files obtained by the REST API. But the difficulty is that some nodes are not fixed, they are different in different json bodies returned, wildcards seem to have no effect.

My test Json file Workflow_Info like:

{
   "id": "/subscriptions/fcxxxx7/resourceGroups/xxxxxx/providers/Microsoft.Web/sites/xxxx/workflows/Test_email",
   "name": "xxxxxxxxx/Test_email",
   "type": "Microsoft.Web/sites/workflows",
   "kind": "Stateful",
   "location": "East Asia",
   "properties": {
      "files": {
         "Test_email/workflow.json": {
            "definition": {
               "$schema": "https://schema.management.azure.com/providers/Microsoft.Logic/schemas/2016-06-01/workflowdefinition.json#",
               "actions": {
                  "Send_an_email_from_a_shared_mailbox_(V2)": {
                     "inputs": {
                        "host": {
                           "connection": {
                              "referenceName": "TheValueIwant"
                           }
                        },
                        "method": "post",
                        "path": "/v2/SharedMailbox/Mail"
                     },
                     "runAfter": {},
                     "type": "ApiConnection"
                  }
               },
               "contentVersion": "1.0.0.0",
               "outputs": {}
            },
            "kind": "Stateful"
         }
      }
   }
}

And another Json body is:

{
   "id": "/subscriptions/xxx/resourceGroups/xxxx/providers/Microsoft.Web/sites/xxx/workflows/test_email",
   "name": "xxx/test_email",
   "type": "Microsoft.Web/sites/workflows",
   "kind": "Stateful",
   "location": "East Asia",
   "properties": {
      "files": {
         "Test_email/workflow.json": {
            "definition": {
               "$schema": "https://schema.management.azure.com/providers/Microsoft.Logic/schemas/2016-06-01/workflowdefinition.json#",
               "actions": {
                  "For_each": {
                     "actions": {},
                     "foreach": "@triggerBody()?['attachments']",
                     "runAfter": {},
                     "type": "Foreach"
                  }
               },
               "contentVersion": "1.0.0.1",
               "outputs": {},
               "triggers": {
                  "When_a_new_email_arrives_in_a_shared_mailbox_(V2)": {
                     "inputs": {
                        "host": {
                           "connection": {
                              "referenceName": "office365"
                           }
                        },
                        "method": "get"
                     },
                     "recurrence": {},
                     "splitOn": "@triggerBody()?['value']",
                     "type": "ApiConnection"
                  }
               }
            },
            "kind": "Stateful"
         }
      },
      "flowState": "Disabled",
      "health": {
         "state": "Healthy"
      }
   }
}

What I want to get is the value of the node referenceName.

But the node it has different value in different json, for the first Json, I could get it by below:

$referenceName= $Workflow_Info.properties.files."Test_email/workflow.json".definition.actions.Send_an_email_from_a_shared_mailbox_(V2).inputs.host.connection.referenceName

But for the second Json, I have to change the node to:

$referenceName= $Workflow_Info.properties.files."Test_email/workflow.json".definition.triggers.When_a_new_email_arrives_in_a_shared_mailbox_(V2).inputs.host.connection.referenceName

2 of these nodes are different： one is .actions.Send_an_email_from_a_shared_mailbox_(V2). and another is .triggers.When_a_new_email_arrives_in_a_shared_mailbox_(V2)..

How to use a common syntax to get the final node? I can't specify the node path for each json file individually, because I have hundreds of json files.

I tried to use wildcard *, but not work.

$referenceName= $Workflow_Info.properties.files."Test_email/workflow.json".definition.actions.'*'.inputs.host.connection.referenceName

See: [Recursive function to iterate through JSON Data](https://stackoverflow.com/a/74337640/1701026). `($Workflow_Info |Get-Node -Where { $_.Name -eq 'referenceName' }).Value`. The nice thing of this solution is that it returns the reference of the node so that you also might decide to *replace* the concerned properties (`Value`). — iRon, Apr 25 '23 at 06:21

Santiago Squarzon · Answer 1 · 2023-04-25T04:15:26.190

Assuming the property you're looking for always has the name connection and it is always nested in the definition property, the following should work for you. Using the examples in question and assuming they're stored in $json1 and $json2, the results would be TheValueIwant and office365.

In theory, if the Jsons always have the same structure, you could just enqueue $json instead of $json.properties.files.'Test_email/workflow.json'.definition and it should find the value you're looking for but its worth noting this code is not handling possible arrays.

$queue = [System.Collections.Generic.Queue[object]]::new()
# enqueue the definition property of the json
$queue.Enqueue($json.properties.files.'Test_email/workflow.json'.definition)

while($queue.Count) {
    $item = $queue.Dequeue()
    # enumerate all properties on this object
    foreach($property in $item.PSObject.Properties) {
        # if this property name is equal to 'connection'
        if($property.Name -eq 'connection') {
            # and the value of this property is an object with
            # a property name 'referenceName'
            if($value = $property.Value.referenceName) {
                # then output this value
                $value
                # and break this loop, no need to keep checking
                break
            }
        }
        # else, enqueue the value of this property
        $queue.Enqueue($property.Value)
    }
}

Thanks for your input. I have put the function of this part in a foreach loop, so I only need to process one json body each time. I tried your code and it seems to be stuck in an endless loop. Can you help me improve it? Sorry I'm not very familiar with powershell so I can't modify your code myself — Joy, Apr 25 '23 at 06:55
@Joy i don't see how this code could create an infinite loop. As you can see it can resolve the json in your question in less than a second. i also don't see room to improve it. — Santiago Squarzon, Apr 25 '23 at 12:09

powershell parse Json file with wildcard in the key name

1 Answers1