带条件的Mongodb聚合查找 [英] Mongodb aggregation lookup with conditions

问题描述

我有一个名为 article_category 的集合，它存储所有 article_id 属于具有 category_id 的类别，数据格式如下.

集合 1:article_category

<代码>{文章编号":2015110920343902，all_category_id":[5,8,10]}

然后我还有一个名为 article 的集合，用于存储我所有的帖子

集合 2:文章

<代码>{"title": "这是文章集合中的示例行"文章编号":2015110920343902，},{"title": "有些变化"article_id":2015110920343903，},{"title": "这是另一行",文章编号":2015110920343904，}

现在我想执行 MongoDB 查询以使用 regex 查找 title 而 category_id 必须等于 8.这是我的查询，但不起作用.

db.article.aggregate({$匹配:{标题:{$正则表达式:/示例/}}},{$查找:{来自:文章类别"，管道:[{ $match: { category_id: 8 } }],如:文章类别"}})

以上查询只显示符合regex但不符合category_id的记录.

有什么想法吗?

解决方案

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

db.article.aggregate([{ $匹配:{标题:{ $regex:/example/}} },{ $查找:{来自:文章类别"，让: {article_id: "$article_id"},管道:[{ $匹配:{$expr: { $and: [{ $in: [ 8, "$all_category_id" ] },{ $eq: [ "$article_id", "$$article_id" ] }] }} }],如:文章类别"} },{ $匹配:{$expr: { $gt: [{ $size: "$article_category"},0] }} }])

更新:

如果您不匹配 article_id，$lookup 将产生与所有文章相同的 article_category 数组.

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

<代码>{"article_id": 0,all_category_id":[5,8,10]}

使用 { $eq: [ "$article_id", "$$article_id" ] } 在管道中，结果 article_category 是

<预><代码>[{文章编号":2015110920343902，"all_category_id" : [ 5, 8, 10 ]}]

没有:

<预><代码>[{文章编号":2015110920343902，"all_category_id" : [ 5, 8, 10 ]},{"article_id": 0,"all_category_id": [ 5, 8, 10 ]}]

如果您需要后者，那么查找请求会更简单:

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

和

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

I have a collection called article_category which store all article_id belongs to the category with category_id with data format like so.

Collection 1: article_category

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

Then I have other collection called article which store all my post

Collection 2: article

{
  "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 title with regex while category_id must equal to 8. Here is my query but is not work.

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 regex but not match by category_id.

Any idea?

解决方案

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_category array. Lastly, you probably want to filter out articles that don't have matched category. The conditional pipeline should look more like this:

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 $lookup will result with identical article_category array to all articles.

Let's say your article_category collection has another document:

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

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

[ 
  { 
    "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 })

这篇关于带条件的Mongodb聚合查找的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！