Redash で MongoDB サンプル

ドキュメントの総数をカウントする

group の _id を null にしておく。

{
    "collection": "mail_logs",
    "aggregate": [
        {
            "$group": {
                "_id": null,
                "cnt": {
                    "$sum": 1
                }
            }
        }
    ]
}

割り算したりするサンプル

{
    "collection": "campaign_mails",
    "aggregate": [
        {
            "$match": {
                "created_at": { "$gt" : {"$humanTime": "last month"} },
                "statistics": { "$ne": null }
            }
        },
        { "$limit": 800 },
        {
            "$project": {
                "created_at": "$created_at",
                "site_id": "$site_id",
                "subject": "$subject",
                "processed_count": "$statistics.processed_count",
                "delivered_count": "$statistics.delivered_count",
                "opened_count": "$statistics.opened_count",
                "clicked_count": "$statistics.clicked_count",
                "CTR": { 
                    "$cond": [
                        { "$eq": [ "$statistics.opened_count", 0 ] },
                        "N/A",
                        { "$divide": ["$statistics.clicked_count", "$statistics.opened_count"] }
                    ]
                },
                "OpenRate": {
                    "$cond": [
                        { "$eq": [ "$statistics.delivered_count", 0 ] },
                        "N/A",
                        { "$divide": ["$statistics.opened_count", "$statistics.delivered_count"] }
                    ]
                }
            }
        },
        {
            "$sort": [
                { 
                    "name": "processed_count",
                    "direction": -1
                }
            ]
        }
    ]
}

group するサンプル

{
    "collection": "campaign_mails",
    "aggregate": [
        {
            "$match": {
                "created_at": { "$gt" : {"$humanTime": "last month"} },
                "statistics": { "$ne": null }
            }
        },
        { "$limit": 800 },
        {
            "$group": {
                "_id": "$site_id",
                "count": {
                    "$sum": 1
                },
                "processed_count": {
                    "$sum": "$statistics.processed_count"
                },
                "delivered_count": {
                    "$sum": "$statistics.delivered_count"
                },
                "opened_count": {
                    "$sum": "$statistics.opened_count"
                }

            }
        }
    ]
}

lookup するサンプル

{

    "collection": "campaign_mails",
    "aggregate": [
        {
            "$match": {
                "created_at": { "$gt": { "$humanTime": "last month" } }
            }
        },
        {
            "$group": {
                "_id": "$site_id",
                "count": {
                    "$sum": 1
                }
            }
        },
        {
            "$lookup": {
                "from": "sites",
                "localField": "_id",
                "foreignField": "_id",
                "as": "sites"
            }
        },
        {
            "$unwind": "$sites"
        },
        {
            "$project": {
                "site_title": "$sites.title",
                "count": "$count"
            }
        }
    ]
}

月で集計するサンプル

完成イメージは上の図です。

クエリは以下

not_cool

{
    "collection": "sample_collection",
    "aggregate": [
        { 
            "$group": {
                "_id": {
                    "year": { "$year": "$created_at" },
                    "month": { "$month": "$created_at" }
                },
                "count": { "$sum": 1 }
            }
        },
        {
          "$addFields": {"yearmonth": {"$add": [{"$multiply": ["$_id.year",100]}, "$_id.month"]}}
        },
        {
            "$project": {
                "count": 1,
                "yearmonth": 1
            }
        }
   ]
}

$toString や $convert が使えずに年と月を concat できなかったので、、無理やり数値で処理しています。

（追記）と思ったけど $dateToString で綺麗にできました。

cool

{
    "collection": "customer_lists",
    "aggregate": [
        {
            "$group": {
                "_id": { "$dateToString": { "format": "%Y-%m", "date": "$created_at" } },
                "count": { "$sum": 1 }
            }
        },
        {
            "$sort": [
                { "name": "_id", "direction": 1 }
            ]
        }
    ]
}

ObjectId で match 条件をみる

{
    "collection": "customers",
    "aggregate": [
        {
            "$match": {
                "site_id": { "$nin": [
                    { "$oid": "552242d169702d1b40c61300" },
                    { "$oid": "543d1e3e69702d0c76240000" }
                ]}
            }
        },
        {
            "$group": {
                "_id": {"$year": "$created_at" },
                "count": { "$sum": 1 }
            }
        }
    ]
}

日付入力を可能にする

{
    "collection": "contacts",
    "aggregate": [
        {
            "$match": {
                "created_at": { "$gt" : {"$humanTime": "{{ from }}"} }
            }
        },
        {
            "$group": {
                "_id": { 
                    "year": { "$year": "$created_at" },
                    "month": { "$month": "$created_at" },
                    "day": { "$dayOfMonth": "$created_at" }
                },
                "count": { "$sum": 1 }
            }
        },
        {
            "$addFields": {
                "ymd": { "$add": [
                    { "$multiply": ["$_id.year", 10000] },
                    { "$multiply": ["$_id.month", 100] },
                    "$_id.day"
                ]}
            }
        },
        {
            "$project": {
                "_id": 0,
                "ymd": 1,
                "count": 1
            }
        }
    ]
}

日付フォーマット

"$project": {
  "created_at": { 
    "$dateToString": { 
      "format": "%Y-%m-%d %H:%M:%S",
      "date": "$created_at" 
    }
  }
}

map

{
    "collection": "mail_action_histories",
    "aggregate": [

        {
            "$project": { "mail_actions": {
                "$map": {
                    "input": "$mail_actions",
                    "as": "ma",
                    "in": { 
                       "ation_type": "$$ma.action_type",
                       "msg_id": "$$ma.mail_msg_id"
                   }
                }
            } }
        },
        {
            "$limit": 2
        }
    ]
}

count したいだけ

{
    "collection": "totalize_cvs",
    "query": {
        "type": "conversion"
    },
    "count": true
}

{
    "collection": "customer_lists",
    "aggregate": [
        {
            "$group": {
                "_id": null,
                "count": { "$sum": 1 }
            }
        }
    ]
}

count ロジックを条件で分ける

たとえば、type_create が import / manual で別れているときに、それぞれの数をカウントしたい場合。

{
    "collection": "customer_lists",
    "aggregate": [
        {
            "$group": {
                "_id": { 
                    "$dateToString": { "format": "%Y-%m", "date": "$created_at" }
                },
                "cnt_import": { "$sum": { "$cond": [{"$eq": ["$type_create", "import"]}, 1, 0] } },
                "cnt_manual": { "$sum": { "$cond": [{"$eq": ["$type_create", "manual"]}, 1, 0] } }
            }
        },
        {
            "$sort": [
                { "name": "_id", "direction": 1 }
            ]
        }
    ]
}

$cond じゃなくて $switch でやる方法もある。

                "cnt_import": { "$sum": { "$cond": [{"$eq": ["$type_create", "import"]}, 1, 0] } },
                "cnt_manual": { "$sum": { "$cond": [{"$eq": ["$type_create", "manual"]}, 1, 0] } },
                "cnt_other": { "$sum": { "$switch": {
                    "branches": [
                        { "case": { "$eq": [ "$type_create", "import" ] }, "then": 0 },
                        { "case": { "$eq": [ "$type_create", "manual" ] }, "then": 0 }
                    ],
                    "default": 1
                } }}