mongodb 带条件的Mongodb聚合查找

Mongodb aggregation lookup with conditions

提问by vietnguyen09

I have a collection called article_categorywhich store all article_idbelongs to the category with category_idwith data format like so.

我有一个名为article_categorywhich store的集合都article_id属于category_id具有这样数据格式的类别。

Collection 1: article_category

集合 1：article_category

{
  "article_id": 2015110920343902,
  "all_category_id": [5,8,10]
}

Then I have other collection called articlewhich store all my post

然后我有其他集合称为article存储我所有的帖子

Collection 2: article

集合2：文章

{
  "title": "This is example rows in article collection"
  "article_id": 2015110920343902,
},
{
  "title": "Something change"
  "article_id": 2015110920343903,
},
{
  "title": "This is another rows",
  "article_id": 2015110920343904,
}

Now I want to perform MongoDB query to find titlewith regexwhile category_idmust equal to 8. Here is my query but is not work.

现在，我想执行的MongoDB查询发现title与regex同时category_id必须等于8。这是我的查询，但不起作用。

db.article.aggregate(
{
  $match: 
  {
    title: 
    {
       $regex: /example/
    }
  }
},
{
    $lookup:
       {
         from: "article_category",
         pipeline: [
            { $match: { category_id: 8 } }
         ],
         as: "article_category"
       }
  }
)

Above query only show the records which match by regexbut not match by category_id.

上面的查询只显示匹配 byregex但不匹配 by的记录category_id。

Any idea?

任何的想法？

回答by Alex Blex

First of all, it is all_category_id, not category_id. Secondly, you don't link articles - all documents will have exactly the same article_categoryarray. Lastly, you probably want to filter out articles that don't have matched category. The conditional pipeline should look more like this:

首先，它是all_category_id，不是category_id。其次，您不链接文章 - 所有文档都将具有完全相同的article_category数组。最后，您可能想过滤掉没有匹配类别的文章。条件管道应该更像这样：

db.article.aggregate([
  { $match: {
      title: { $regex: /example/ }
  } },
  { $lookup: {
    from: "article_category",
    let: {
      article_id: "$article_id"
    },
    pipeline: [
      { $match: {
          $expr: { $and: [
              { $in: [ 8, "$all_category_id" ] },
              { $eq: [ "$article_id", "$$article_id" ] }
          ] }
      } }
    ],
    as: "article_category"
  } },
  { $match: {
    $expr: { $gt: [
      { $size: "$article_category"},
      0
    ] }
  } }
] )

UPDATE:

更新：

If you don't match article_id, the $lookupwill result with identical article_categoryarray to all articles.

如果不匹配article_id，$lookup将导致article_category所有文章的数组相同。

Let's say your article_categorycollection has another document:

假设您的article_category集合有另一个文档：

{
  "article_id": 0,
  "all_category_id": [5,8,10]
}

With { $eq: [ "$article_id", "$$article_id" ] }in the pipeline the resulting article_categoryis

凭借{ $eq: [ "$article_id", "$$article_id" ] }在管道由此而来article_category的

[ 
  { 
    "article_id" : 2015110920343902, 
    "all_category_id" : [ 5, 8, 10 ] 
  } 
]

without:

没有：

[ 
  { 
    "article_id" : 2015110920343902, 
    "all_category_id" : [ 5, 8, 10 ] 
  },
  {
    "article_id": 0,
    "all_category_id": [ 5, 8, 10 ]
  }
]

If the later is what you need, it would be way simpler to make to find requests:

如果后者是您所需要的，那么查找请求会更简单：

db.article.find({ title: { $regex: /example/ } })

and

和

db.article_category.find({ all_category_id: 8 })

回答by Sagar Veeram

You've couple of things incorrect here. category_idshould be all_category_id. Use the join condition in $lookupand move the $matchoutside of $lookupstage with $unwindfor optimizedlookup.

你在这里有几件事不正确。category_id应该是all_category_id。使用连接条件 in$lookup并移动舞台的$match外部$lookup以$unwind进行optimized查找。

Use $projectwith exclusion to drop the looked up field from final response. Something like {$project:{article_category:0}}

$project与排除一起使用可从最终响应中删除查找的字段。就像是{$project:{article_category:0}}

Try

尝试

db.article.aggregate([
  {"$match":{"title":{"$regex":/example/}}},
  {"$lookup":{
    "from":"article_category",
    "localField":"article_id",
    "foreignField":"article_id",
    "as":"article_category"
  }},
  {"$unwind":"$article_category"},
  {"$match":{"article_category.all_category_id":8}}
])

For uncorrelated subquery try

对于不相关的子查询尝试

db.article.aggregate([
  {"$match":{"title":{"$regex":/example/}}},
  {"$lookup":{
    "from":"article_category",
    "pipeline":[{"$match":{"all_category_id":8}}],
    "as":"categories"
  }},
  {"$match":{"categories":{"$ne":[]}}}
])