jq コマンドで検索と整形

最近よく使う jq コマンドを自分用にメモ。

※ jq 1.5 を使用しています。

内容

• key / value の検索
• 文字列の分割
• 変数
• JSON の配列と sort

サンプルデータ

$ cat sample.json
{"time":"2017-10-01 00:00:00 +0900","request":"GET /index.html HTTP/1.1","status":"200","ua":"Mozilla/5.0 (iPhone; CPU iPhone OS 11_0 like Mac OS X) AppleWebKit/604.1.38 (KHTML, like Gecko) Version/11.0 Mobile/15A356 Safari/604.1","referer":"http://www.example.com/hoge.php?item=1"}
{"time":"2017-10-01 10:00:00 +0900","request":"GET /index.html HTTP/1.1","status":"200","ua":"Mozilla/5.0 (Macintosh; Intel Mac OS X 10_13) AppleWebKit/604.1.38 (KHTML, like Gecko) Version/11.0 Safari/604.1.38","referer":"http://www.example.com/fuga.php?item=2"}
{"time":"2017-10-01 20:00:00 +0900","request":"GET /index.html HTTP/1.1","status":"200","ua":"Mozilla/5.0 (iPhone; CPU iPhone OS 11_0 like Mac OS X) AppleWebKit/604.1.38 (KHTML, like Gecko) Version/11.0 Mobile/15A356 Safari/604.1"}
{"time":"2017-10-02 00:00:00 +0900","request":"GET /index.html HTTP/1.1","status":"200","ua":"Mozilla/5.0 (Macintosh; Intel Mac OS X 10_13) AppleWebKit/604.1.38 (KHTML, like Gecko) Version/11.0 Safari/604.1.38","referer":"http://www.example.com/hoge.php?item=3"}

key / value の検索

key で検索して特定の key をタブ区切りで出力

$ jq -r 'select(has("referer")) | .time +"\t"+ .referer' sample.json
2017-10-01 00:00:00 +0900 http://www.example.com/hoge.php?item=1
2017-10-01 10:00:00 +0900 http://www.example.com/fuga.php?item=2
2017-10-02 00:00:00 +0900 http://www.example.com/hoge.php?item=3

value で検索して特定の key をタブ区切りで出力

$ jq -r 'select(.ua | test("iPhone")) | .time +"\t"+ .ua' sample.json
2017-10-01 00:00:00 +0900 Mozilla/5.0 (iPhone; CPU iPhone OS 11_0 like Mac OS X) AppleWebKit/604.1.38 (KHTML, like Gecko) Version/11.0 Mobile/15A356 Safari/604.1
2017-10-01 20:00:00 +0900 Mozilla/5.0 (iPhone; CPU iPhone OS 11_0 like Mac OS X) AppleWebKit/604.1.38 (KHTML, like Gecko) Version/11.0 Mobile/15A356 Safari/604.1

文字列の分割

特定の文字列で分割

referer を抽出 (前処理)

$ jq -r 'select(has("referer")) | .referer' sample.json
http://www.example.com/hoge.php?item=1
http://www.example.com/fuga.php?item=2
http://www.example.com/hoge.php?item=3

/ で分割

$ jq -r 'select(has("referer")) | .referer | split("/") | .[3]' sample.json
hoge.php?item=1
fuga.php?item=2
hoge.php?item=3

さらに ? で分割

$ jq -r 'select(has("referer")) | .referer | split("/") | .[3] | split("?") | .[1]' sample.json
item=1
item=2
item=3

変数

途中結果を変数に代入して使い回し

$ jq -r 'select(has("referer")) | .referer | split("/") | .[3] as $tmp | ($tmp | split("?") | .[0]) +"\t"+ ($tmp | split("=") | .[1])' sample.json
hoge.php  1
fuga.php  2
hoge.php  3

JSON の配列と sort

JSON の配列を出力 (一度配列にしないと sort できない)
-s は --slurp でもよい

