how to extract certain objects from Json file when specific object is matched

Question

I need a PowerShell script to extract values based on certain conditions from a JSON file. From the JSON file below how do I extract values for parameters like Project id, Runbookid, Name when the object contains the following in the JSON file - Octopus.Action.Script.ScriptSource": "Inline". Basically need to filter the JSON file and extract certain values only.

I update the JSON where I added the first block with "Items"

   

     {
      "Id": "RunbookProcess-Runbooks-1341",
      "RunbookId": "Runbooks-1341",
      "ProjectId": "Projects-2772",
      "Steps": [
        {
          "Id": "67a30c29-5c44-49d1-b939-1e2d1906dbce",
          "Name": "Run a Script",
          "PackageRequirement": "LetOctopusDecide",
          "Properties": {},
          "Condition": "Success",
          "StartTrigger": "StartAfterPrevious",
          "Actions": [
            {
              "Id": "cdde4e76-9e96-4740-bc38-0cd85bf0d4ae",
              "Name": "Run a Script",
              "ActionType": "Octopus.Script",
              "WorkerPoolId": "WorkerPools-142",
              "Container": {
                "Image": null,
                "FeedId": null
              },
              "WorkerPoolVariable": null,
              "Environments": [],
              "ExcludedEnvironments": [
                "Environments-781",
                "Environments-704",
                "Environments-1001"
              ],
              "Channels": [],
              "TenantTags": [],
              "Packages": [],
              "Condition": "Success",
              "Properties": {
                "Octopus.Action.RunOnServer": "true",
                "Octopus.Action.Script.ScriptSource": "Inline",
               },
              "Links": {}
            }
          ]
        }
      ],
      "Version": 2,
      "LastSnapshotId": null,
      "SpaceId": "Spaces-63",
      "Links": {
        "Self": "/api/Spaces-63/projects/Projects-2772/runbookProcesses/RunbookProcess-Runbooks-1341",
        "OwnerRunbookRunTemplate": "/api/Spaces-63/projects/Projects-2772/runbooks/Runbooks-1341/runbookRunTemplate"
      }
    },
    {
      "Id": "RunbookProcess-Runbooks-1342",
      "RunbookId": "Runbooks-1342",
      "ProjectId": "Projects-2772",
      "Steps": [
        {
          "Id": "9b6ca528-1141-447c-a696-aba2798995a2",
          "Name": "Run a Script2",
          "PackageRequirement": "LetOctopusDecide",
          "Properties": {},
          "Condition": "Success",
          "StartTrigger": "StartAfterPrevious",
          "Actions": [
            {
              "Id": "6600ce7c-2fa8-4802-8bd1-def4a81f7fa3",
              "Name": "Run a Script2",
              "ActionType": "Octopus.Script",
              "WorkerPoolId": "WorkerPools-142",
              "Container": {
                "Image": null,
                "FeedId": null
              },
              "WorkerPoolVariable": null,
              "Environments": [],
              "ExcludedEnvironments": [],
              "Channels": [],
              "TenantTags": [],
              "Packages": [],
              "Condition": "Success",
              "Properties": {
                "Octopus.Action.RunOnServer": "true",
                "Octopus.Action.Script.ScriptSource": "Inline",
                "Octopus.Action.Script.Syntax": "PowerShell",
                "Octopus.Action.Script.ScriptBody": "dsa"
              },
              "Links": {}
            }
          ]
        }
      ],
      "Version": 1,
      "LastSnapshotId": null,
      "SpaceId": "Spaces-63",
      "Links": {
        "Self": "/api/Spaces-63/projects/Projects-2772/runbookProcesses/RunbookProcess-Runbooks-1342",
        "OwnerRunbookRunTemplate": "/api/Spaces-63/projects/Projects-2772/runbooks/Runbooks-1342/runbookRunTemplate"
      }
    },
    {
      "Id": "RunbookProcess-Runbooks-1361",
      "RunbookId": "Runbooks-1361",
      "ProjectId": "Projects-2901",
      "Steps": [
        {
          "Id": "af61bbe8-e626-492e-855f-9c713e0812cf",
          "Name": "Run a Service with Logs - Runbook",
          "PackageRequirement": "LetOctopusDecide",
          "Properties": {
            "Octopus.Action.TargetRoles": "Enable-Services"
          },
          "Condition": "Success",
          "StartTrigger": "StartAfterPrevious",
          "Actions": [
            {
              "Id": "361c15ef-69b0-4edc-975f-4fae5a37534f",
              "Name": "Run a Service with Logs - Runbook",
              "ActionType": "Octopus.Script",
              "Container": {
                "Image": null,
                "FeedId": null
              },
              "WorkerPoolVariable": "",
              "Environments": [],
              "ExcludedEnvironments": [],
              "Channels": [],
              "TenantTags": [],
              "Packages": [
                {
                  "Id": "2da83219-8c83-4698-acea-9b22df978961",
                  "Name": "",
                  "PackageId": "OctopusRunbooks",
                  "FeedId": "Feeds-1221",
                  "AcquisitionLocation": "Server",
                  "Properties": {
                    "SelectionMode": "immediate"
                  }
                }
              ],
              "Condition": "Success",
              "Properties": {
                "Octopus.Action.Script.ScriptSource": "Package",
                "Octopus.Action.Script.ScriptFileName": "Service-RunExeWithLogs.ps1",
               
              },
              "Links": {}
            }
          ]
        }
      ],
      "Version": 0,
      "LastSnapshotId": null,
      "SpaceId": "Spaces-63",
      "Links": {
        "Self": "/api/Spaces-63/projects/Projects-2901/runbookProcesses/RunbookProcess-Runbooks-1361",
        "OwnerRunbookSnapshotTemplate": "/api/Spaces-63/projects/Projects-2901/runbooks/Runbooks-1361/runbookSnapshotTemplate",
        "OwnerRunbookRunTemplate": "/api/Spaces-63/projects/Projects-2901/runbooks/Runbooks-1361/runbookRunTemplate"
      }
    }

