Mongodb查询联接优化 [英] Mongodb query join optimization

问题描述

假设我有3个收藏集:

Grandparents
{
 _id:1,
 name:old foo
}

Parents
{
_id:2,
grandparentId:1,
name: foo
}
Person
{
_id: 3,
parentId:2,
name: youngfoo
}

我如何优化查询以查找人的祖父母姓名= x的所有人?

How do i optimize a query to find all person where person's grandparent's name = x?

在这种情况下，名称不是唯一的.

Name is not unique in this case.

到目前为止我能想到的是:
1.查询名称= x
的所有祖父母 2.查询所有从步骤1获得的祖父母ID ==祖父母ID的所有父母.
3.查询所有从步骤2中获得父母ID ==父母ID的人.

What i can think of so far:
1. Query all grandparents with name = x
2. Query all parents where grandparent ids == grandparent ids gotten from step 1
3. Query all persons where parent ids == parent ids gotten from step 2.

感觉不是很有效. mongodb专家可以帮助我吗?谢谢！

Doesn't feel very efficient. Can mongodb experts out there help me? Thanks!

推荐答案

我认为您想做这样的事情.我没有测试此查询，但是这是我会尝试在您的位置进行的操作.这仅在3.6 mongodb上可行，因为它支持多个联接.想法是加入所有3个收藏集.首先加入的是Parents和Person(通过Parents ID)和Persons"parentsId".第二次加入是父母和祖父母.然后，按祖父母姓名过滤，您将获得一个包含该祖父母，他的儿子(父母)和他的孙子(人)的文档.然后，您只需投影该人即可.

I think you want to do something like this. I did not test this query, but this is what i would try in your place. This is only possible on 3.6 mongodb, because it supports multiple joins. The idea is to join all 3 collections. First join is Parents and Person by Parents id, and Persons "parentsId". Second join is Parents and Grandparents. Then you filter out by grandparent name and you will get a document that contains that grandparent, his son (parent), and his grandson(person). Then you just project the person.

    db.Parents.aggregate([
       {
          $lookup:{
             from:"Person",
             localField:"_id",
             foreignField:"parentId",
             as:"Person"
          }
       },
       {
          $unwind:"$Person"
       },
       {
          $lookup:{
             from:"Grandparents",
             localField:"grandparentId",
             foreignField:"_id",
             as:"Grandparents"
          }
       },
       {
          $unwind:"$Grandparents"
       },
       {$match:{Grandparents.name:"x"}},
       {$project:{Person.name:1,Person._id:1}}
}])

我认为这可以解决问题

这篇关于Mongodb查询联接优化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！