MongoDB Mongoose聚合查询深度嵌套的数组，删除空结果并填充引用

本文介绍了MongoDB Mongoose聚合查询深度嵌套的数组，删除空结果并填充引用的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

此问题是对

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 ] } } } } } }

MongoPlayground

第二部分比较复杂，但仍然可能.代替填充，您需要运行 $ lookup 来从其他集合中获取数据.问题是您的Person值是深层嵌套的，因此在使用id值列表. /index.html"rel =" nofollow noreferrer> $ reduce 和 $ 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] } } ] } } } } } } } }

完整解决方案