Redash で MongoDB サンプル

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

group の `_id` を `null` にしておく。

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

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

```json
{
    "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 するサンプル

```json
{
    "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 するサンプル

```json
{
    
    "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"
            }
        }
    ]
}
```

# 月で集計するサンプル

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/45822/1c32997b-9716-fa8a-c65e-b856be9c032d.png)

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

クエリは以下

```json: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` で綺麗にできました。

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


# ObjectId で match 条件をみる

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

# 日付入力を可能にする

![スクリーンショット 2020-12-13 18.58.57.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/45822/6972e914-f5b2-1e03-6487-05d2d66b20bb.png)


```json
{
    "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
            }
        }
    ]
}
```

# 日付フォーマット

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

# map

```json
{
    "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 したいだけ

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

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

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

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

```json
{
    "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` でやる方法もある。

```json
                "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
                } }}
```