Parsing JSON Data Part 2

Question

I asked a previous question, and received a brilliant response from mklement0, so thank you for that.

The JSON is:-

{
  "results": [
    {
      "id": 1,
      "first_name": "Jeanette",
      "last_name": "Penddreth",
      "email": "jpenddreth0@census.gov",
      "gender": "Female",
      "ip_address": "26.58.193.2",
      "serverlist": [
        {
          "myval1": "testdata1",
          "myval2": "testdata2",
          "myval3": "testdata3"
        }
      ],
      "aitlist": [
        {
          "ait1": "aitdata1",
          "ait2": "aitdata2"
        }
      ]
    },
    {
      "id": 2,
      "first_name": "Giavani",
      "last_name": "Frediani",
      "email": "gfrediani1@senate.gov",
      "gender": "Male",
      "ip_address": "229.179.4.212",
      "serverlist": [
        {
          "myval1": "testdata5",
          "myval2": "testdata6",
          "myval3": "testdata7"
        },
        {
          "myval1": "testdata9",
          "myval2": "testdata10",
          "myval3": "testdata11"
        }
      ],
      "aitlist": [
        {
          "ait1": "aitdata3",
          "ait2": "aitdata4"
        }
      ]
    },
    {
      "id": 3,
      "first_name": "Noell",
      "last_name": "Bea",
      "email": "nbea2@imageshack.us",
      "gender": "Female",
      "ip_address": "180.66.162.255"
    },
    {
      "id": 4,
      "first_name": "Willard",
      "last_name": "Valek",
      "email": "wvalek3@vk.com",
      "gender": "Male",
      "ip_address": "67.76.188.26"
    }
   ]
}

The final piece in the jigsw was that I need to output all the items in the square brackets in the JSON file, for each 'record' together, which is exactly like using the following code would output the data:

(Get-Content -Raw C:\Temp\JsonFile.json | ConvertFrom-Json).Results | Out-GridView

The final output would then look like this:

1,"Jeanette","Penddreth","jpenddreth0@census.gov","Female","26.58.193.2","{@{myval1=testdata1; myval2=testdata2; myval3=testdata3}}","{@{ait1=aitdata1; ait2=aitdata2}}"

Which is how the output appears in Out-GridView.

The link to the original question is https://stackoverflow.com/questions/66390256/parsing-values-from-json-a-data-data-file — davtt, Mar 01 '21 at 16:37

mklement0 · Accepted Answer · 2021-03-02T01:53:45.263

Building on the answer to your previous question:

(Get-Content -Raw C:\Temp\JsonFile.json | ConvertFrom-Json).Results | ForEach-Object {
  # Helper script block that walks an object graph and outputs all 
  # *scalar* leaf property values as-is, while using a single string to
  # represent *array-valued* properties.
  $sb = {
    foreach ($el in @($args[0])) {
      if ($el -is [System.Management.Automation.PSCustomObject]) { # a complex object -> recurse
        foreach ($prop in $el.psobject.Properties) { 
          if ($prop.Value -is [array]) { # array-valued property
            # Use the same representation that Out-GridView displays.
            '"{0}"' -f ($prop.Value.ForEach({ $_.psobject.ToString() }) -join ',')
          }
          else { # scalar property -> recurse
            & $sb $prop.Value 
          }
        } 
      }
      else {
         # a leaf value -> output it, enclosed in embedded "..." if it is a string
        if ($el -is [string]) { "`"$el`"" } else { $el }
      }
    }
  }

  # Call the script block with the input object at hand to collect all values,
  # join them with ",", and output the result.
  (& $sb $_) -join ','

}

With your sample input this yields:

1,"Jeanette","Penddreth","jpenddreth0@census.gov","Female","26.58.193.2","@{myval1=testdata1; myval2=testdata2; myval3=testdata3}","@{ait1=aitdata1; ait2=aitdata2}"
2,"Giavani","Frediani","gfrediani1@senate.gov","Male","229.179.4.212","@{myval1=testdata5; myval2=testdata6; myval3=testdata7},@{myval1=testdata9; myval2=testdata10; myval3=testdata11}","@{ait1=aitdata3; ait2=aitdata4}"
3,"Noell","Bea","nbea2@imageshack.us","Female","180.66.162.255"
4,"Willard","Valek","wvalek3@vk.com","Male","67.76.188.26"

Note that an extra {...} enclosure is omitted compared to Out-GridView' representation, as it serves no real purpose.

Fundamentally, though, the representation is based on the hashtable-like representation that [pscustomobject] instances stringify to via their .ToString() methods, as described in this answer.

However, due to the bug described in GitHub issue #6163, the .ToString() method must be called on the intrinsic .psobject member rather than on the [pscustomobect] instance itself.

Thanks for all your help with this. I admit that I was a bit stuck... — davtt, Mar 02 '21 at 11:20
I'm glad to hear my answers were helpful, @davtt; my pleasure. — mklement0, Mar 02 '21 at 13:32

Parsing JSON Data Part 2

1 Answers1