如何用PowerShell将Rest API的JSON响应导出为含嵌套Owner的CSV

本人几乎没有PowerShell编写经验，需将Rest API返回的JSON响应导出为CSV以作后续处理。现有API响应包含嵌套的Owner对象，当前编写的代码可获取包含Owner字段的实体，但Owner仅显示为单列，希望拆分Owner的子字段（如emailAddress等）并合并到同一输出中。

API响应示例 #

{
  "id": "9dea9e9f-f802-4e39-8a75-ad84fabde69f",
  "title": "Power BI Capacity \"Enterprise Apps - P1\" Compute",
  "artifactId": "3f517b9b-ae25-4247-8df4-428dfa9a759b",
  "artifactDisplayName": "Fabric Capacity Metrics",
  "subArtifactDisplayName": "Compute",
  "artifactType": "Report",
  "isEnabled": true,
  "frequency": "Weekly",
  "startDate": "2/14/2024 12:00:00 AM",
  "endDate": "2/15/2025 4:33:43 PM",
  "linkToContent": true,
  "previewImage": true,
  "attachmentFormat": "PDF",
  "owner": {
    "emailAddress": "hperez@swinerton.com",
    "displayName": "Tito Perez",
    "identifier": "hperez@swinerton.com",
    "graphId": "d631e63d-b77d-4410-a553-712dc93f1655",
    "principalType": "User"
  },
  "users": []
}

现有代码 #

$URL = 'https://api.powerbi.com/v1.0/myorg/admin/users/63d0139e-691f-4005-a185-9bde3362080f/subscriptions'
$r=Invoke-PowerBIRestMethod -URL $URL -Method Get
$JSONData=$r|ConvertFrom-Json 
$JSONData | ForEach-Object { $_.SubscriptionEntities } | Select id,title,artifactId,artifactDisplayName,subArtifactDisplayName,artifactType,isEnabled,frequency,startDate,endDate,linkToContent,attachmentFormat,owner | Format-Table
$JSONData | ForEach-Object { $_.SubscriptionEntities.owner } | Select emailAddress| Format-Table

要拆分嵌套的Owner对象并将其字段合并到主输出中，可使用PowerShell的计算属性提取Owner的子字段，同时保留原有实体的所有字段，最后直接导出为CSV文件。

修改后的代码如下：

$URL = 'https://api.powerbi.com/v1.0/myorg/admin/users/63d0139e-691f-4005-a185-9bde3362080f/subscriptions'
$r = Invoke-PowerBIRestMethod -URL $URL -Method Get
$JSONData = $r | ConvertFrom-Json 

# 提取SubscriptionEntities并处理嵌套的Owner字段
$processedData = $JSONData.SubscriptionEntities | Select-Object `
    id, title, artifactId, artifactDisplayName, subArtifactDisplayName, `
    artifactType, isEnabled, frequency, startDate, endDate, linkToContent, `
    attachmentFormat, `
    # 计算属性：提取Owner的子字段并赋予清晰命名
    @{Name='OwnerEmail'; Expression={$_.owner.emailAddress}},
    @{Name='OwnerDisplayName'; Expression={$_.owner.displayName}},
    @{Name='OwnerIdentifier'; Expression={$_.owner.identifier}},
    @{Name='OwnerGraphId'; Expression={$_.owner.graphId}},
    @{Name='OwnerPrincipalType'; Expression={$_.owner.principalType}}

# 可选：查看处理后的数据
$processedData | Format-Table

# 导出为CSV文件，替换为你的目标路径
$processedData | Export-Csv -Path "C:\Your\Target\Path\Subscriptions.csv" -NoTypeInformation -Encoding UTF8

代码说明 #

• 计算属性：通过@{Name='字段名'; Expression={取值逻辑}}的方式，从嵌套的owner对象中提取子字段，用OwnerEmail这类命名避免字段冲突。
• Export-Csv参数：-NoTypeInformation移除CSV开头的类型注释，-Encoding UTF8确保特殊字符显示正常。
• 简化遍历：直接用$JSONData.SubscriptionEntities代替ForEach-Object遍历，代码更简洁高效。

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