I have an array of JSON objects like the following:
[
{
"WorkspaceName": "A",
"Users": [
{
"AccessRight": "Admin",
"UserPrincipalName": "John"
},
{
"AccessRight": "Contributor",
"UserPrincipalName": "Jane"
}
]
},
{
"WorkspaceName": "B",
"Users": [
{
"AccessRight": "Admin",
"UserPrincipalName": "John"
}
]
}
]
I want to output this array of JSON objects to a CSV file. Specifically, I want to "expand" the Users array to include all of its properties on separate rows, thereby "duplicating" the WorkspaceName property across rows. So, I want the CSV file to appear like:
WorkspaceName Users_AccessRight Users_UserPrincipalName
------------- ----------------- -----------------------
A Admin John
A Contributor Jane
B Admin John
How can I do so? In particular, how can I loop through the selected properties in the Users array? While there will always be the same properties in array Users, each JSON object may have a different number of users in Users. I have attempted the following code, but it only returns System.Object[] for Users_AccessRight and Users_UserPrincipalName and doesn't "expand" the rows like I want above.
$obj = @"
[
{
"WorkspaceName": "A",
"Users": [
{
"AccessRight": "Admin",
"UserPrincipalName": "John"
},
{
"AccessRight": "Contributor",
"UserPrincipalName": "Jane"
}
]
},
{
"WorkspaceName": "B",
"Users": [
{
"AccessRight": "Admin",
"UserPrincipalName": "John"
}
]
}
]
"@ | ConvertFrom-Json
$pathToOutputFile = "C:\output.csv"
$flattened = $obj | ForEach-Object {
return [PSCustomObject]@{
WorkspaceName = $_.WorkspaceName
Users_AccessRight = $_.Users.AccessRight
Users_UserPrincipalName = $_.Users.UserPrincipalName
}
}
$flattened | Export-CSV $pathToOutputFile -NoTypeInformation
You're missing an inner loop to enumerate the .Users
property:
$obj | ForEach-Object {
foreach ($user in $_.Users) {
[pscustomobject]@{
WorkspaceName = $_.WorkspaceName
Users_AccessRight = $user.AccessRight
Users_UserPrincipalName = $user.UserPrincipalName
}
}
} | Export-Csv $pathToOutputFile -NoTypeInformation