jsonpowershellexport-to-csvobject-graph

how to expand nested array in JSON in PowerShell


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

Solution

  • 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