首页 / 技术问答 / Mongodb问答列表 / 问答详情

MongoDB 查询以包含多个字段的最常见值的计数

提前感谢您的帮助！

我有一个集合QR，其模式与此类似:

var qrSchema = new Schema({
    qrId: { type: String, index: true },
    owner: { type: Schema.Types.ObjectId, ref: 'User' },
    qrName: { type: String },
    qrCategory: { type: String, index: true },
    shortUrl: { type: String}}
})

与此类似的系列Datas:

var dataSchema = new Schema({
    qrId: { type: String, index: true}
    city: { type: String},
    device: { type: String},
    date: { type: Date, index:true},
})

QR和数据之间的关系是1对多.

我有这样一个集合:

Model.QR.aggregate([
{ $match: {
    $and: [
        { owner: mongoose.Types.ObjectId(user._id) },
        {
        $expr: {
            $cond: [
            { $in: [ category, [ null, "", "undefined" ]] },
            true,
            { $eq: [ "$qrCategory", category ] }
            ]
        }
        }
    ]
    }
},
{ $lookup:
    {
    "from": "datas",
    "localField": "qrId",
    "foreignField": "qrId",
    "as": "data"
    }
},
{
    $project: {
    _id: 0,
    qrId: 1,
    qrName: 1,
    qrCategory: 1,
    shortUrl: 1,
    data: {
        $filter: {
            input: "$data",
            as: "item",
            cond: {
              $and: [
                { $gte: [ "$$item.date", date.start ] },
                { $lte: [ "$$item.date", date.end ] }
            ] }
        }
      }
    }    
},
{ 
    $group: {
    _id: { "qrId": "$qrId", "qrName": "$qrName", "qrCategory": "$qrCategory", "shortUrl": "$shortUrl" },
    data: {
        $push: {
        dataItems: "$data",
        count: { 
            $size:  { '$ifNull': ['$data', []] }
        }
        }
    }
    }
},
{
    $sort: {
    "data.count": -1
    }
},
{
    $limit: 10,
}]).exec((err, results) => { })

返回的结果是:

[
    {
        "_id": {
            "qrId": "0PRA",
            "qrName": "Campaign 0PRA",
            "qrCategory": "html",
            "shortUrl": "http://someurl.com/0PRA"
        },
        "data": [
            {
                "dataItems": [
                    {
                        "_id": "6200f2a8c0cf7a1c49233c7f",
                        "qrId": "0PRA",
                        "device": "iOS",
                        "city": "Beijing",
                    },
                    {
                        "_id": "6200f2eac0cf7a1c49233c80",
                        "qrId": "0PRA",
                        "device": "AndroidOS",
                        "city": "Beijing",
                    },
                    {
                        "_id": "6200f3a4c0cf7a1c49233c81",
                        "qrId": "0PRA",
                        "device": "AndroidOS",
                        "city": "Beijing",
                    },
                    {
                        "_id": "6200f632c0cf7a1c49233c88",
                        "qrId": "0PRA",
                        "device": "AndroidOS",
                        "city": "Nanchang",
                    },
                    {
                        "_id": "6201b342c0cf7a1c49233caa",
                        "qrId": "0PRA",
                        "device": "iOS",
                        "city": "Taizhou",
                    }
                ],
                "count": 5
            }
        ]
    },
    {
        "_id": {
            "qrId": "NQ17",
            "qrName": "Campaign NQ17",
            "qrCategory": "menu",
            "shortUrl": "http://someurl.com/NQ17"
        },
        "data": [
            {
                "dataItems": [
                    {
                        "_id": "6200f207c0cf7a1c49233c7a",
                        "qrId": "NQ17",
                        "device": "iOS",
                        "city": "Singapore"
                    },
                    {
                        "_id": "8200f207c1cf7a1c49233c7a",
                        "qrId": "NQ17",
                        "device": "iOS",
                        "city": "Singapore"
                    },
                    {
                        "_id": "6200ac5db44f23b9ec2b6040",
                        "qrId": "NQ17",
                        "device": "AndroidOS",
                        "city": "San Antonio"
                    }
                ],
                "count": 3
            }
        ]
    }
]

我试图在计数dataItems后将最常见的设备和城市包括在结果中，如下所示:

[
    {
        "_id": {
            "qrId": "0PRA",
            "qrName": "Campaign 0PRA",
            "qrCategory": "html",
            "shortUrl": "http://someurl.com/0PRA"
        },
        "data": [
            {
                "dataItems": [
                    {
                        "_id": "6200f2a8c0cf7a1c49233c7f",
                        "qrId": "0PRA",
                        "device": "iOS",
                        "city": "Beijing",
                    },
                    {
                        "_id": "6200f2eac0cf7a1c49233c80",
                        "qrId": "0PRA",
                        "device": "AndroidOS",
                        "city": "Beijing",
                    },
                    {
                        "_id": "6200f3a4c0cf7a1c49233c81",
                        "qrId": "0PRA",
                        "device": "AndroidOS",
                        "city": "Beijing",
                    },
                    {
                        "_id": "6200f632c0cf7a1c49233c88",
                        "qrId": "0PRA",
                        "device": "AndroidOS",
                        "city": "Nanchang",
                    },
                    {
                        "_id": "6201b342c0cf7a1c49233caa",
                        "qrId": "0PRA",
                        "device": "iOS",
                        "city": "Taizhou",
                    }
                ],
                "count": 5,
                "topDevice": "AndroidOS",  // <---- trying to add this
                "topLocation": "Beijing"   // <---- trying to add this
            }
        ]
    },
    {
        "_id": {
            "qrId": "NQ17",
            "qrName": "Campaign NQ17",
            "qrCategory": "menu",
            "shortUrl": "http://someurl.com/NQ17"
        },
        "data": [
            {
                "dataItems": [
                    {
                        "_id": "6200f207c0cf7a1c49233c7a",
                        "qrId": "NQ17",
                        "device": "iOS",
                        "city": "Singapore"
                    },
                    {
                        "_id": "8200f207c1cf7a1c49233c7a",
                        "qrId": "NQ17",
                        "device": "iOS",
                        "city": "Singapore"
                    },
                    {
                        "_id": "6200ac5db44f23b9ec2b6040",
                        "qrId": "NQ17",
                        "device": "android",
                        "city": "San Antonio"
                    }
                ],
                "count": 3,
                "topDevice": "iOS",          // <---- trying to add this
                "topLocation": "Singapore"   // <---- trying to add this
            }
        ]
    }
]

