Parsing Values From Json a data data file

Question

I need to load a JSON file into powershell, and then process the various nodes from the JSon file. The file is loaded from an existing Json file:

$JSONContent = (Get-Content -Path "C:\JsonFile.json" -Raw) | ConvertFrom-Json

Once this is loaded, I can see the data values by:

$JSONContent | SELECT -expand Results

which then gives me a list format of the fields and values.

My problem is that I want to iterate through the $JSONContent object and for each logical record (delimited by comma), concatenate each field value to make a comma-separated string.

I am sure the answer is trivial, but I have tried to resolve this. If any kind person could help with this, then I would be grateful.

The JSON file as input is:

{
  "results": [
    {
      "id": 1,
      "id2": null,
      "first_name": "Jeanette",
      "last_name": "Penddreth",
      "email": "[email protected]",
      "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": "[email protected]",
      "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": "[email protected]",
      "gender": "Female",
      "ip_address": "180.66.162.255"
    },
    {
      "id": 4,
      "first_name": "Willard",
      "last_name": "Valek",
      "email": "[email protected]",
      "gender": "Male",
      "ip_address": "67.76.188.26"
    }
  ]
}

The objective is to end up with the values from each node as a comma-separated string like this, using the example of the first record:

1,"Jeanette","Penddreth","[email protected]","Female", "26.58.193.2","testdata1","testdata2","testdata3","aitdata1","aitdata2"

mklement0 · Accepted Answer · 2021-03-01 23:21:11Z

The following command recursively walks all arrays and objects in the object graph parsed from your JSON input via ConvertFrom-Json and outputs each object's leaf property values (those containing primitive types) as a comma-separated list:

(Get-Content -Raw C:\JsonFile.json | ConvertFrom-Json).Results | ForEach-Object {
  # Helper script block that walks an object graph and outputs all leaf property values.
  $sb = {
    foreach ($el in @($args[0])) {
      if ($el -is [System.Management.Automation.PSCustomObject]) { # a complex object -> recurse
        foreach ($prop in $el.psobject.Properties) { & $sb $prop.Value } # recurse on the properties
      }
      else {
        $el # a leaf value -> output it
      }
    }
  }

  # Call the script block with the input object at hand and collect all values.
  $values = & $sb $_

  # Enclose string values in embedded "...", join with "," and output.
  $values.ForEach({ if ($_ -is [string]) { "`"$_`"" } else { $_} }) -join ','

}

^{Note: The @(...) around $args[0] is required to ensure that null JSON values aren't lost.}

With your sample input this yields:

1,"Jeanette","Penddreth","[email protected]","Female","26.58.193.2","testdata1","testdata2","testdata3","aitdata1","aitdata2"
2,"Giavani","Frediani","[email protected]","Male","229.179.4.212","testdata5","testdata6","testdata7","testdata9","testdata10","testdata11","aitdata3","aitdata4"
3,"Noell","Bea","[email protected]","Female","180.66.162.255"
4,"Willard","Valek","[email protected]","Male","67.76.188.26"

Collectives™ on Stack Overflow

Parsing Values From Json a data data file

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related