jq コマンドで検索と整形

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

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

## 内容

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

## サンプルデータ

```sh
$ 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 をタブ区切りで出力

```sh
$ 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 をタブ区切りで出力

```sh
$ 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 を抽出 (前処理)

```sh
$ 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
```

`/` で分割

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

さらに `?` で分割

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

## 変数

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

```sh
$ 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` でもよい

```sh
$ 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]`


```sh
$ 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"
}
```

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

```sh
$ 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 コマンドが強力すぎてヤバい件](http://momijiame.tumblr.com/post/51299899637/jq-コマンドが強力すぎてヤバい件)