你好我有以下收藏
const TransactionSchema = mongoose.Schema({
schedule: {
type: mongoose.Schema.ObjectId,
required: true,
ref: "Schedule"
},
uniqueCode: {
type: String,
required: true
},
created: {
type: Date,
default: Date.now
},
status: {
type: String,
required: false
},
})
const ScheduleSchema = mongoose.Schema({
start: {
type: Date,
required: true,
},
end: {
type: Date,
required: false,
},
location: {
type: mongoose.Schema.ObjectId,
required: true,
ref: "Location"
},
})
我想回傳計劃在事務中出現的次數（狀態等于“活動”）并根據其位置 ID 對其進行分組，然后查找位置集合以顯示名稱。例如我有以下資料。
交易
[
{
"_id":"identifier",
"schedule":identifier1,
"uniqueCode":"312312312312",
"created":"Date",
"status": 'Active'
},
{
"_id":"identifier",
"schedule":identifier1,
"uniqueCode":"1213123123",
"created":"Date",
"status": "Deleted"
}
]
日程
[
{
"_id":identifier1,
"start":"date",
"end":"date",
"location": id1
},
{
"_id":identifier2,
"start":"date",
"end":"date",
"location": id2
}
]
我想得到以下結果并將結果限制為 10 并根據其總值對其進行排序：
[
{
"locationName":id1 name,
"total":1
},
{
"locationName":id2 name,
"total":0
}
]
謝謝你。對不起，我的英語不好。
uj5u.com熱心網友回復：
有點復雜和長查詢。
$lookup-集合通過匹配schedule加入集合：transaction
_id(schedule) 與schedule(transaction)status是Active
并回傳一個transactions陣列。
$lookup-schedule集合與集合連接location以回傳location陣列。$set- 獲取location陣列中的第一個檔案，因此該欄位將是檔案欄位而不是陣列。[這需要幫助進一步的階段]$group- 分組location._id。并且需要 和 等location欄位total。$sort- 按totalDESC 排序。$limit- 最多退回 10 個檔案。$project- 裝飾輸出檔案。
db.schedule.aggregate([
{
$lookup: {
from: "transaction",
let: {
scheduleId: "$_id"
},
pipeline: [
{
$match: {
$expr: {
$and: [
{
$eq: [
"$schedule",
"$$scheduleId"
]
},
{
$eq: [
"$status",
"Active"
]
}
]
}
}
}
],
as: "transactions"
}
},
{
$lookup: {
from: "location",
localField: "location",
foreignField: "_id",
as: "location"
}
},
{
$set: {
location: {
$first: "$location"
}
}
},
{
$group: {
_id: "$location._id",
location: {
$first: "$location"
},
total: {
$sum: {
$size: "$transactions"
}
}
}
},
{
$sort: {
"total": -1
}
},
{
$limit: 10
},
{
$project: {
_id: 0,
locationName: "$location.name",
total: 1
}
}
])
示例 Mongo Playground
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/465218.html