MongoDB Mongoose聚合查询深度嵌套的数组，删除空结果并填充引用 [英] MongoDB Mongoose aggregate query deeply nested array remove empty results and populate references
问题描述
This question is a follow up to a previous question for which I have accepted an answer already. I have an aggregate query that returns the results of a deeply nested array of subdocuments based on a date range. The query returns the correct results within the specified date range, however it also returns an empty array for the results that do not match the query.
技术:MongoDB 3.6，Mongoose 5.5，NodeJS 12
Technologies: MongoDB 3.6, Mongoose 5.5, NodeJS 12
问题1: 有什么方法可以删除与查询不匹配的结果?
Question 1: Is there any way to remove the results that don't match the query?
问题2:
有什么方法可以在结果中填充" Person db引用?例如，要获取人物显示名称，我通常使用填充"，例如find().populate({ path: 'Person', select: 'DisplayName'})
Question 2:
Is there any way to 'populate' the Person db reference in the results? For example to get the Person Display Name I usually use 'populate' such as find().populate({ path: 'Person', select: 'DisplayName'})
记录架构
let RecordsSchema = new Schema({
RecordID: {
type: Number,
index: true
},
RecordType: {
type: String
},
Status: {
type: String
},
// ItemReport array of subdocuments
ItemReport: [ItemReportSchema],
}, {
collection: 'records',
selectPopulatedPaths: false
});
let ItemReportSchema = new Schema({
// ObjectId reference
ReportBy: {
type: Schema.Types.ObjectId,
ref: 'people'
},
ReportDate: {
type: Date,
required: true
},
WorkDoneBy: [{
Person: {
type: Schema.Types.ObjectId,
ref: 'people'
},
CompletedHours: {
type: Number,
required: true
},
DateCompleted: {
type: Date
}
}],
});
查询
可以运行，但也返回空结果，还需要填充Person db引用的Display Name属性
Works but also returns empty results and also need to populate the Display Name property of the Person db reference
db.records.aggregate([
{
"$project": {
"ItemReport": {
$map: {
input: "$ItemReport",
as: "ir",
in: {
WorkDoneBy: {
$filter: {
input: "$$ir.WorkDoneBy",
as: "value",
cond: {
"$and": [
{ "$ne": [ "$$value.DateCompleted", null ] },
{ "$gt": [ "$$value.DateCompleted", new Date("2017-01-01T12:00:00.000Z") ] },
{ "$lt": [ "$$value.DateCompleted", new Date("2018-12-31T12:00:00.000Z") ] }
]
}
}
}
}
}
}
}
}
])
实际结果
{
"_id": "5dcb6406e63830b7aa5427ca",
"ItemReport": [
{
"WorkDoneBy": [
{
"_id": "5dcb6406e63830b7aa53d8ea",
"PersonID": 111,
"ReportID": 8855,
"CompletedHours": 3,
"DateCompleted": "2017-01-20T05:00:00.000Z",
"Person": "5dcb6409e63830b7aa54fdba"
}
]
}
]
},
{
"_id": "5dcb6406e63830b7aa5427f1",
"ItemReport": [
{
"WorkDoneBy": [
{
"_id": "5dcb6406e63830b7aa53dcdc",
"PersonID": 4,
"ReportID": 9673,
"CompletedHours": 17,
"DateCompleted": "2017-05-18T04:00:00.000Z",
"Person": "5dcb6409e63830b7aa54fd69"
},
{
"_id": "5dcb6406e63830b7aa53dcdd",
"PersonID": 320,
"ReportID": 9673,
"CompletedHours": 3,
"DateCompleted": "2017-05-18T04:00:00.000Z",
"Person": "5dcb6409e63830b7aa54fe88"
}
]
}
]
},
{
"_id": "5dcb6406e63830b7aa5427f2",
"ItemReport": [
{
"WorkDoneBy": []
}
]
},
{
"_id": "5dcb6406e63830b7aa5427f3",
"ItemReport": [
{
"WorkDoneBy": []
}
]
},
{
"_id": "5dcb6406e63830b7aa5427f4",
"ItemReport": [
{
"WorkDoneBy": []
}
]
},
{
"_id": "5dcb6406e63830b7aa5427f5",
"ItemReport": [
{
"WorkDoneBy": []
}
]
},
所需结果
请注意，带有空"WorkDoneBy"数组的结果已删除(问题1)，并且填充了人"显示名称(问题2).
Note the results with an empty "WorkDoneBy" array are removed (question 1), and the "Person" display name is populated (question 2).
{
"_id": "5dcb6406e63830b7aa5427f1",
"ItemReport": [
{
"WorkDoneBy": [
{
"_id": "5dcb6406e63830b7aa53dcdc",
"CompletedHours": 17,
"DateCompleted": "2017-05-18T04:00:00.000Z",
"Person": {
_id: "5dcb6409e63830b7aa54fe88",
DisplayName: "Joe Jones"
}
},
{
"_id": "5dcb6406e63830b7aa53dcdd",
"CompletedHours": 3,
"DateCompleted": "2017-05-18T04:00:00.000Z",
"Person": {
_id: "5dcb6409e63830b7aa54fe88",
DisplayName: "Alice Smith"
}
}
]
}
]
},
推荐答案
第一个问题相对容易回答，并且有多种方法可以解决.我希望将 $ anyElementTrue 与 $ map ，因为这些运算符很容易解释. /p>
First question is relatively easy to answer and there are multiple ways to do that. I would prefer using $anyElementTrue along with $map as those operators are pretty self-explanatory.
{
"$match": {
$expr: { $anyElementTrue: { $map: { input: "$ItemReport", in: { $gt: [ { $size: "$$this.WorkDoneBy" }, 0 ] } } } }
}
}
第二部分比较复杂，但仍然可能.代替填充，您需要运行 $ lookup 来从其他集合中获取数据.问题是您的Person值是深层嵌套的，因此在使用 $ setUnion .获得数据后，您需要使用$map和 $ mergeObjects .
Second part is a bit more complicated but still possible. Instead of populate you need to run $lookup to bring the data from other collection. The problem is that your Person values are deeply nested so you need to prepare a list of id values before using $reduce and $setUnion. Once you get the data you need to merge your nested objects with people entities using $map and $mergeObjects.
{
$addFields: {
people: {
$reduce: {
input: "$ItemReport",
initialValue: [],
in: { $setUnion: [ "$$value", "$$this.WorkDoneBy.Person" ] }
}
}
}
},
{
$lookup: {
from: "people",
localField: "peopleIds",
foreignField: "_id",
as: "people"
}
},
{
$project: {
_id: 1,
ItemReport: {
$map: {
input: "$ItemReport",
as: "ir",
in: {
WorkDoneBy: {
$map: {
input: "$$ir.WorkDoneBy",
as: "wdb",
in: {
$mergeObjects: [
"$$wdb",
{
Person: { $arrayElemAt: [{ $filter: { input: "$people", cond: { $eq: [ "$$this._id", "$$wdb.Person" ] } } } , 0] }
}
]
}
}
}
}
}
}
}
}
这篇关于MongoDB Mongoose聚合查询深度嵌套的数组，删除空结果并填充引用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
