預先感謝您的任何幫助！
我有一個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 和 Datas 之間的關系是一對多的。
我有一個這樣的聚合：
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
}
]
}
]
這可能嗎？
非常感謝您提前提供任何幫助或提示！
uj5u.com熱心網友回復：
方法一
使用$function會更容易。MongoDB 版本 >= 4.4
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
方法二
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
uj5u.com熱心網友回復：
詢問
- 添加你需要的比賽，我不明白比賽應該做什么
- 查找
qrId - 過濾器只保留 start<=dates<=end（替換 1 和 100）
- 分面到
all-documents組topDevicetopLocation $set將這些資料從它們所在的嵌套位置中取出- 計數被添加為大小
all-documents
*也許我遺漏了一些東西，但試試看（第一部分我認為它像玉婷的回答）
測驗代碼在這里
QR.aggregate(
[{"$lookup":
{"from":"Datas",
"localField":"qrId",
"foreignField":"qrId",
"pipeline":
[{"$match":{"$and":[{"date":{"$gte":1}}, {"date":{"$lte":100}}]}},
{"$facet":
{"dataItems":[],
"topDevice":
[{"$group":{"_id":"$device", "count":{"$sum":1}}},
{"$sort":{"count":-1}}, {"$limit":1}],
"topLocation":
[{"$group":{"_id":"$city", "count":{"$sum":1}}},
{"$sort":{"count":-1}}, {"$limit":1}]}}],
"as":"data"}},
{"$set":{"data":{"$arrayElemAt":["$data", 0]}}},
{"$set":
{"dataItems":"$data.dataItems",
"count":{"$size":"$data.dataItems"},
"topDevice":
{"$getField":
{"field":"_id", "input":{"$arrayElemAt":["$data.topDevice", 0]}}},
"topLocation":
{"$getField":
{"field":"_id",
"input":{"$arrayElemAt":["$data.topLocation", 0]}}},
"data":"$$REMOVE"}}])
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/432402.html