Powershell how to extract data from JSON file based on one parameter

Question

I am trying to write a PowerShell script to extract values based on certain conditions from a very large JSON file. I have pasted a portion of the JSON file below and the PowerShell script I am trying to use, but I do not get any values for them. From the JSON file below how do I extract values for parameters like Projectid, Runbookid, Name when the object contains the following in the JSON file - Octopus.Action.Script.ScriptSource": "Inline".

Also as the data returned is very large, so I am trying to format it so that the output shows all the data. Here is the code I am trying:

Get-Content -Raw "./sample.json" |
 ConvertFrom-Json |
  Where-Object { 
      $_.Items.Steps.Actions.Properties.'Octopus.Action.Script.ScriptSource' -Contains "Inline"
  } | 
  Select-Object @{ n='Id'; e={ $_.Items.Id } }, 
  @{ n='RunbookId'; e={ $_.Items.RunbookId } }, 
  @{ n='Name'; e={ $_.Items.Steps.Name } } | Format-Table -Wrap -AutoSize

Portion of the JSON file:

    {
  "ItemType": "RunbookProcess",
  "TotalResults": 2449,
  "ItemsPerPage": 15000,
  "NumberOfPages": 1,
  "LastPageNumber": 0,
  "Items": [
    {
      "Id": "RunbookProcess-Runbooks-1001",
      "RunbookId": "Runbooks-1001",
      "ProjectId": "Projects-2082",
      "Steps": [
        {
          "Id": "7b1c3949-0f89-4365-ab5b-c336cf242a16",
          "Name": "Run a Script",
          "PackageRequirement": "LetOctopusDecide",
          "Properties": {
            "Octopus.Action.TargetRoles": "APIHost"
          },
          "Condition": "Success",
          "StartTrigger": "StartAfterPrevious",
          "Actions": [
            {
              "Id": "d1265330-bdc4-4e43-aaae-42e2b8475fdd",
              "Name": "Run a Script",
               "Container": {
                "Image": null,
                "FeedId": null
              },
              "WorkerPoolVariable": "",
              "Environments": [],
              "ExcludedEnvironments": [],
              "Channels": [],
              "TenantTags": [],
              "Packages": [
                {
                  "Id": "ffe772f1-5038-42b5-86a8-23e49045cd5d",
                  "Name": "",
                  "PackageId": "OctopusRunbooks",
                  "FeedId": "Feeds-1221",
                  "AcquisitionLocation": "Server",
                  "Properties": {
                    "SelectionMode": "immediate"
                  }
                }
              ],
              "Condition": "Success",
              "Properties": {
                "Octopus.Action.Script.ScriptSource": "Package"
             },
              "Links": {}
            }
          ]
        }
      ],
      "Version": 1,
      "LastSnapshotId": null,
      "SpaceId": "Spaces-63",
      "Links": {
        "Self": "/api/Spaces-63/projects/Projects-2082/runbookProcesses/RunbookProcess-Runbooks-1001"
}
    },
    {
      "Id": "RunbookProcess-Runbooks-1673-s-12-4WU2R",
      "RunbookId": "Runbooks-1673",
      "ProjectId": "Projects-3223",
      "Steps": [
        {
          "Id": "a28bb410-9b09-4168-8855-f5a27da88455",
          "Name": "Run a Script",
          "PackageRequirement": "LetOctopusDecide",
          "Properties": {},
          "Condition": "Success",
          "StartTrigger": "StartAfterPrevious",
          "Actions": [
            {
              "Id": "7547d995-510c-45fb-b08a-5f37672c7948",
              "Name": "Run a Script",
              "ActionType": "Octopus.Script",
              "Container": {
                "Image": null,
                "FeedId": null
              },
              "WorkerPoolVariable": null,
              "Environments": [],
              "ExcludedEnvironments": [],
              "Channels": [],
              "TenantTags": [],
              "Packages": [
                {
                  "Id": "76399f70-bf6a-4ec2-bca3-90f2038d65ed",
                  "Name": "Fusion.Ops.SeiDataGenerator",
                  "PackageId": "Fusion.Ops.SeiDataGenerator",
                  "FeedId": "Feeds-1221",
                  "AcquisitionLocation": "Server",
                  "Properties": {
                    "Extract": "True",
                    "SelectionMode": "immediate"
                  }
                }
              ],
              "Condition": "Success",
              "Properties": {
                "Octopus.Action.RunOnServer": "true",
                "Octopus.Action.Script.ScriptSource": "Inline"
               },
              "Links": {}
            }
          ]
        }
      ],
      "Version": 12,
      "LastSnapshotId": null,
      "SpaceId": "Spaces-63",
      "Links": {
        "Self": "/api/Spaces-63/projects/Projects-3223/runbookProcesses/RunbookProcess-Runbooks-1673-s-12-4WU2R"
      }
    },
    {
      "Id": "RunbookProcess-Runbooks-1673-s-14-HSYYX",
      "RunbookId": "Runbooks-1673",
      "ProjectId": "Projects-3223",
      "Steps": [
        {
          "Id": "a28bb410-9b09-4168-8855-f5a27da88455",
          "Name": "Run a Script",
          "PackageRequirement": "LetOctopusDecide",
          "Properties": {},
          "Condition": "Success",
          "StartTrigger": "StartAfterPrevious",
          "Actions": [
            {
              "Id": "7547d995-510c-45fb-b08a-5f37672c7948",
              "Name": "Run a Script",
              "Container": {
                "Image": null,
                "FeedId": null
              },
              "WorkerPoolVariable": null,
              "Environments": [],
              "ExcludedEnvironments": [],
              "Channels": [],
              "TenantTags": [],
              "Packages": [
                {
                  "Id": "76399f70-bf6a-4ec2-bca3-90f2038d65ed",
                  "Name": "Fusion.Ops.SeiDataGenerator",
                  "PackageId": "Fusion.Ops.SeiDataGenerator",
                  "FeedId": "Feeds-1221",
                  "AcquisitionLocation": "Server",
                  "Properties": {
                    "Extract": "True",
                    "SelectionMode": "immediate"
                  }
                }
              ],
              "Condition": "Success",
              "Properties": {
                "Octopus.Action.RunOnServer": "true",
                "Octopus.Action.Script.ScriptSource": "Inline"
               },
              "Links": {}
            }
          ]
        }
      ],
      "Version": 14,
      "LastSnapshotId": null,
      "SpaceId": "Spaces-63",
      "Links": {
        "Self": "/api/Spaces-63/projects/Projects-3223/runbookProcesses/RunbookProcess-Runbooks-1673-s-14-HSYYX"
       }
    },
    {
      "Id": "RunbookProcess-Runbooks-998",
      "RunbookId": "Runbooks-998",
      "ProjectId": "Projects-1821",
      "Steps": [
        {
          "Id": "711c0357-93ee-4106-bbed-0392f9ddc262",
          "Name": "Run a Script",
          "PackageRequirement": "LetOctopusDecide",
          "Properties": {
            "Octopus.Action.TargetRoles": "IAM-Web"
          },
          "Condition": "Success",
          "StartTrigger": "StartAfterPrevious",
          "Actions": [
            {
              "Id": "609046b8-7269-4238-8a2e-4b7d77b8f9a2",
              "Name": "Run a Script",
              "Container": {
                "Image": null,
                "FeedId": null
              },
              "WorkerPoolVariable": "",
              "Environments": [],
              "ExcludedEnvironments": [],
              "Channels": [],
              "TenantTags": [],
              "Packages": [
                {
                  "Id": "2a13e500-28e6-46f2-ac7d-3f19d6476e81",
                  "Name": "",
                  "PackageId": "OctopusRunbooks",
                  "FeedId": "Feeds-1221",
                  "AcquisitionLocation": "Server",
                  "Properties": {
                    "SelectionMode": "immediate"
                  }
                }
              ],
              "Condition": "Success",
              "Properties": {
                "Octopus.Action.Script.ScriptSource": "Package"
                },
              "Links": {}
            }
          ]
        }
      ],
      "Version": 1,
      "LastSnapshotId": null,
      "SpaceId": "Spaces-63",
      "Links": {
        "Self": "/api/Spaces-63/projects/Projects-1821/runbookProcesses/RunbookProcess-Runbooks-998"
       }
    }
  ],
  "Links": {
    "Self": "/api/Spaces-63/runbookProcesses?skip=0&take=15000",
    "Page.Last": "/api/Spaces-63/runbookProcesses?skip=0&take=15000"
  }
}

