快捷導(dǎo)航

使用命令行將json數(shù)據(jù)導(dǎo)出到csv(一行命令搞定)

更新時(shí)間：2024年01月24日 11:51:50 作者：涼涼的知識(shí)庫(kù)

這篇文章主要為大家介紹了使用命令行將json數(shù)據(jù)導(dǎo)出到csv,一行命令搞定的方法詳解,有需要的朋友可以借鑒參考下,希望能夠有所幫助,祝大家多多進(jìn)步,早日升職加薪

命令行導(dǎo)出json數(shù)據(jù)到csv

臨近年終，經(jīng)常遇到把接口數(shù)據(jù)導(dǎo)出到csv，再進(jìn)一步做成圖表放入PPT中的訴求，畢竟PPT才是最好的語言！

每次導(dǎo)出數(shù)據(jù)都要寫一堆代碼，未免太浪費(fèi)時(shí)間，送你一串神奇的命令行指令，讓你快速導(dǎo)出json到csv中，事半功倍！

處理json，肯定繞不過jq這個(gè)命令。之前的文章：《教你在命令行操作JSON》介紹了jq基礎(chǔ)的用法。本篇文章就借著導(dǎo)出數(shù)據(jù)這個(gè)實(shí)際的需求，再介紹下jq的高級(jí)用法

簡(jiǎn)單

先來個(gè)簡(jiǎn)單版本的，接口響應(yīng)內(nèi)容如下，我們只想導(dǎo)出其中的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的使用，下面我們加大難度，自動(dòng)提取數(shù)據(jù)的全部字段，并添加表頭

進(jìn)階

先看下進(jìn)階版本的全貌，為了換行更加清晰的展示，這里把jq的filter單獨(dú)放入了一個(gè)文件，在執(zhí)行jq的時(shí)候只需要指定-f file即可。效果和在命令行中一樣。

# filters 文件內(nèi)容
(map(keys) | add | unique) as $header
    | map(. as $row | $header | map($row[.])) as $rows
    | $header, $rows[]
    | @csv

# j.json文件內(nèi)容
# [
#     {"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第一行需要的表頭。逐個(gè)命令看下

? keys 對(duì)象所有key組成的數(shù)組

$ echo '{"code": "NSW", "name": "New South Wales", "level":"state", "country": "AU"}' | jq 'keys'
[
  "code",
  "country",
  "level",
  "name"
]

? map(f) 可以對(duì)數(shù)組的每一項(xiàng)進(jìn)行f操作，然后合并結(jié)果

$ echo '[{"name": "foo"},{"name": "bar"},{"name": "foobar"}]' | jq 'map(.name)'
[
  "foo",
  "bar",
  "foobar"
]

f可以是更復(fù)雜的函數(shù)，例如length可以獲取字符串或數(shù)組的長(zhǎng)度，把length放到map中，得到數(shù)組每一個(gè)元素的長(zhǎng)度

$ echo '["foo", "bar", "foobar"]' | jq 'map(length)'
[
  3,
  3,
  6
]

所以map(keys)對(duì)于下面這段json來說。對(duì)數(shù)組中每一個(gè)元素執(zhí)行keys，即對(duì)象所有key組成的數(shù)組

# 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 顧名思義，首先將數(shù)組合并，然后再去重

$ cat j.json | jq 'map(keys) | add | unique'
[
  "code",
  "country",
  "level",
  "name"
]

(map(keys) | add | unique) as $header 總結(jié)就是遍歷要轉(zhuǎn)換成csv的每一條數(shù)據(jù)，取每一條數(shù)據(jù)的所有key，合并去重。相比于取數(shù)據(jù)的第一條作為表頭，這種方式獲取了所有數(shù)據(jù)的字段，避免第一條后面數(shù)據(jù)的字段多于第一條的情況

生成表格數(shù)據(jù)

map(. as $row | $header | map($row[.])) as $row就是生成表格內(nèi)容的主要命令

最外層的map遍歷處理每一行數(shù)據(jù)，我們看看如何對(duì)每一行進(jìn)行處理

?? . as $row相當(dāng)于給當(dāng)前行命名成$row

??$header | map($row[.]) 此時(shí)上下文已經(jīng)變成了$header

???? ``map(遍歷表頭的每一個(gè)字段，從row中獲取對(duì)應(yīng)的值。類似、row["country"]、$row["level"]`這樣

對(duì)每一行處理完后，就得到了多行的表格的內(nèi)容區(qū)域

$ 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指令能很好的完成把數(shù)組轉(zhuǎn)換成csv的工作。

最終完成的效果如下，說簡(jiǎn)單也簡(jiǎn)單，說復(fù)雜也復(fù)雜。命令有點(diǎn)長(zhǎng)，往后滑??

$ 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"

課后題

有時(shí)候接口返回的數(shù)據(jù)可能會(huì)是如下結(jié)構(gòu)，思考下如何利用jq完成csv的轉(zhuǎn)換吧

{
    "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數(shù)據(jù)導(dǎo)出到csv(一行命令搞定)的詳細(xì)內(nèi)容，更多關(guān)于json數(shù)據(jù)導(dǎo)出到csv的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！

您可能感興趣的文章: