PowerShell从Azure DevOps JSON数据生成CSV失败求助

尝试用Azure CLI导出已完成的Pull Requests到CSV时，执行以下命令：

az repos pr list --organization https://dev.azure.com/hahahaha `
    --project main --status completed `
    --top 10 --skip 0 `
    --query "[*].{pullRequestId:pullRequestId, createdBy:createdBy.displayName,creationDate: creationDate,repository: repository.name, closedDate: closedDate}" `
    | ConvertFrom-Json `
    | ConvertTo-Csv

得到异常输出，显示的是数组对象的属性而非PR数据：

#TYPE System.Object[]
"Count","Length","LongLength","Rank","SyncRoot","IsReadOnly","IsFixedSize","IsSynchronized"
"10","10","10","1","System.Object[]","False","True","False"

但将ConvertTo-Csv替换为Format-Table时，能正常显示结构化数据：

az repos pr list --organization https://dev.azure.com/hahahaha `
    --project main --status completed `
    --top 10 --skip 0 `
    --query "[*].{pullRequestId:pullRequestId, createdBy:createdBy.displayName,creationDate: creationDate,repository: repository.name, closedDate: closedDate}" `
    | ConvertFrom-Json `
    | Format-Table

输出示例：

closedDate                       createdBy        creationDate                     pullRequestId repository
----------                       ---------        ------------                     ------------- ----------
2024-03-26T13:50:27.781982+00:00 dddd dddddd      2024-03-22T20:33:39.322343+00:00          3966 zzzzzzzzzzzzzzzzzzzz
2024-03-26T13:07:43.264020+00:00 eeeeee eeeeeeeee 2024-03-26T12:13:30.279093+00:00          3987 wwwwwwwwwwwwwww    

问题原因 #

ConvertFrom-Json返回的数组对象被ConvertTo-Csv直接当作单一对象处理，而非枚举数组内的每个PR条目，导致导出的是数组自身的属性而非PR数据。

修复方法 #

方法1：强制枚举数组元素 #

在ConvertFrom-Json后添加ForEach-Object（简写%），逐个输出数组内的PR对象，确保ConvertTo-Csv处理的是每条PR数据：

az repos pr list --organization https://dev.azure.com/hahahaha `
    --project main --status completed `
    --top 10 --skip 0 `
    --query "[*].{pullRequestId:pullRequestId, createdBy:createdBy.displayName,creationDate: creationDate,repository: repository.name, closedDate: closedDate}" `
    | ConvertFrom-Json `
    | ForEach-Object { $_ } `
    | ConvertTo-Csv -NoTypeInformation

方法2：先存变量再导出 #

将ConvertFrom-Json的结果赋值给变量，再通过变量管道到ConvertTo-Csv，PowerShell会自动枚举数组元素：

$pullRequests = az repos pr list --organization https://dev.azure.com/hahahaha `
    --project main --status completed `
    --top 10 --skip 0 `
    --query "[*].{pullRequestId:pullRequestId, createdBy:createdBy.displayName,creationDate: creationDate,repository: repository.name, closedDate: closedDate}" `
    | ConvertFrom-Json

$pullRequests | ConvertTo-Csv -NoTypeInformation

额外说明 #

添加-NoTypeInformation参数可以去掉CSV开头的#TYPE标识，让输出更符合标准CSV格式。

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