如何在PowerShell中展开JSON嵌套数组并导出为CSV

我有如下格式的JSON对象数组：

[
     {
        "WorkspaceName":  "A",
        "Users":  [
                      {
                          "AccessRight":  "Admin",
                          "UserPrincipalName":  "John"
                      },
                      {
                          "AccessRight":  "Contributor",
                          "UserPrincipalName":  "Jane"
                      }
                  ]
    },
    {
        "WorkspaceName":  "B",
        "Users":  [
                      {
                          "AccessRight":  "Admin",
                          "UserPrincipalName":  "John"
                      }
                  ]
    }
]

我希望将该JSON对象数组输出为CSV文件，需要展开Users数组，将其属性拆分到不同行中，同时重复对应的WorkspaceName属性。期望的CSV格式如下：

WorkspaceName,Users_AccessRight,Users_UserPrincipalName
A,Admin,John
A,Contributor,Jane
B,Admin,John

我尝试了以下代码，但Users_AccessRight和Users_UserPrincipalName列返回的是System.Object[]，并未按预期展开行：

$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

你的代码问题在于直接提取Users.AccessRight和Users.UserPrincipalName，这会返回数组而非单个值，正确的做法是遍历每个Workspace下的Users数组，为每个用户生成一条带对应WorkspaceName的记录。

修改后的代码如下：

$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 {
    # 保存当前Workspace的名称
    $currentWorkspace = $_.WorkspaceName
    # 遍历当前Workspace下的每个用户
    $_.Users | ForEach-Object {
        [PSCustomObject]@{
            WorkspaceName = $currentWorkspace
            Users_AccessRight = $_.AccessRight
            Users_UserPrincipalName = $_.UserPrincipalName
        }
    }
}

$flattened | Export-CSV $pathToOutputFile -NoTypeInformation -Encoding UTF8

代码说明 #

1. 外层ForEach-Object遍历每个Workspace对象，先保存当前的WorkspaceName。
2. 内层ForEach-Object遍历当前Workspace下的Users数组，为每个用户创建一个PSCustomObject，将WorkspaceName与用户的AccessRight、UserPrincipalName绑定。
3. 最终所有生成的对象会被收集到$flattened数组中，导出CSV时自然会按行展开。

内容的提问来源于stack exchange，提问作者skyline01