如何将Azure API中的JSON文件数据转换为CSV?

Question

我有一个JSON文件，其中有如下所示的数据，我试图使用Powershell脚本将其转换为CSV，但我得到的响应是出乎意料的。如何在csv列中正确获取时间戳，平均值和最大值的数据。

JSON data:
[
{
"cost":  44639,
"timespan":  "2023-02-10T23:59:59Z/2023-03-13T23:59:59Z",
"interval":  "PT1H",
"value":  [
{
"id":  "/subscriptions/xxxx/resourceGroups/Automation-script/providers/Microsoft.Storage/storageAccounts/automationscript87c1/providers/Microsoft.Insights/metrics/UsedCapacity",
"type":  "Microsoft.Insights/metrics",
"name":  {
"value":  "UsedCapacity",
"localizedValue":  "Used capacity"
},
"displayDescription":  "The amount of storage used by the storage account. For standard storage accounts, itu0027s the sum of capacity used by blob, table, file, and queue. For premium storage accounts and Blob storage accounts, it is the same as BlobCapacity or FileCapacity.",
"unit":  "Bytes",
"timeseries":  [
{
"metadatavalues":  [
],
"data":  [
{
"timeStamp":  "2023-02-10T23:59:00Z"
},
{
"timeStamp":  "2023-02-11T00:59:00Z"
},
{
"timeStamp":  "2023-02-11T01:59:00Z"
},
{
"timeStamp":  "2023-02-11T02:59:00Z", 
"average" : 17044123
}

]
}
],
"errorCode":  "Success"
}
],
"namespace":  "Microsoft.Storage/storageAccounts",
"resourceregion":  "centralindia"
},


"cost":  44639,
"timespan":  "2023-02-10T23:59:59Z/2023-03-13T23:59:59Z",
"interval":  "PT1H",
"value":  [
{
"id":  "/subscriptions/xxxxx/resourceGroups/VM-DavePlatform_group/providers/Microsoft.Compute/virtualMachines/AnotherTestVM/providers/Microsoft.Insights/metrics/Percentage CPU",
"type":  "Microsoft.Insights/metrics",
"name":  {
"value":  "Percentage CPU",
"localizedValue":  "Percentage CPU"
},
"displayDescription":  "The percentage of allocated compute units that are currently in use by the Virtual Machine(s)",
"unit":  "Percent",
"timeseries":  [
{
"metadatavalues":  [
],
"data":  [
{
"timeStamp":  "2023-02-10T23:59:00Z"
},
{
"timeStamp":  "2023-02-11T00:59:00Z"
},
{"timeStamp":  "2023-03-13T21:59:00Z",
"maximum" 35.16
}
]
}
],
"errorCode":  "Success"
}
],
"namespace":  "Microsoft.Compute/virtualMachines",
"resourceregion":  "centralindia"
} ]

Powershell脚本

# Load the JSON file
$json = Get-Content -Raw -Path "C:Users$env:UserNameDownloadsAll_Resources_Combined.JSON" | ConvertFrom-Json

# Select the properties you want to keep and convert the values to string
$json | Select-Object @{n='Id';e={$_.value.id}},
@{n='LocalizedValue';e={$_.value.name.localizedValue}},
@{n='Unit';e={$_.value.unit}},
@{n='Timestamp';e={$_.value.timeseries.data | ForEach-Object {$_.timeStamp}}},
@{n='Average';e={$_.value.timeseries.data | ForEach-Object {$_.average}}},
@{n='Maximum';e={$_.value.timeseries.data | ForEach-Object {$_.maximum}}} |
Export-Csv  "C:Users$env:UserNameDownloadsfile.csv" -NoTypeInformation

csv数据我已经尝试迭代我的Powershell脚本的每个时间戳，但我仍然没有得到每一行的每个时间戳的数据，而不是在单行中获得所有的时间戳值。

Answer 1

您需要对每个对象的.value.timeseries.data.timestamp属性值进行循环，以便为每个对象创建输出对象。

基于您的方法(如果关注性能，可以对其进行优化):

Get-Content -Raw C:Users$env:UserNameDownloadsAll_Resources_Combined.JSON | 
ConvertFrom-Json | 
ForEach-Object {
foreach ($ts in $_.value.timeseries.data.timestamp) {
$_ | 
Select-Object @{n = 'Id'; e = { $_.value.id } },
@{n = 'LocalizedValue'; e = { $_.value.name.localizedValue } },
@{n = 'Unit'; e = { $_.value.unit } },
@{n = 'Timestamp'; e = { $ts } },
@{n = 'Average'; e = { $_.value.timeseries.data.average } } },
@{n = 'Maximum'; e = { $_.value.timeseries.data.maximum } } } 
}
} # | Export-Csv ...

注意上面在属性访问表达式.value.timeseries.data.timestamp、.value.timeseries.data.average和.value.timeseries.data.maximum中使用了成员访问枚举。