Trouble pasring nested values from json files using powershell-CodePudding

Trying to pull the signing_certificate_id field from the below json snippet, but unfortunately, the data is not making it into the excel export. The other info is making it into the csv. (there are cases where these code blocks exist twice in the same json to represent a separate environment.

Json Snippet:

{
  "sfg_ping::production::standard_sp_connections": [
    {
      "name": "hhhh",
      "entity_id": "https://hhhh.hhhh.com",
      "contact_info": "[email protected]",
      "sp_endpoint": "https://hhhh.hhhh.com/",
      "sso_initialization_and_signature_policy": {
        "sign_assertion_or_response": "assertion",
        "sp_trust_model": "unanchored",
        "signing_certificate_id": "gggggg",
        "sp_initiated": {
          "require_signed_authn_requests": false
        }
      },

Script being used:

Get-ChildItem "C:\Users\hhhh\appdev\targetfolder-json2" -Filter *.json -PipelineVariable file | ForEach-Object {
    (Get-Content $_.pspath -Raw | ConvertFrom-Json).PSObject.Properties.Value | Select-Object @(
        @{ l = 'filename'; e = { $file.Name }}, 'Name','contact_info', 'signing_certificate_id'
    )
} | Export-Csv path\to\myexport.csv -NoTypeInformation

CodePudding user response：

You need to create a calculated property for getting the nested JSON property, similar to what you did for filename:

Get-ChildItem "C:\Users\hhhh\appdev\targetfolder-json2" -Filter *.json -PipelineVariable file | ForEach-Object {
    (Get-Content $_.pspath -Raw | ConvertFrom-Json).PSObject.Properties.Value | Select-Object @(
        @{ l = 'filename'; e = { $file.Name }}, 'Name','contact_info',
        @{ l = 'signing_certificate_id'; e = { $_.sso_initialization_and_signature_policy.signing_certificate_id }}
    )
} | Export-Csv path\to\myexport.csv -NoTypeInformation