json到csv(格式化)，使用带有时间字段的jq并排序

Question

我读过类似的问题，但没有找到答案。我输入了以下数据：

{  "connectionHistory": [
{
"endTime": 1585571806,
"bytesSent": 31588,
"startTime": 1585571453,
"duration": 353,
"bytesReceived": 68711,
"virtualIpAddress": "10.20.1.102",
"remoteIpAddress": "172.16.15.183"
},
{
"endTime": 1585591333,
"bytesSent": 21927,
"startTime": 1585591095,
"duration": 238,
"bytesReceived": 51041,
"virtualIpAddress": "10.20.1.102",
"remoteIpAddress": "172.16.13.75"
},
{
"endTime": 1585592547,
"bytesSent": 4630423,
"startTime": 1585591333,
"duration": 1214,
"bytesReceived": 678052,
"virtualIpAddress": "10.20.1.102",
"remoteIpAddress": "172.16.13.75"
},
{
"endTime": 1585743727,
"bytesSent": 2153310,
"startTime": 1585743512,
"duration": 215,
"bytesReceived": 499382,
"virtualIpAddress": "10.20.1.102",
"remoteIpAddress": "172.16.12.209"
}

]}

并且喜欢在输出中有这样的东西：

Start               End                 Duration    IP client       IP remote      Received       Sent
01 Apr 2020, 16:13  01 Apr 2020, 16:15  02m 11s     10.20.1.102     5.170.193.103   475.15 K    2.01 M
01 Apr 2020, 14:18  01 Apr 2020, 14:22  03m 35s     10.20.1.102     5.170.192.209   487.68 K    2.05 M
30 Mar 2020, 20:02  30 Mar 2020, 20:22  20m 14s     10.20.1.102     5.170.193.75    662.16 K    4.42 M
30 Mar 2020, 19:58  30 Mar 2020, 20:02  03m 58s     10.20.1.102     5.170.193.75    49.84 K     21.41 K
30 Mar 2020, 14:30  30 Mar 2020, 14:36  05m 53s     10.20.1.102     5.170.195.183   67.1 K      30.85 K

试着和jq一起玩，但没有好的结果。。。

感谢提示；-(

谢谢，p.

Answer 1

我在网上做了一些尝试和搜索。找到了有帮助的代码(jq:Object不能是csv格式的，只能是数组(。

创建的文件json2csv.jq包含：

def json2headers:
def isscalar: type | . != "array" and . != "object";
def isflat: all(.[]; isscalar);
paths as $p
| getpath($p)
| if type == "array" and isflat then $p
elif isscalar and (($p[-1]|type) == "string") then $p
else empty end ;
def json2array($header):
def value($p):
try getpath($p) catch null
| if type == "object" then null else . end;
[$header[] as $p | value($p)];
def json2csv:
( [.[] | json2headers] | unique) as $h
| ([$h[]|join("_") ],
(.[]
| json2array($h)
| map( if type == "array" then map(tostring)|join("|") else tostring end)))
| @csv ;

使用进行调用

jq -r -L. 'include "json2csv"; json2csv' connAAA.json

我得到了：

"bytesReceived","bytesSent","duration","endTime","remoteIpAddress","startTime","virtualIpAddress"
"9510","4657","81","1585511362","192.168.101.91","1585511281","10.20.1.6"
"48586","52696","1956","1585514599","192.168.101.91","1585512643","10.20.1.6"
"11829","7399","153","1585514835","192.168.101.91","1585514682","10.20.1.6"
"13871","10318","330","1585518156","192.168.101.91","1585517826","10.20.1.6"

如果我使用@tsv而不是@csv，我得到：

bytesReceived   bytesSent       duration        endTime remoteIpAddress startTime       virtualIpAddress
9510    4657    81      1585511362      192.168.101.91  1585511281      10.20.1.6
48586   52696   1956    1585514599      192.168.101.91  1585512643      10.20.1.6
11829   7399    153     1585514835      192.168.101.91  1585514682      10.20.1.6
13871   10318   330     1585518156      192.168.101.91  1585517826      10.20.1.6

这接近我预想的结果。现在(可能之前(，我需要将Unix时间戳转换为DateTime。

我想使用todateiso8601函数，但无法正确插入。

假设这对一个精通jq的人来说并不困难；-(

谢谢，p.