如何用PowerShell按指定条件从大型JSON文件提取数据？

需求 #

从大型Octopus Runbook Process JSON文件中，提取所有包含Octopus.Action.Script.ScriptSource值为Inline的条目，获取对应的ProjectId、RunbookId以及步骤的Name字段，并格式化输出。

原脚本（无返回结果） #

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

JSON片段 #

{"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"}},...]

问题分析 #

原脚本失败的核心原因：

• 未展开Items数组：管道中处理的是顶层JSON对象，而非单个Item条目，导致属性引用逻辑错误
• 筛选条件未针对数组层级正确遍历：$_.Items.Steps.Actions.Properties是多层数组结构，直接用-Contains无法正确匹配

修正后的脚本 #

Get-Content -Raw "./sample.json" |
    ConvertFrom-Json |
    Select-Object -ExpandProperty Items |
    Where-Object {
        # 检查当前Item的所有Steps下的Actions中是否存在Inline脚本源
        $_.Steps.Actions.Properties.'Octopus.Action.Script.ScriptSource' -contains 'Inline'
    } |
    Select-Object ProjectId, RunbookId, @{n='StepName'; e={$_.Steps.Name}} |
    Format-Table -Wrap -AutoSize

关键修改说明 #

1. 展开Items数组：使用Select-Object -ExpandProperty Items将顶层对象中的Items数组拆分为单个Item对象，让后续操作直接针对每个条目
2. 简化筛选逻辑：由于现在处理的是单个Item，PowerShell会自动遍历Steps和Actions数组，用-contains即可判断是否存在目标值
3. 正确引用属性：直接选择Item本身的ProjectId、RunbookId，步骤名称通过计算属性获取$_.Steps.Name

输出示例 #

针对提供的JSON片段，脚本会返回如下结果：

ProjectId    RunbookId    StepName
----------   ----------   ----------
Projects-3223 Runbooks-1673 Run a Script

内容的提问来源于stack exchange，提问作者user2444271