Powershell - JSON file for table
I'm trying to get into Powershell and trying to convert a simple JSON file to table format.
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 I got). id is missing
Id FirstName LastName
-- --------- --------
Adam Ant
Ben Boggs
Carl Cox
How would you do it?
Any help is appreciated
+3
source to share
1 answer
JSON is not my strength, but if you look at the data structure, then the ID is not on the same level as the first and last name nested in the details. I was putting data into a string here 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 am using calculated properties to get these "nested" details associated with each id
This gives me the following results.
Id FirstName LastName
-- --------- --------
1 Adam Ant
2 Ben Boggs
3 Carl Cox
+2
source to share