Powershell - JSON file to table

Question

I am trying to get into Powershell and am trying to convert a simple JSON file to table format.

The json-file looks like this:

{ "Members" : [
{ "Id" : 1,
    "Details" : [ { "FirstName" : "Adam" }, { "LastName" : "Ant" } ] },
{ "Id" : 2,
    "Details" : [ { "FirstName" : "Ben" }, { "LastName" : "Boggs" } ] },
{ "Id" : 3,
    "Details" : [ { "FirstName" : "Carl"} , { "LastName" : "Cox" } ] }
]
}

Powershell expression:

$jString.Members.details | Select-Object -Property Id, FirstName, LastName

output so far (best I got).. id missing

Id  FirstName   LastName
--  ---------   --------
    Adam        Ant
    Ben         Boggs
    Carl        Cox

How would I accomplish that?

Any help appreciated

Answer 1

JSON is not my strength but if you look at the data structure ID is not on the same level as first and last name which is nested in details. I put the data in a here-string for testing.

$json = @"
{ "Members" : [
    { "Id" : 1,
        "Details" : [ { "FirstName" : "Adam" }, { "LastName" : "Ant" } ] },
    { "Id" : 2,
        "Details" : [ { "FirstName" : "Ben" }, { "LastName" : "Boggs" } ] },
    { "Id" : 3,
        "Details" : [ { "FirstName" : "Carl"} , { "LastName" : "Cox" } ] }
]
}
"@ | ConvertFrom-Json | Select-Object -Expand Members 

$json | Select ID,@{Name="FirstName";E={$_.Details | Select -Expand FirstName}},@{Name="LastName";E={$_.Details | Select -Expand LastName}}

I use calculated properties to get those "nested" details associated to each ID

Which gets me the following results.

Id FirstName LastName
-- --------- --------
 1 Adam      Ant     
 2 Ben       Boggs   
 3 Carl      Cox