mklement0 · Accepted Answer · 2022-08-28 10:28:29Z

1

ConvertFrom-Json parses JSON text into an object graph (nested [pscustomobject] instances).

You can therefore use dot notation, using ., the member-access operator, to drill down into this graph, in order to extract the data of interest, using Where-Object to do the filtering and Select-Object to extract the properties of interest, including the use of calculated properties:

Get-Content -Raw file.json |
  ConvertFrom-Json |
  Where-Object { 
    $_.Steps.Actions.Properties.'Octopus.Action.Script.ScriptSource' -contains 'InLine' 
  } | 
  Select-Object @{ n='Id'; e={ $_.Steps.Id } }, 
                RunbookId, 
                @{ n='Name'; e={ $_.Steps.Name } }

Note the need to quote the property name Octopus.Action.Script.ScriptSource to ensure that it is recognized as a single name.

Output with your sample JSON file:

Id                                   RunbookId     Name
--                                   ---------     ----
67a30c29-5c44-49d1-b939-1e2d1906dbce Runbooks-1341 Run a Script
9b6ca528-1141-447c-a696-aba2798995a2 Runbooks-1342 Run a Script2

edited Aug 28, 2022 at 10:28

answered Aug 28, 2022 at 2:05

mklement0

452k68 gold badges727 silver badges986 bronze badges

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

8 Comments

user2444271 Over a year ago

Hi, Thank you for your help. I had actually removed the top part of the JSON to minimize the code from a very large JSON file. I added another object Item which I removed from my JSON file earlier. I have updated my code as under. I do not see any output though.

user2444271 Over a year ago

$data=Get-Content -Raw "./input.json" | ConvertFrom-Json | Where-Object { $_Items.Steps.Actions.Properties.'Octopus.Action.Script.ScriptSource' -contains 'InLine' } | Select-Object @{ n='Id'; e={ $_Items.Steps.Id } }, RunbookId, @{ n='Name'; e={ $_.Items.Steps.Name } } Write-Host $data

user2444271 Over a year ago

I updated the JSON where I added the first block with "Items"

user2444271 Over a year ago

Hi, I went through your answer again and worked out your method. It works perfectly.

user2444271 Over a year ago

Posted new question here.. if you can help please! @mklement0 stackoverflow.com/q/73522533/2444271

|

Collectives™ on Stack Overflow

how to extract certain objects from Json file when specific object is matched

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related