使用嵌套查询的Mongo $ lookup过滤器

本文介绍了使用嵌套查询的Mongo $ lookup过滤器的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在使用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": [] }}}, ])