PowerShell中如何将JSON数组的platforms字段作为单列导出为CSV

使用Invoke-RestMethod获取JSON格式的API响应后，尝试导出为CSV时，当前代码将platforms数组的元素拆分为多列，不符合需求。需要调整代码，将整个platforms数组作为CSV的单独一列保存。

现有代码 #

$response = Invoke-RestMethod 'resturl' -Method 'GET' -Headers $headers  
$response | ConvertTo-Json
$response | Export-Csv "\<path\>\\input.json" -NoTypeInformation
$properties=@('applicationServerId','applicationId','applicationName','serverName','serverFqdn','serverStatus','serverEnvironment','productLine','datacenter','serverType','serverSubStatus', @{Name='platform';Expression={$_.platforms[0]}} @{Name='instance';Expression={$_.platforms[1]}} @{Name='environment';Expression={$_.platforms[2]}} @{Name='platformMasterId';Expression={$_.platforms[3]}} @{Name='host';Expression={$_.platforms[4]}} @{Name='gateway';Expression={$_.platforms[5]}})

预期JSON响应结构 #

[
    {
        "applicationServerId":  12345,
        "applicationId":  "123456",
        "applicationName":  "my_application1",
        "serverName":  "test",
        "serverFqdn":  "test.com",
        "serverStatus":  "yes",
        "serverEnvironment":  "dev",
        "productLine":  "myproduct1",
        "datacenter":  "austn",
        "platforms":  [
                          "@{platform=myplatform1; instance=test-austn; environment=DEV; platformMasterId=12345; host=; gateway=}"
                      ],
        "serverType":  "app",
        "serverSubStatus":  null
    },
    {
        "applicationServerId":  12345,
        "applicationId":  "1234567",
        "applicationName":  "my_application2",
        "serverName":  "test",
        "serverFqdn":  "test.com",
        "serverStatus":  "yes",
        "serverEnvironment":  "Development",
        "productLine":  "myproduct2",
        "datacenter":  "austn",
        "platforms":  [
                          "@{platform=myplatform2; instance=test-austn; environment=DEV; platformMasterId=123456; host=; gateway=}"
                      ],
        "serverType":  "app",
        "serverSubStatus":  null
    }
]

当前错误的CSV输出 #

"applicationServerId","applicationId","applicationName","serverName","serverFqdn","serverStatus","serverEnvironment","productLine","datacenter","serverType","serverSubStatus","platform","instance","environment","platformMasterId","host","gateway"

"12345","123456","my_application1","test","test.com","yes","dev","myproduct1","austn","Application",,,"@{platform=myplatform1; instance=test-austn; environment=DEV; platformMasterId=12345; host=; gateway=}",,,,,

"12345","123456","my_application1","test","test.com","yes","dev","myproduct1","austn","Application",,,"@{platform=myplatform1; instance=test-austn; environment=DEV; platformMasterId=12345; host=; gateway=}","@{platform=myplatform2; instance=test-austn; environment=DEV;platformMasterId=12345; host=; gateway=}",,,,,

解决方案 #

修改代码，通过计算属性将整个platforms数组转换为可存储在CSV中的字符串格式（推荐用JSON格式，便于后续解析），然后导出CSV：

$response = Invoke-RestMethod 'resturl' -Method 'GET' -Headers $headers  

# 定义导出属性，将platforms数组转为压缩JSON字符串作为单独一列
$properties = @(
    'applicationServerId',
    'applicationId',
    'applicationName',
    'serverName',
    'serverFqdn',
    'serverStatus',
    'serverEnvironment',
    'productLine',
    'datacenter',
    'serverType',
    'serverSubStatus',
    @{Name='platforms'; Expression={ $_.platforms | ConvertTo-Json -Compress }}
)

# 选择属性后导出CSV
$response | Select-Object $properties | Export-Csv -Path "\<path\>\output.csv" -NoTypeInformation

说明 #

• 原代码错误地将platforms数组的索引元素拆分为多个列，现在通过ConvertTo-Json -Compress把整个数组转为紧凑的JSON字符串，确保CSV中platforms是单独一列。
• 如果不需要JSON格式，也可以用$_.platforms | Out-String转为纯文本格式，但JSON格式更适合后续还原数组结构。

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