[MongoDB]「複数カラムでSELECT DISTINCT」を日次で取得する方法

MongoDBを勉強中です。
MySQLでやっていることをMongoDBでやるには？で詰まったので結果をメモっておきます。

この記事でわかること

  • MySQLのSELECT DISTINCTをMongoDBで書く方法
  • MongoDBで文字列の日付をISODate型に変換し、日次で集計する方法
  • MongoDBのaggregationでsortがうまくいかない時の見直しポイント

シチュエーション

以下のようなテーブルから、uidとdeviceのユニークな組み合わせのリストを、日別で取得したい。

カラム 型 説明
uid 文字列 ユーザーID
device 文字列 デバイス情報
created_at 文字列 日時

環境

  • MacOS 10.13.6
  • MySQL 5.6
  • MongoDB 4.0.6

MySQL

MySQLでは以下のように書くことができます。

テストデータ作成

# TABLE作成
CREATE TABLE sample_log (
uid VARCHAR(20) NOT NULL,
device VARCHAR(20) NOT NULL,
created_at DATETIME NOT NULL
);

# レコード挿入
INSERT INTO sample_log (uid, device, created_at) VALUES
 ('hoge','xxx','2019-02-01 00:00:00'),
 ('fuga','yyy','2019-02-01 00:00:01'),
 ('hoge','xxx','2019-02-01 00:00:02'),
 ('piyo','zzz','2019-02-01 00:00:03'),
 ('hoge','yyy','2019-02-01 00:00:04'),
 ('fuga','yyy','2019-02-01 00:00:05'),
 ('piyo','zzz','2019-02-01 00:00:06'),
 ('hoge','yyy','2019-02-02 00:00:04'),
 ('fuga','yyy','2019-02-02 00:00:05'),
 ('foo','xxx','2019-02-02 00:00:06'),
 ('bar','yyy','2019-02-02 00:00:07'),
 ('foo','xxx','2019-02-02 00:00:08'),
 ('bar','yyy','2019-02-02 00:00:09'),

抽出 : SELECT DISTINCTを使う書き方

SELECT
    DISTINCT uid, device, DATE_FORMAT(created_at, "%Y-%m-%d")
FROM
    sample_log
ORDER BY
    created_at, uid;

抽出 : GROUP BYを使う書き方

SELECT
    uid, device, DATE_FORMAT(created_at, "%Y-%m-%d")
FROM
    sample_log
GROUP BY
    uid, device, DATE_FORMAT(created_at, "%Y-%m-%d")
ORDER BY
    created_at, uid;

いずれも以下のような結果が得られます。

+------+--------+------------+
| uid  | device | created_at |
+------+--------+------------+
| fuga | yyy    | 2019-02-01 |
| hoge | xxx    | 2019-02-01 |
| hoge | yyy    | 2019-02-01 |
| piyo | zzz    | 2019-02-01 |
| bar  | yyy    | 2019-02-02 |
| foo  | xxx    | 2019-02-02 |
| fuga | yyy    | 2019-02-02 |
| hoge | yyy    | 2019-02-02 |
+------+--------+------------+

MongoDB

テストデータ作成

col = db.sample_log
col.insert(
    [
        {uid: 'hoge', device: 'xxx', created_at: '2019-02-01 00:00:00' },
        {uid: 'fuga', device: 'yyy', created_at: '2019-02-01 00:00:01' },
        {uid: 'hoge', device: 'xxx', created_at: '2019-02-01 00:00:02' },
        {uid: 'piyo', device: 'zzz', created_at: '2019-02-01 00:00:03' },
        {uid: 'hoge', device: 'yyy', created_at: '2019-02-01 00:00:04' },
        {uid: 'fuga', device: 'yyy', created_at: '2019-02-01 00:00:05' },
        {uid: 'piyo', device: 'zzz', created_at: '2019-02-01 00:00:06' },
        {uid: 'hoge', device: 'yyy', created_at: '2019-02-02 00:00:04' },
        {uid: 'fuga', device: 'yyy', created_at: '2019-02-02 00:00:05' },
        {uid: 'foo', device: 'xxx', created_at: '2019-02-02 00:00:06' },
        {uid: 'bar', device: 'yyy', created_at: '2019-02-02 00:00:07' },
        {uid: 'foo', device: 'xxx', created_at: '2019-02-02 00:00:08' },
        {uid: 'bar', device: 'yyy', created_at: '2019-02-02 00:00:09' }
    ]
);

抽出

DISTINCTは使わずに、MySQLでいうGROUP BYを使った書き方を、Aggregation Pipelineという機能を使ってやります。

col.aggregate(
    [
    {$project:
        {
            _id: 0,
            uid: 1,
            device: 1,
            created_at: 
            { 
                $toDate: "$created_at"
            }
        }
    },
    {$group:
        {
            _id:
            {
                uid: "$uid",
                device: "$device",
                created_at:
                {
                    $dateToString:
                    {
                        "format" : "%Y-%m-%d",
                        "date": "$created_at"
                    }
                }
            }
        }
    },
    {$sort:
        {
            "_id.created_at": 1,  "_id.uid": 1
        }
    }
    ]);

以下の結果が得られます。

{ "_id" : { "uid" : "fuga", "device" : "yyy", "created_at" : "2019-02-01" } }
{ "_id" : { "uid" : "hoge", "device" : "yyy", "created_at" : "2019-02-01" } }
{ "_id" : { "uid" : "hoge", "device" : "xxx", "created_at" : "2019-02-01" } }
{ "_id" : { "uid" : "piyo", "device" : "zzz", "created_at" : "2019-02-01" } }
{ "_id" : { "uid" : "bar", "device" : "yyy", "created_at" : "2019-02-02" } }
{ "_id" : { "uid" : "foo", "device" : "xxx", "created_at" : "2019-02-02" } }
{ "_id" : { "uid" : "fuga", "device" : "yyy", "created_at" : "2019-02-02" } }
{ "_id" : { "uid" : "hoge", "device" : "yyy", "created_at" : "2019-02-02" } }

ポイント1. $toDateで文字列の日付をISODateに変換

まずは文字列の日付をISODateに変換します。
$toDateと$dateFromStringの2つの方法がありますが、クエリが短くて済む$toDateを使います。

注意点として、日付の文字列はUTCの時間でISODate型に変換されます。
なので、文字列の日付がJSTならば、変換時に少し加工する必要が出てきます。

が、今回は主題ではないため割愛。

ポイント2. $groupで一意の組み合わせを抽出

前段で、uid, device, (ISODate型に変換された)created_atが出揃ったので、それらをグルーピングします。
created_atは、$dateToStringを使い、”%Y-%m-%d”の形式にしてからグルーピングすることで、同じ日のデータをまとめています。

ポイント3. $sortでcreated_at順に

created_atでソートします。

ここでかなり詰まりました。
$sortの対象が入れ子構造の場合、ドットつなぎの文字列で指定する必要があります。

今回のクエリでいうと、

NG

{$sort:
        {
            "created_at": 1
        }
    }

{ } という結果が返ってきてしまいます。

OK

{$sort:
        {
            "_id.created_at": 1
        }
    }

正しく返ります。なぜなら前段の$groupにより、出力結果は、

{ "_id" : { "uid" : "piyo", "device" : "zzz", "created_at" : "2019-02-01" } }

というような構造になっているため、_idの入れ子になっているcreated_atを指定する必要があるためです。

参考

Aggregation Pipeline

日付変換

開発MongoDB, MySQL