mongodb - $在 mongodb 中查找多个级别

Question

我是 mongodb 的新手，我使用 mongoose populate 编写了查询。

this.model.findById({ _id: req.params.id })
.populate('potentialLevels', 'description result plIndex')
.populate({
  path: 'categories',
  select: 'name',
  populate: {
    path: 'tasks',
    model: 'Task',
    select: '-createdAt -updateAt -start -end -userEstEnd',
    populate: [{
      path: 'potentialLevelId',
      model: 'PotentialLevel',
      select: 'description result plIndex'
    }, {
      path: 'tasks',
      model: 'Task',
      select: 'name',
      options: { sort: { 'priority': 1 } }
    }]
  }
})

上面的查询有效并返回以下数据:

{"categories":[{"tasks":[],"_id":"5b56b9ca03dbaf1ee804a334","name":"Relationship"},{"tasks":[{"estMin":75,"totEstMin":72,"totFocusBlock":2.4,"perDayTotEstMin":12,"perDayTotFocusBlock":0.4,"dueDays":6,"priority":0.031746031746031744,"status":"CREATED","tasks":[{"_id":"5b9f71a32b264f001592d59b","name":"Market it"}],"_id":"5b9f712a2b264f001592d595","cx":18.82748286757155,"cy":-48.04254248966534,"name":"Rent 14M","categoryId":"5b56b9ca03dbaf1ee804a335","potentialLevelId":{"_id":"5b56b7bab926429370bb5035","description":"Big, Long terms Positive Reward","plIndex":2,"result":9},"updatedAt":"2018-09-17T17:11:20.657Z","__v":1,"childViewId":"5b9f719e2b264f001592d59a"},{"estMin":20,"totEstMin":240,"totFocusBlock":8,"perDayTotEstMin":5.714285714285714,"perDayTotFocusBlock":0.1904761904761905,"dueDays":42,"priority":0.01511715797430083,"status":"CREATED","tasks":[{"_id":"5b9fcf7d97e5ea00158c010b","name":"ijg[f"}],"_id":"5b9fcf3c97e5ea00158c0105","cx":43.25128613351225,"cy":-23.764442930501236,"name":"Build online biz £10k","categoryId":"5b56b9ca03dbaf1ee804a335","potentialLevelId":{"_id":"5b56b7bab926429370bb5035","description":"Big, Long terms Positive Reward","plIndex":2,"result":9},"updatedAt":"2018-09-17T17:11:20.411Z","__v":1,"childViewId":"5b9fcf7597e5ea00158c010a"}],"_id":"5b56b9ca03dbaf1ee804a335","name":"Work"},{"tasks":[{"estMin":60,"totEstMin":72,"totFocusBlock":2.4,"perDayTotEstMin":1.309090909090909,"perDayTotFocusBlock":0.04363636363636363,"dueDays":55,"priority":0.003463203463203463,"status":"CREATED","tasks":[],"_id":"5b9fceb797e5ea00158c0104","cx":40.06629027106884,"cy":28.811714352229274,"name":"Explore corp sub model","categoryId":"5b56b9ca03dbaf1ee804a336","potentialLevelId":{"_id":"5b56b7bab926429370bb5035","description":"Big, Long terms Positive Reward","plIndex":2,"result":9},"updatedAt":"2018-09-17T15:58:25.889Z","__v":0},{"estMin":0,"totEstMin":0,"totFocusBlock":0,"perDayTotEstMin":0,"perDayTotFocusBlock":0,"dueDays":0,"priority":0,"status":"CREATED","tasks":[],"_id":"5ba0f173c2d9810015ad3e0c","cx":282.75602123836603,"cy":133.17858293828135,"name":"kjg;hk","categoryId":"5b56b9ca03dbaf1ee804a336","potentialLevelId":{"_id":"5b56b7bab926429370bb503d","description":"Tiny, short-lasting Positive reward","plIndex":10,"result":1},"updatedAt":"2018-09-18T12:37:07.974Z","__v":0}],"_id":"5b56b9ca03dbaf1ee804a336","name":"Fun"},{"tasks":[],"_id":"5b56b9ca03dbaf1ee804a337","name":"Health"}],"events":[],"potentialLevels":[{"_id":"5b56b7bab926429370bb5034","description":"Massive Life-long Positive Reward","plIndex":1,"result":10},{"_id":"5b56b7bab926429370bb5035","description":"Big, Long terms Positive Reward","plIndex":2,"result":9},{"_id":"5b56b7bab926429370bb5036","description":"Medium long-term Positive Reward","plIndex":3,"result":8},{"_id":"5b56b7bab926429370bb5037","description":"Small long Positive reward","plIndex":4,"result":7},{"_id":"5b56b7bab926429370bb5038","description":"Big Medium term Positive reward","plIndex":5,"result":6},{"_id":"5b56b7bab926429370bb5039","description":"Medium sized, Medium term Positive reward","plIndex":6,"result":5},{"_id":"5b56b7bab926429370bb503a","description":"Small Medium-term Positive reward","plIndex":7,"result":4},{"_id":"5b56b7bab926429370bb503b","description":"Big short-lasting Positive reward","plIndex":8,"result":3},{"_id":"5b56b7bab926429370bb503c","description":"Small short-lasting Positive reward","plIndex":9,"result":2},{"_id":"5b56b7bab926429370bb503d","description":"Tiny, short-lasting Positive reward","plIndex":10,"result":1}],"_id":"5b56b9ca03dbaf1ee804a338","__v":0,"createdAt":"2018-07-24T05:31:54.598Z","name":"Capture GUI","updatedAt":"2018-07-24T05:31:54.598Z","userId":"5b4db45839adf5cbe5496b90","viewType":"CAPTURE"}