You're close -- you just need to put the filter on the right level, that is, on the level you want to ultimately select the properties. So (... | ConvertFrom-Json).Items.Where{$_.Steps.Actions.Properties.'Octopus.Action.Script.ScriptSource' -Contains "Inline" } | Select-Object @{ n='Id'; e={ $_.Id } }, @{ n='RunbookId'; e={ $_.RunbookId } }, @{ n='Name'; e={ $_.Name } }. If that doesn't yield desired output, providing that would be useful (Items do not appear to have a Name, for example, Actions do -- but those don't have RunbookIds). — Jeroen Mostert
– Jeroen Mostert, Commented Aug 30, 2022 at 19:31
@JeroenMostert. Thanks that seems to get the Id and RunbookId, but the Name is not coming through. I am trying to extract the Name under Steps — user2444271
– user2444271, Commented Aug 30, 2022 at 19:46

Chad Baldwin · Accepted Answer · 2022-08-30 20:08:39Z

1

This is assuming that each Item only ever contains one Step, because it will return the Name as a string. But if it contains more than one Step, then it will return the Name as an array of strings.

Get-Content './sample.json' -Raw | ConvertFrom-Json |
  ForEach-Object Items |
  Where-Object { $_.Steps.Actions.Properties.'Octopus.Action.Script.ScriptSource' -Contains 'Inline' } |
  ForEach-Object {
    [pscustomobject]@{
      Id = $_.Id
      RunbookId = $_.RunbookId
      Name = $_.Steps.Name
    }
  }

More questions pop up if you need to deal with multiple steps, because it would be handled in one of two ways...You duplicate the Id/RunbookId for each step, or you return the step names as an array of strings.

edited Aug 30, 2022 at 20:08

answered Aug 30, 2022 at 20:03

Chad Baldwin

2,68222 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Powershell how to extract data from JSON file based on one parameter

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related