jqコマンドで集計 group by, max, sum, count

2021年12月31日

jqコマンド

jq コマンドで集計（最大、合計、数、平均、並び替え）をする方法を紹介します。jq のバージョンは 1.6。

以前書いた jq コマンドの基本的な使い方の続きです。

jqコマンド
jqコマンド覚え書き

JSON から必要なものを取り出すときに使う jq コマンド。使い方をすぐ忘れてしまって毎回 jq Manual を見て ...

続きを見る

目次

  1. サンプルの JSON
  2. 最大値（max_by）
  3. 合計値（add）
  4. 配列の数（length）
  5. グループごとの最大値 (group_by, max_by)
  6. グループごとの合計値 (group_by, add)
  7. グループごとの配列の数 (group_by, length)
  8. グループごとの平均 (group_by, add/length)
  9. 集計後の並び替え (sort_by)
  10. 絞り込んでから集計 (select)
  11. reduce を使った集計

サンプルの JSON

サンプルとして以下の JSON を使います。注文データのイメージです。ターミナルで以下のコマンドを実行して $JSON という変数に入れておきます。

JSON=$(cat << EOS
[
  {
    "user": "alice",
    "item": "apple",
    "price": 100,
    "created_at": "2021-12-25T17:00:00Z"
  },
  {
    "user": "bob",
    "item": "orange",
    "price": 150,
    "created_at": "2021-12-25T17:10:00Z"
  },
  {
    "user": "carol",
    "item": "apple",
    "price": 100,
    "created_at": "2021-12-25T17:20:00Z"
  },
  {
    "user": "alice",
    "item": "orange",
    "price": 150,
    "created_at": "2021-12-25T17:30:00Z"
  },
  {
    "user": "alice",
    "item": "banana",
    "price": 200,
    "created_at": "2021-12-25T17:40:00Z"
  },
  {
    "user": "carol",
    "item": "apple",
    "price": 100,
    "created_at": "2021-12-25T17:50:00Z"
  }
]
EOS
)

最大値（max_by）

created_at の最大値を求める例です。max_by() でキーを指定して最大値を求めます。

echo $JSON | jq '. | max_by(.created_at)'
{
  "user": "carol",
  "item": "apple",
  "price": 100,
  "created_at": "2021-12-25T17:50:00Z"
}

合計値（add）

price の合計値を求める例です。addフィルタを使います。

echo $JSON | jq '[.[].price]|add'
800

配列の数（length）

配列の要素数を数える例です。length フィルタを使います。

echo $JSON | jq '.|length'
6

グループごとの最大値 (group_by, max_by)

ここからはグループごとに集計する例を紹介します。単純な集計より、こちらの方がよく使います。

最初は user ごとに created_at の最大値を集計する例です。SQL の max, group by のイメージです。

echo $JSON | jq 'group_by(.user) | map(max_by(.created_at))'
[
  {
    "user": "alice",
    "item": "banana",
    "price": 200,
    "created_at": "2021-12-25T17:40:00Z"
  },
  {
    "user": "bob",
    "item": "orange",
    "price": 150,
    "created_at": "2021-12-25T17:10:00Z"
  },
  {
    "user": "carol",
    "item": "apple",
    "price": 100,
    "created_at": "2021-12-25T17:50:00Z"
  }
]

group_by() で user ごとの配列ができるので、map() を使って配列の要素ごとに max_by() を実行し最大値を求めます。map() は他のプログラミング言語と同じように、配列の要素それぞれに指定した関数を実行して新しい配列を返します。

詳細は jq Manual を参照。

グループごとの合計値 (group_by, add)

user ごとに price を合計する例です。SQL の sum, group by のイメージです。

echo $JSON | jq 'group_by(.user) | map({"user": .[0].user, "price": [.[].price]|add})'
[
  {
    "user": "alice",
    "price": 450
  },
  {
    "user": "bob",
    "price": 150
  },
  {
    "user": "carol",
    "price": 200
  }
]

group_by() のあと、map() で配列ごとに add フィルタを使って合計を求めます。map() の引数の user の値を .[0].user として配列の先頭のものだけ取ってくるのがポイント。

グループごとの配列の数 (group_by, length)

user ごとにまとめた配列の要素数を求める例です。SQL の count, group by のイメージです。

echo $JSON | jq 'group_by(.user) | map({"user": .[0].user, "length": [.[]]|length})'
[
  {
    "user": "alice",
    "length": 3
  },
  {
    "user": "bob",
    "length": 1
  },
  {
    "user": "carol",
    "length": 2
  }
]

グループごとの平均 (group_by, add/length)

user ごとの price の平均を求める例です。平均を求める関数は無いようなので計算します。

echo $JSON | jq 'group_by(.user) | map({"user": .[0].user, "average": ([.[].price]|add/length)})'
[
  {
    "user": "alice",
    "average": 150
  },
  {
    "user": "bob",
    "average": 150
  },
  {
    "user": "carol",
    "average": 100
  }
]

計算は ([.[].price]|add/length)})のように、かっこで囲む必要があります。かっこについて詳細は jq Manual (Parenthesis) を参照。

集計後の並び替え (sort_by)

先ほどの price の合計値を小さい順に並び替える例です。詳しくは jq Manual (sort, sort_by(path_expression)) を参照。

echo $JSON | jq 'group_by(.user) | map({"user": .[0].user, "price": [.[].price]|add}) | sort_by(.price)'
[
  {
    "user": "bob",
    "price": 150
  },
  {
    "user": "carol",
    "price": 200
  },
  {
    "user": "alice",
    "price": 450
  }
]

絞り込んでから集計 (select)

select(boolean_expression) でデータを絞り込んでから集計します。select() については jq Manual (select) を参照。

echo $JSON | jq 'map(select(.created_at >= "2021-12-25T17:30:00Z")) | group_by(.user) | map({"user": .[0].user, "length": [.[]]|length})'
[
  {
    "user": "alice",
    "length": 2
  },
  {
    "user": "carol",
    "length": 1
  }
]

reduce を使った集計

reduce() を使うと複雑な集計ができます。先ほどの合計値を求める例を reduce() を使って書き換えるとこのようになります。標準関数ではできない集計をするときに使います。詳細は jq Manual (Reduce) を参照。

echo $JSON | jq 'group_by(.user) | map({"user": .[0].user, "price": (reduce .[].price as $price (0; . + $price))})'
[
  {
    "user": "alice",
    "price": 450
  },
  {
    "user": "bob",
    "price": 150
  },
  {
    "user": "carol",
    "price": 200
  }
]

-技術ブログ
-