使用jq将json转换为csv，数组嵌套在数组中

Question

我如何压平这个json数组，并使用jq写入csv文件，使每一行都包含所有字段，即

我对jq有点新手，但从这个开始，它没有捕获内部嵌套数组

jq -r '(.[0] | keys_unsorted) as $keys | $keys, map([.[ $keys[] ]])[]'

[
{
"name":"PM2.5",
"unit":"ug/m3",
"values":[
{
"timestamp":"2020-11-16T13:10:21.135Z",
"value":3.696
},
{
"timestamp":"2020-11-16T13:10:31.649Z",
"value":3.696
},
{
"timestamp":"2020-11-16T13:10:42.35Z",
"value":3.696
},
{
"timestamp":"2020-11-16T13:10:52.953Z",
"value":3.696
}
]
},
{
"name":"PM10",
"unit":"ug/m3",
"values":[
{
"timestamp":"2020-11-16T13:10:21.135Z",
"value":7.596
},
{
"timestamp":"2020-11-16T13:10:31.649Z",
"value":7.596
},
{
"timestamp":"2020-11-16T13:10:42.35Z",
"value":7.596
},
{
"timestamp":"2020-11-16T13:10:52.953Z",
"value":7.596
}
]
}
]

到csv如下：

"name", "unit", "timestamp", "value"
"PM2.5", "ug/m3", "2020-11-16T13:10:21.135Z", 3.696
"PM2.5", "ug/m3", "2020-11-16T13:10:31.649Z", 3.696
"PM2.5", "ug/m3", "2020-11-16T13:10:42.35Z", 3.696
"PM2.5", "ug/m3", "2020-11-16T13:10:52.953Z", 3.696  
"PM10", "ug/m3", "2020-11-16T13:10:21.135Z", 7.596
"PM10", "ug/m3", "2020-11-16T13:10:31.649Z", 7.596
"PM10", "ug/m3", "2020-11-16T13:10:42.35Z", 7.596
"PM10", "ug/m3", "2020-11-16T13:10:52.953Z", 7.596

Answer 1

$ jq -r '
[["name","unit","timestamp","value"]], 
[ .[] 
| [ .name, .unit ] + 
(.values[] | [.timestamp,.value]) ] 
| .[] 
| @csv' input.json 
"name","unit","timestamp","value"
"PM2.5","ug/m3","2020-11-16T13:10:21.135Z",3.696
"PM2.5","ug/m3","2020-11-16T13:10:31.649Z",3.696
"PM2.5","ug/m3","2020-11-16T13:10:42.35Z",3.696
"PM2.5","ug/m3","2020-11-16T13:10:52.953Z",3.696
"PM10","ug/m3","2020-11-16T13:10:21.135Z",7.596
"PM10","ug/m3","2020-11-16T13:10:31.649Z",7.596
"PM10","ug/m3","2020-11-16T13:10:42.35Z",7.596
"PM10","ug/m3","2020-11-16T13:10:52.953Z",7.596

构建一个数组数组，每行一个，包括一个头行，然后将它们全部格式化为CSV记录。