大家早上好/下午好
针对Google BigQuery API运行查询时,您会在以奇怪的方式构造的JSON文件中获得响应。
您可以在$ColumnName = $Response.schema.fields.name
中找到列名,但是列的值在$Row = $Response.rows
中。
这是行的样子:
{
"f": [
"@{v=@2019-11-04 14:14:49}","@{v=@8349531079148439128}","@{v=Returning Visitor}","@{v=1}","@{v=Yes}","@{v=Good news!}","@{v=0}","@{v=google}","@{v=organic}","@{v=(not set)}","@{v=(not provided)}","@{v=mobile}","@{v=/}","@{v=No}","@{v=No}"
]
},{
"f": [
"@{v=@2019-11-04 08:52:48}","@{v=@3602395308467676896}","@{v=New Visitor}","@{v=4a9bcd67a01d}","@{v=We\u0027re happy to carry out the work.}","@{v=}","@{v=ivr}","@{v=sms}","@{v=ivr-deflect}","@{v=No}"
]
}
因此,在“ f”内部,将每一行分配给“ f”,将每一列的值分配给“ v”。
为了使它们具有正确的格式,我在下面编写了一对循环:
$Data = @()
for($j=0; $j -lt $RowCount; $j++){
$Datarow = New-Object PSObject
For($i=0; $i -lt $ColumnCount; $i++){
$Datarow | Add-Member -type NoteProperty -Name $ColumnName[$i] -Value $Row[$j].f.v[$i]
}
$Data += $Datarow
}
但是您可以想象,报价缓慢。尤其是在我们的老旧的服务器计算机上,它将对其进行调度。
您知道更好的方法吗?
致敬Jarek