$ jq -s . sample.json
[
  {
    "time": "2017-10-01 00:00:00 +0900",
    "request": "GET /index.html HTTP/1.1",
    "status": "200",
    "ua": "Mozilla/5.0 (iPhone; CPU iPhone OS 11_0 like Mac OS X) AppleWebKit/604.1.38 (KHTML, like Gecko) Version/11.0 Mobile/15A356 Safari/604.1",
    "referer": "http://www.example.com/hoge.php?item=1"
  },
  {
    "time": "2017-10-01 10:00:00 +0900",
    "request": "GET /index.html HTTP/1.1",
    "status": "200",
    "ua": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_13) AppleWebKit/604.1.38 (KHTML, like Gecko) Version/11.0 Safari/604.1.38",
    "referer": "http://www.example.com/fuga.php?item=2"
  },
  {
    "time": "2017-10-01 20:00:00 +0900",
    "request": "GET /index.html HTTP/1.1",
    "status": "200",
    "ua": "Mozilla/5.0 (iPhone; CPU iPhone OS 11_0 like Mac OS X) AppleWebKit/604.1.38 (KHTML, like Gecko) Version/11.0 Mobile/15A356 Safari/604.1"
  },
  {
    "time": "2017-10-02 00:00:00 +0900",
    "request": "GET /index.html HTTP/1.1",
    "status": "200",
    "ua": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_13) AppleWebKit/604.1.38 (KHTML, like Gecko) Version/11.0 Safari/604.1.38",
    "referer": "http://www.example.com/hoge.php?item=3"
  }
]

JSON (の配列) をソート
.[] はすべて, 最初の要素だけなら .[0]

$ jq -s . sample.json | jq 'sort_by(.ua) | .[]' 
{
  "time": "2017-10-01 10:00:00 +0900",
  "request": "GET /index.html HTTP/1.1",
  "status": "200",
  "ua": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_13) AppleWebKit/604.1.38 (KHTML, like Gecko) Version/11.0 Safari/604.1.38",
  "referer": "http://www.example.com/fuga.php?item=2"
}
{
  "time": "2017-10-02 00:00:00 +0900",
  "request": "GET /index.html HTTP/1.1",
  "status": "200",
  "ua": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_13) AppleWebKit/604.1.38 (KHTML, like Gecko) Version/11.0 Safari/604.1.38",
  "referer": "http://www.example.com/hoge.php?item=3"
}
{
  "time": "2017-10-01 00:00:00 +0900",
  "request": "GET /index.html HTTP/1.1",
  "status": "200",
  "ua": "Mozilla/5.0 (iPhone; CPU iPhone OS 11_0 like Mac OS X) AppleWebKit/604.1.38 (KHTML, like Gecko) Version/11.0 Mobile/15A356 Safari/604.1",
  "referer": "http://www.example.com/hoge.php?item=1"
}
{
  "time": "2017-10-01 20:00:00 +0900",
  "request": "GET /index.html HTTP/1.1",
  "status": "200",
  "ua": "Mozilla/5.0 (iPhone; CPU iPhone OS 11_0 like Mac OS X) AppleWebKit/604.1.38 (KHTML, like Gecko) Version/11.0 Mobile/15A356 Safari/604.1"
}

条件に一致する要素を取得して再度配列に変換

$ jq -s . sample.json | jq '.[] | select(.ua | test("Macintosh"))' | jq -s .
[
  {
    "time": "2017-10-01 10:00:00 +0900",
    "request": "GET /index.html HTTP/1.1",
    "status": "200",
    "ua": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_13) AppleWebKit/604.1.38 (KHTML, like Gecko) Version/11.0 Safari/604.1.38",
    "referer": "http://www.example.com/fuga.php?item=2"
  },
  {
    "time": "2017-10-02 00:00:00 +0900",
    "request": "GET /index.html HTTP/1.1",
    "status": "200",
    "ua": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_13) AppleWebKit/604.1.38 (KHTML, like Gecko) Version/11.0 Safari/604.1.38",
    "referer": "http://www.example.com/hoge.php?item=3"
  }
]

参考

jq コマンドが強力すぎてヤバい件