如何使用JQ在命令行中将JSON列表提取到CSV中? [英] How do I extract a JSON list into a CSV in command line using JQ?

问题描述

我有以下JSON数据:

I have the following JSON data:

% cat test2 

{"day":"2020-07-15","map":
{"a":"ask","b":"bid","t":"timestamp"},"msLatency":52,"pair":"EUR/USD","status":"success","ticks":[
{"b":1.14105,"a":1.14106,"x":48,"t":1594771200000},
{"b":1.14105,"a":1.14106,"x":48,"t":1594771201000},
{"b":1.14103,"a":1.14104,"x":48,"t":1594771202000},
{"b":1.141,"a":1.1413,"x":48,"t":1594771203000},
{"b":1.14103,"a":1.14104,"x":48,"t":1594771205000},
{"b":1.14094,"a":1.14095,"x":48,"t":1594778803000}],"type":"forex"}

我想得到:

1.14105,1.14106,1594771200000
1.14105,1.14106,1594771201000
1.14103,1.14104,1594771202000
1.141,1.1413,1594771203000
1.14103,1.14104,1594771205000
1.14094,1.14095,1594778803000

理想情况下，输出也应填充零，其中1和5是参数，用于指定前两列是具有1个自然位和5个小数位的数字(尽管此步骤可以通过awk轻松完成): /p>

Ideally the output shall also pad with zeros, with 1 and 5 being parameters to specify that the first two columns are numbers with 1 natural place and 5 decimal places (although this step can be done easily with awk):

1.14105,1.14106,1594771200000
1.14105,1.14106,1594771201000
1.14103,1.14104,1594771202000
1.14100,1.14130,1594771203000
1.14103,1.14104,1594771205000
1.14094,1.14095,1594778803000

我已经通过JQ尝试过此操作

I have tried this with JQ:

 % cat test2 | jq '.ticks'
[
  {
    "b": 1.14105,
    "a": 1.14106,
    "x": 48,
    "t": 1594771200000
  },
  {
    "b": 1.14105,
    "a": 1.14106,
    "x": 48,
    "t": 1594771201000
  },
  {
    "b": 1.14103,
    "a": 1.14104,
    "x": 48,
    "t": 1594771202000
  },
  {
    "b": 1.141,
    "a": 1.1413,
    "x": 48,
    "t": 1594771203000
  },
  {
    "b": 1.14103,
    "a": 1.14104,
    "x": 48,
    "t": 1594771205000
  },
  {
    "b": 1.14094,
    "a": 1.14095,
    "x": 48,
    "t": 1594778803000
  }
]

但是我仍然坚持如何将其转换为CSV.

But I am stuck on how to turn this into a CSV.

作为参考，我之前进行了以下解析，在这里使用JQ更为简单:

Just as reference, I previously had the following parsing, using JQ here is much simpler option:

cat test2 |
sed -e 's/{\"/#{\"/g' |
tr '#' '\n' |
grep -v "timestamp" |
grep -v "day" |
sed '/^[[:space:]]*$/d' |
sed 's/].*$//g' |
sed 's/{//g' |
sed 's/},//g' |
sed 's/}//g' |
sed 's/\"//g' |
awk -F '[:,]' -v decimal_places=5 -v integer_places=1 '{
        for(i=1; i<=NF; i=i+2) {
                value[$i]=$(i+1);
        };
        format_price="%0" integer_places "." decimal_places "f"
        format=format_price " " format_price " %d\n"
        printf(format,value["b"],value["a"],value["t"]);
}'

推荐答案

这是一个解决方案:

jq -r '.ticks[] | [.b, .a, .t] | join(",")' test2

1.14105,1.14106,1594771200000
1.14105,1.14106,1594771201000
1.14103,1.14104,1594771202000
1.141,1.1413,1594771203000
1.14103,1.14104,1594771205000
1.14094,1.14095,1594778803000

它不会填充到5位数字.我不知道如何在jq中执行此操作.我仍然认为使用另一种语言会更容易.

It doesn't do the padding to 5 digits. I have no idea how to do that in jq. I still think another language would be easier.

这篇关于如何使用JQ在命令行中将JSON列表提取到CSV中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！