这可能吗？

非常感谢您的帮助或提示！

推荐答案

Method 1

使用$function会更容易.MongoDB version >= 4.4

Sort function in js

db.collection.aggregate([
  {
    "$set": {
      "data": {
        "$map": {
          "input": "$data",
          "as": "d",
          "in": {
            "count": "$$d.count",
            "dataItems": "$$d.dataItems",
            "topDevice": {
              $function: {
                body: "function(arr) {return arr.sort((a,b) =>arr.filter(v => v===a).length-arr.filter(v => v===b).length).pop() }",
                args: [ "$$d.dataItems.device" ],
                lang: "js"
              }
            },
            "topLocation": {
              $function: {
                body: "function(arr) {return arr.sort((a,b) =>arr.filter(v => v===a).length-arr.filter(v => v===b).length).pop() }",
                args: [ "$$d.dataItems.city" ],
                lang: "js"
              }
            }
          }
        }
      }
    }
  }
])

mongoplayground

Method 2

db.qr.aggregate([
  {
    "$match": {
      owner: {
        "$in": [
          "1",
          "2"
        ]
      }
    }
  },
  {
    "$lookup": {
      "from": "data",
      "localField": "qrId",
      "foreignField": "qrId",
      "as": "data",
      "pipeline": [
        {
          "$match": {
            "$and": [
              {
                "date": {
                  "$gte": ISODate("2021-09-01T01:23:25.184Z")
                }
              },
              {
                "date": {
                  "$lte": ISODate("2021-09-02T11:23:25.184Z")
                }
              }
            ]
          }
        },
        {
          "$facet": {
            "deviceGroup": [
              {
                "$group": {
                  "_id": "$device",
                  "sum": {
                    "$sum": 1
                  }
                }
              },
              {
                "$sort": {
                  sum: -1
                }
              },
              {
                "$limit": 1
              }
            ],
            "cityGroup": [
              {
                "$group": {
                  "_id": "$city",
                  "sum": {
                    "$sum": 1
                  }
                }
              },
              {
                "$sort": {
                  sum: -1
                }
              },
              {
                "$limit": 1
              }
            ],
            "all": []
          }
        }
      ]
    }
  },
  {
    "$set": {
      "data": {
        "$first": "$data.all"
      },
      "topDevice": {
        "$first": {
          "$first": "$data.deviceGroup._id"
        }
      },
      "topLocation": {
        "$first": {
          "$first": "$data.cityGroup._id"
        }
      }
    }
  },
  {
    $group: {
      _id: {
        "qrId": "$qrId",
        "qrName": "$qrName",
        "qrCategory": "$qrCategory",
        "shortUrl": "$shortUrl"
      },
      data: {
        $push: {
          dataItems: "$data",
          topDevice: "$topDevice",
          topLocation: "$topLocation",
          count: {
            $size: {
              "$ifNull": [
                "$data",
                []
              ]
            }
          }
        }
      }
    }
  }
])

mongoplayground

Mongodb相关问答推荐

Golang mongodb聚合错误：管道阶段规范对象必须包含一个字段

MongoDB获取所有文档谁的S子文档只包含一个特定值？

从 Amazon S3(Next.js、Mongodb、Mongoose)删除图像

MongoDB DB参考返回null值

Mongodb Timeseries / Golang - ['timestamp' 必须存在并包含有效的 BSON UTC 日期时间值]

Mongodb聚合查找异常值

MongoDB 更新：如果新值不同，则将旧字段值推送到另一个数组字段

MongoDB乘以对象值？

MongoDB C# 驱动程序 2.0 InsertManyAsync 与 BulkWriteAsync

mongo _id 字段重复键错误

oplog 在独立 mongod 上启用，不适用于副本集

mongoose如何在幕后工作

NodeJS中的密码重置

Mongoose：填充填充字段

mongodb中类型不相等的查询

Ruby 按键值分组哈希

show dbs 给出Not Authorized to execute command错误

MongoError：Can't extract geo keys from object

mongoosefind()不返回结果

在mongoose中创建和查找地理位置

实用课程推荐
相关教程推荐
休闲君评测 |  TOOLFK工具网 |  古诗文网 |  良许Linux教程网 |  Python之禅 |  红色石头 |  入党申请书 | 

© 2020 Hi LearnFk 意见反馈 免责声明 关于我们 桂ICP备11002319号-3 友链&广告位+QQ: 1963612630