我要评论命令行导出json数据到csv
临近年终，经常遇到把接口数据导出到csv，再进一步做成图表放入ppt中的诉求，毕竟ppt才是最好的语言！
每次导出数据都要写一堆代码，未免太浪费时间，送你一串神奇的命令行指令，让你快速导出json到csv中，事半功倍！
处理json，肯定绕不过jq这个命令。之前的文章：《教你在命令行操作json》介绍了jq基础的用法。本篇文章就借着导出数据这个实际的需求，再介绍下jq的高级用法
简单
先来个简单版本的，接口响应内容如下，我们只想导出其中的code、name字段到scv
[
{"code": "nsw", "name": "new south wales", "level":"state", "country": "au"},
{"code": "ab", "name": "alberta", "level":"province", "country": "ca"},
{"code": "abd", "name": "aberdeenshire", "level":"council area", "country": "gb"},
{"code": "ak", "name": "alaska", "level":"state", "country": "us"}
]
$ cat j.json | jq -r '. | ["name", "code"], map([.name, .code])[] | @csv' "name","code" "new south wales","nsw" "alberta","ab" "aberdeenshire","abd" "alaska","ak"
可以先尝试自行理解上面jq的使用，下面我们加大难度，自动提取数据的全部字段，并添加表头
进阶
先看下进阶版本的全貌，为了换行更加清晰的展示，这里把jq的filter单独放入了一个文件，在执行jq的时候只需要指定-f file即可。效果和在命令行中一样。
# filters 文件内容
(map(keys) | add | unique) as $header
| map(. as $row | $header | map($row[.])) as $rows
| $header, $rows[]
| @csv
# j.json文件内容
# [
# {"code": "nsw", "name": "new south wales", "level":"state", "country": "au"},
# {"code": "ab", "name": "alberta", "level":"province", "country": "ca"},
# {"code": "abd", "name": "aberdeenshire", "level":"council area", "country": "gb"},
# {"code": "ak", "name": "alaska", "level":"state", "country": "us"}
# ]
$ cat j.json | jq -r -f filters | tee j.csv
"code","country","level","name"
"nsw","au","state","new south wales"
"ab","ca","province","alberta"
"abd","gb","council area","aberdeenshire"
"ak","us","state","alaska"
提取csv的表头
(map(keys) | add | unique) as $header 用来提取csv第一行需要的表头。逐个命令看下
✨ keys 对象所有key组成的数组
$ echo '{"code": "nsw", "name": "new south wales", "level":"state", "country": "au"}' | jq 'keys'
[
"code",
"country",
"level",
"name"
]
✨ map(f) 可以对数组的每一项进行f操作，然后合并结果
$ echo '[{"name": "foo"},{"name": "bar"},{"name": "foobar"}]' | jq 'map(.name)'
[
"foo",
"bar",
"foobar"
]
f可以是更复杂的函数，例如length可以获取字符串或数组的长度，把length放到map中，得到数组每一个元素的长度
$ echo '["foo", "bar", "foobar"]' | jq 'map(length)' [ 3, 3, 6 ]
所以map(keys)对于下面这段json来说。对数组中每一个元素执行keys，即对象所有key组成的数组
# j.json
[
{"code": "nsw", "name": "new south wales", "level":"state", "country": "au"},
{"code": "ab", "name": "alberta", "level":"province", "country": "ca"},
{"code": "abd", "name": "aberdeenshire", "level":"council area", "country": "gb"},
{"code": "ak", "name": "alaska", "level":"state", "country": "us"}
]
$ cat j.json | jq 'map(keys)'
[
[
"code",
"country",
"level",
"name"
],
[
"code",
"country",
"level",
"name"
],
[
"code",
"country",
"level",
"name"
],
[
"code",
"country",
"level",
"name"
]
]
✨ add | unique 顾名思义，首先将数组合并，然后再去重
$ cat j.json | jq 'map(keys) | add | unique' [ "code", "country", "level", "name" ]
(map(keys) | add | unique) as $header 总结就是遍历要转换成csv的每一条数据，取每一条数据的所有key，合并去重。相比于取数据的第一条作为表头，这种方式获取了所有数据的字段，避免第一条后面数据的字段多于第一条的情况
生成表格数据
map(. as $row | $header | map($row[.])) as $row就是生成表格内容的主要命令
最外层的map遍历处理每一行数据，我们看看如何对每一行进行处理
🌲 . as $row相当于给当前行命名成$row
🌲$header | map($row[.]) 此时上下文已经变成了$header
🌲🌲 ``map(遍历表头的每一个字段，从row中获取对应的值。类似、row["country"]、$row["level"]`这样
对每一行处理完后，就得到了多行的表格的内容区域
$ cat j.json | jq -r '(map(keys) | add | unique) as $header | map(. as $row | $header | map($row[.])) as $rows | $header, $rows[] ' [ "code", "country", "level", "name" ] [ "nsw", "au", "state", "new south wales" ] [ "ab", "ca", "province", "alberta" ] [ "abd", "gb", "council area", "aberdeenshire" ] [ "ak", "us", "state", "alaska" ]
输出成csv
@csv指令能很好的完成把数组转换成csv的工作。
最终完成的效果如下，说简单也简单，说复杂也复杂。命令有点长，往后滑👉
$ cat j.json | jq -r '(map(keys) | add | unique) as $header | map(. as $row | $header | map($row[.])) as $rows | $header, $rows[] | @csv' "code","country","level","name" "nsw","au","state","new south wales" "ab","ca","province","alberta" "abd","gb","council area","aberdeenshire" "ak","us","state","alaska"
课后题
有时候接口返回的数据可能会是如下结构，思考下如何利用jq完成csv的转换吧
{
"headers": [
"code",
"name",
"level",
"country"
],
"data": [
["nsw", "new south wales", "state", "au"],
["ab", "alberta", "province", "ca"],
["abd", "aberdeenshire", "council area", "gb"],
["ak", "alaska", "state", "us"]
]
以上就是使用命令行将json数据导出到csv(一行命令搞定)的详细内容，更多关于json数据导出到csv的资料请关注代码网其它相关文章！
发表评论