使用嵌套查询的Mongo $ lookup过滤器 [英] Mongo $lookup filter using nested query

问题描述

我正在使用mongoDB的$ lookup函数，特别是管道语法，以使我能够执行比我正在使用的(Sails/Waterline)ORM允许的查询更复杂的查询.

I'm playing with mongoDB's $lookup function, specifically the pipeline syntax, to allow me to perform some more complex queries than the ORM I am using (Sails/Waterline) allows.

我的数据的简化版本如下....

A cut down version of my data looks like....

    // 'job' collection
    {
      "id"      : j1,
      "mediaID" : "ABC1234"
    },
    {
      "id"      : j2,
      "mediaID" : "DEF1234"
    },
    {
      "id"      : j3,
      "mediaID" : "FGH3456"
    }

.. and ..

..and..

    // 'task' collection

    // j1 tasks
    {
      "id"      : "t1",
      "job"     : "j1",
      "taskName": "MOVE",
      "status"  : "COMPLETE"
    },
    {
      "id"      : "t2",
      "job"     : "j1",
      "taskName": "PUBLISH",
      "status"  : "COMPLETE"
    },
    // j2 tasks
    {
      "id"      : "t3",
      "job"     : "j2",
      "taskName": "MOVE",
      "status"  : "FAILED"
    },
    // j3 tasks
    {
      "id"      : "t4",
      "job"     : "j3",
      "taskName": "MOVE",
      "status"  : "COMPLETE"
    }

..其中任务集合通过job.id -> task.job

..where the task collection links to the job collection via job.id -> task.job

我想实现的是，能够通过job.mediaID和/或task.status过滤作业.我目前拥有的查询几乎可以满足我的要求，但是它并没有过滤出jobs，只是没有填充tasks部分.

What I would like to achieve is, to be able to filter jobs by job.mediaID and/or also task.status. The query I currently have almost gets what I want, but it doesn't filter out the jobs, it just doesn't populate the tasks part.

我当前的查询如下...

My current query is as follows...

    let results = await jobCollection.aggregate([

      // First filter jobs with matching criteria
      {
        $match: {
          $text: {$search: "1234"}
        }
      },

      // Next, link the task collection, and filter by status
      {
        "$lookup": {
          from    : 'task',
          'let'   : {job_id: '$_id'},
          pipeline: [
            {
              $match: {
                $expr: {
                  $and: [
                    // This does the linking of job.id -> task.job_id
                    {$eq: ['$job', '$$job_id']},
                    // This filters out unwanted tasks
                    {$eq: ['$status', 'FAILED']}
                  ]
                }
              }
            }
          ],
          as      : 'tasks'
        }
      }
    ])
    .toArray();

在该示例中，第一阶段将匹配j1和j2，因为它们都包含"1234"，然后我想根据任务状态进一步过滤出作业，例如，仅的任务包含status==FAILED，所以我的最终结果将是j2文档.

In that example, the first stage would match j1 and j2 as they both contain "1234", then I would like to further filter out the jobs, based on the task status, for example, only j2 has a task that has status==FAILED, so my end result would just be the j2 document.

我希望能做到.我想我可能只需要在末尾添加一些巧妙的投影即可.谢谢

I hope this makes. I think I may just need to add some clever projection on the end. Thanks

推荐答案

$lookup管道内的$match与jobCollection文档没有关系.它仅过滤tasks集合的文档.因此，您必须在$lookup之后再使用一个$match阶段来筛选出ROOT(jobCollection)文档.

The $match inside $lookup pipeline has no relation with the jobCollection documents. It only filters the documents for tasks collection. So, You have to use one more $match stage after the $lookup to filter out the ROOT(jobCollection) documents.

jobCollection.aggregate([
  { "$match": { "$text": { "$search": "1234" }}},
  { "$lookup": {
    "from": "task",
    "let": { "job_id": "$_id" },
    "pipeline": [
      { "$match": {
        "$expr": {
          "$and": [
            { "$eq": ["$job", "$$job_id"] },
            { "$eq": ["$status", "FAILED"] }
          ]
        }
      }}
    ],
    "as": "tasks"
  }},
  { "$match": { "tasks": { "$ne": [] }}},
])

这篇关于使用嵌套查询的Mongo $ lookup过滤器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！