PowerShell的 - 通过解析JSON创建CSV

Question

我提取从Facebook一个JSON，这是它的样子：

pressions_by_paid_non_paid_unique/day 
{ 
    "data": [ 
    { 
     "name": "page_impressions_by_paid_non_paid_unique", 
    "period": "day", 
    "values": [ 
    { 
     "value": { 
     "total": 549215, 
     "unpaid": 549215, 
     "paid": 0 
     }, 
     "end_time": "2017-06-02T07:00:00+0000" 
    }, 

我想什么是创建一个CSV文件，这要么是这样的：

总，无偿，支付，结束时间
549215,549215,0,2017-06-02T07：00：00 + 0000

或

值，NUM，END_TIME

总，549215,2017-06-02T07：00：00 + 0000

未付，549215,2017-06-02T07：00：00 + 0000

支付，0,2017-06-02T07：00：00 + 0000

我想出什么样的主意是：

$file = "D:\BI_LP\001-Solutions_Sources\script\Powershell\curl\facebook21.json" 
Get-Content $file -Raw | 
ConvertFrom-Json | 
Select -Expand data | 
Select -Expand values | % { 
$end_time = $_.end_time 
$value = $_.value 
$_.value | select @{n='end_time';e={$end_time}}, @{n='value';e={$value}} 
} 

这给了我：

end_time     value 

--------     ----- 


2017-05-21T07:00:00+0000 @{total=608837; unpaid=608837; paid=0} 

2017-05-22T07:00:00+0000 @{total=682090; unpaid=682090; paid=0} 

2017-05-23T07:00:00+0000 @{total=885274; unpaid=885274; paid=0} 

2017-05-24T07:00:00+0000 @{total=810845; unpaid=810845; paid=0} 

2017-05-25T07:00:00+0000 @{total=755453; unpaid=755453; paid=0} 

2017-05-26T07:00:00+0000 @{total=629096; unpaid=629096; paid=0} 

有没有人有什么建议？

Answer 1

我觉得你快到了。你可以尝试在foreach循环创建输出对象有点像这样：

[pscustomobject]@{ 
    total = $_.value.total 
    unpaid = $_.value.unpaid 
    paid  = $_.value.paid 
    end_time = $_.end_time 
} 

Answer 2

基于您的代码：

@" 
{ 
    "data": [ 
    { 
     "name": "page_impressions_by_paid_non_paid_unique", 
     "period": "day", 
     "values": [ 
     { 
      "value": { 
      "total": 549215, 
      "unpaid": 549215, 
      "paid": 0 
     }, 
     "end_time": "2017-06-02T07:00:00+0000" 
     } 
     ] 
    } 
    ] 
} 
"@ | ConvertFrom-Json | Select -Expand data | Select -Expand values | % { 
    $end_time = $_.end_time 
    $value = $_.value 
    $_.value | select @{n='endtime'; e={$value.total}}, @{n='unpaid'; e={$value.unpaid}}, @{n='paid'; e={$value.paid}}, @{n='end_time';e={$end_time}} 
} | ft -autosize 

提供了以下的输出：

endtime unpaid paid end_time     
------- ------ ---- --------     
549215 549215 0 2017-06-02T07:00:00+0000 

而且出口到csv，将ft -autosize更改为Export-Csv -Path c:\...

供参考：您的json示例缺少很多右括号。

Answer 3

感谢查理，你让我在正确的方向:)

$file = "D:\BI_LP\001-Solutions_Sources\script\Powershell\curl\facebook21.json" 
Get-Content $file -Raw | 
ConvertFrom-Json | 
Select -Expand data | 
Select -Expand values | % { 
$end_time = $_.end_time 
$total = $_.value.total 
$unpaid = $_.value.unpaid 
$paid  = $_.value.paid 
$_.value | select @{n='end_time';e={$end_time}}, @{n='total';e={$total}},@{n='unpaid';e={$unpaid}},@{n='paid';e={$paid}} 
}