现在我想要使用聚合管道进行相同的查询。我试过下面的代码，但它不工作。

db.View.aggregate([
 { "$match": { "viewType": "CAPTURE", "userId": ObjectId("5b4db45839adf5cbe5496b90") } },
 {"$lookup": {"from": "PotentialLevel", "localField": "potentialLevels", "foreignField": "_id", "as": "potentialLevels"}},
 { "$lookup": {"from": "Category", "localField": "categories", "foreignField": "_id", "as": "categories"}},
 { "$unwind": {"path":"$categories", "preserveNullAndEmptyArrays": true} },
 { "$lookup": {"from": "Task", "localField": "categories.tasks", "foreignField": "_id", "as": "categories.tasks"}},
  {"$group": { "_id": "$_id", "name": {"$first": "$name"},  "potentialLevels": {"$first": "$potentialLevels"}, "categories": {"$push": "$categories"}}}
])

上面的查询完成了一半。我无法为深度填充聚合编写查询。请帮我。谢谢

Answer 1

您可以在 mongodb 3.6 及更高版本中尝试以下聚合

this.model.aggregate([
  { "$match": { "_id": mongoose.Types.ObjectId(req.params.id) }},
  { "$lookup": {
    "from": PotentialLevels.collection.name,
    "let": { "potentialLevels": "$potentialLevels" },
    "pipeline": [
      { "$match": { "$expr": { "$in": [ "$_id", "$$potentialLevels" ] } } },
      { "$project": { "description": 1, "result": 1, "plIndex": 1 }}
    ],
    "as": "potentialLevels"
  }},
  { "$lookup": {
    "from": Categories.collection.name,
    "let": { "categories": "$categories" },
    "pipeline": [
      { "$match": { "$expr": { "$in": [ "$_id", "$$categories" ] } } },
      { "$lookup": {
        "from": Tasks.collection.name,
        "let": { "tasks": "$tasks" },
        "pipeline": [
          { "$match": { "$expr": { "$in": [ "$_id", "$$tasks" ] } } },
          { "$lookup": {
            "from": PotentialLevels.collection.name,
            "let": { "potentialLevelId": "$potentialLevelId" },
            "pipeline": [
              { "$match": { "$expr": { "$eq": [ "$_id", "$$potentialLevelId" ] } } },
              { "$project": { "description": 1, "result": 1, "plIndex": 1 }}
            ],
            "as": "potentialLevelId"
          }},
          { "$lookup": {
            "from": Tasks.collection.name,
            "let": { "tasks": "$tasks" },
            "pipeline": [
              { "$match": { "$expr": { "$in": [ "$_id", "$$tasks" ] } } },
              { "$sort": { "priority": 1 } },
              { "$project": { "name": 1 }}
            ],
            "as": "tasks"
          }},
          { "$unwind": "$potentialLevelId" },
          { "$project": { "createdAt": 1, "updateAt": 1, "start": 1, "end": 1, "userEstEnd": 1, "tasks": 1, "potentialLevelId": 1 }}
        ],
        "as": "tasks"
      }},
      { "$project": { "name": 1, "tasks": 1 }}
    ],
    "as": "categories"
  }}
])