mongodb在单个查询中使用多个$group

huwehgph 于 2023-05-17 发布在 Go

关注(0)|答案(3)|浏览(152)

我在mongodb中存储了类似的文档：

{
    "_id" : ObjectId("----"),
    "status" : "pending",
    "user" : "huSWFekrPkw_xwtqDueAm4j4tHiuPJf3",
    "type" : "inMemory",
    "question" : "Hello, How are you?",
    "intent" : "Greeting",
    "createdAt" : ISODate("2018-07-24T06:33:59.399Z"),
    "updatedAt" : ISODate("2018-07-24T06:33:59.399Z"),
}
{
    "_id" : ObjectId("----"),
    "status" : "trained",
    "user" : "huSWFekrPkw_xwtqDueAm4j4tHiuPJf3",
    "type" : "inMemory",
    "question" : "Holla",
    "intent" : "Greeting",
    "createdAt" : ISODate("2018-07-25T06:33:59.399Z"),
    "updatedAt" : ISODate("2018-07-25T06:33:59.399Z"),
}
{
    "_id" : ObjectId("----"),
    "status" : "trained",
    "user" : "huSWFekrPkw_xwtqDueAm4j4tHiuPJf3",
    "type" : "inMemory",
    "question" : "want to talk with agent?",
    "intent" : "Agent",
    "createdAt" : ISODate("2018-07-26T06:33:59.399Z"),
    "updatedAt" : ISODate("2018-07-26T06:33:59.399Z"),
}

我想要的聚合管道：

intent上的$组
status上的$组
1.在这两组结果的基础上，我想过滤出有多少是未决的，并为每个特定的Intent进行了训练。与Greeting intent一样，我有2个待处理文档和1个已训练文档。
1.后来我还想，有多少文档的Greeting意图在今天，过去7天或上个月.
所以最终的文档看起来像这样：

{
    "intent" : "Greeting",
    "status_pending" : 1,
    "status_trained" : 2,
    "last_day" : 1,
    "last_seven_day" : 3,
    "last_month" : 7
}
{
    "intent" : "Agent",
    "status_pending" : 1,
    "status_trained" : 1,
    "last_day" : 1,
    "last_seven_day" : 2,
    "last_month" : 3
}

mongodb

来源：https://stackoverflow.com/questions/51510984/mongodb-multiple-group-in-single-query

3条答案

按热度按时间

fdbelqdn1#

您可以为每个intent使用$group和$push所有日期和状态。然后你可以使用$filter和$size来计算每个过滤器有多少项：

db.col.aggregate([
    {
        $group: {
            _id: "$intent",
            statuses: { $push: "$status" },
            dates: { $push: "$createdAt" },
        }
    },
    {
        $project: {
            _id: 0,
            intent: "$_id",
            status_pending: {
                $size: { $filter: { input: "$statuses", as: "s", cond: { $eq: [ "$$s", "pending" ] } } }
            },
            status_trained: {
                $size: { $filter: { input: "$statuses", as: "s", cond: { $eq: [ "$$s", "trained" ] } } }
            },
            last_day: {
                $size: { $filter: { input: "$dates", as: "d", cond: { $gt: [ "$$d", new Date(new Date().setDate(new Date().getDate()-1)) ] } } }
            },
            last_seven_day: {
                $size: { $filter: { input: "$dates", as: "d", cond: { $gt: [ "$$d", new Date(new Date().setDate(new Date().getDate()-7)) ] } } }
            },
            last_month: {
                $size: { $filter: { input: "$dates", as: "d", cond: { $gt: [ "$$d", new Date(new Date().setDate(new Date().getDate()-30)) ] } } }
            },
        }
    }
])

赞(0）回复(0）举报 2023-05-17

mnemlml82#

你也可以试试这个

db.getCollection('collectionName').aggregate([
{$group:{
    _id: "$intent",
    "status_pending": {$sum: {$cond: [{ $eq: ["$status", "pending"]}, 1,0 ]}},
    "status_trained": {$sum: {$cond: [{ $eq: ["$status", "trained"]}, 1,0 ]}},
    "last_day": { $sum: { $cond: [{$gt: [ "$createdAt", new Date(new Date().setDate(new Date().getDate()-1)) ]},1,0] } },
    "last_seven_day": { $sum: { $cond: [{$gt: [ "$createdAt", new Date(new Date().setDate(new Date().getDate()-7)) ]},1,0] } },
    "last_month": { $sum: { $cond: [{$gt: [ "$createdAt", new Date(new Date().setDate(new Date().getDate()-30)) ]},1,0] } }
  }
 }
])

赞(0）回复(0）举报 2023-05-17

vyu0f0g13#

为了在单个管道中执行$groups，一个可能的解决方案是使用$facet。它更简单和更快。

{ 
    $facet: {
        intents: [{ $group: { _id: { insurer: '$intent' }, count: { $sum: 1 }} }, { $sort : { 'count' : -1 } }],
        statuses: [{ $group: { _id: { status: '$status' }, count: { $sum: 1 }} }, { $sort : { 'count' : -1 } }]
    }
}

赞(0）回复(0）举报 2023-05-17

我来回答

mongodb在单个查询中使用多个$group

3条答案

相关问题

热门标签

最新问答