powershell - 在Powershell中将JSON转换为Csv列

Question

我想将JSON转换为以下CSV输出。我正在努力将“结果”数据解析为两列。

CSV输出:

TIMESTAMP, VALUE,
1581292800000, 270,
1581292860000, 347

我的JSON输出如下:

$JSON =
        {
        "value":  [
                      {
                          "fields":  [
                                         "@{label=DateTime; field=eventTimestamp; type=date; aggregation=series}",
                                         "@{label=Count; field=metrics.End User Response Time (ms); type=integer; aggregation=count}"
                                     ],
                          "results":  [
                                          "1581292800000 270",
                                          "1581292860000 347",
                                          "1581292920000 348",
                                          "1581292980000 401",
                                          "1581293040000 435",
                                          "1581293100000 413",
                                          "1581293160000 466",
                                          "1581293220000 445",
                                          "1581293280000 450",
                                          "1581293340000 488",
                                          "1581293400000 470",
                                          "1581293460000 450",
                                          "1581293520000 440",
                                          "1581293580000 435",
                                          "1581293640000 403",
                                          "1581293700000 472",
                                          "1581293760000 392",
                                          "1581293820000 398",
                                          "1581293880000 357",
                                          "1581293940000 356",
                                          "1581294000000 361",
                                          "1581294060000 339",
                                          "1581294120000 373",
                                          "1581294180000 340",
                                          "1581294240000 329",
                                          "1581294300000 327",
                                          "1581294360000 307",
                                          "1581294420000 282",
                                          "1581294480000 315"
                                      ],
                          "moreData":  false,
                          "schema":  "browserrecord"
                      }
                  ],
        "Count":  1
    }

下面的代码提取结果，但是TIMESTAMP和VALUE都在同一列中。

$Output1 = ConvertTo-Json $JSON
$Output2 = ConvertFrom-Json $Outage2 | Select -ExpandProperty 'value' |  Select -ExpandProperty 'results' 

任何帮助将非常感激。

干杯!

Answer 1

到目前为止，您已经成功创建了一个字符串数组$output2，其中包含json文档中所有以空格分隔的字符串值-到目前为止，一切都很好!

现在，我们可以使用-split运算符将两个“列”拆分为单独的字符串:

$results = $output2 |ForEach-Object {
    $ts,$value = -split $_

    [pscustomobject]@{
        TIMESTAMP = $ts
        VALUE = $value
    }
}

现在，我们有了一个对象数组，将两个值分成适当命名的属性 TIMESTAMP和 VALUE，我们可以使用 Export-Csv:

$results |Export-Csv .\results.csv -NoTypeInformation