mongodb - 使用带有条件的 $graphLookup

Question

我试图找到给定 child 的祖先。在某个时候，那个 child 的祖先姓氏改变了。我想找到给定姓氏的这个 child 的最后一位 parent 。例如:

{
    "_id":1,
    "parent":null
    "first":"Bob",
    "last":"Sagget"
},
{
    "_id":2,
    "parent":1,
    "first":"Jane",
    "last":"Dor"
},
{
    "_id":3,
    "parent":2,
    "first":"Crane",
    "last":"Dor"
},
{
    "_id":4,
    "parent":3,
    "first":"Ho",
    "last":"Dor"
},
{
    "_id":5,
    "parent":4,
    "first":"Mor",
    "last":"Dor"
}

我要查询 _id 5 并得到姓氏 的祖先多尔 .在这个数据库中会有其他我不想看到的名字为 Dor 的人，所以我不能只查询“Dor”的姓氏。

这是我当前的聚合查询 - 这给了我所有的祖先到 _id 1. 我怎样才能停在 _id 2 个？:

db.PeopleDb.aggregate(
[
    {
        $graphLookup: {
                "from": "PeopleDb", 
                "startWith": "$parent", 
                "connectFromField": "parent", 
                "connectToField": "_id", 
                "as": "linearAncestors"
            }
        },
        {
        $match: {
        "_id":5
        }
    },
]);

Answer 1

相反，您正在寻找的基本查询实际上是“开始”的，它限制了仅将单数文档与 _id: 5 匹配。然后执行 $graphLookup 为了寻找祖先。

至于“条件”，涉及到一些步骤，所以最好走遍整个过程并了解发生了什么:

db.PeopleDb.aggregate([
  { "$match": { "_id": 5 } },
  { "$graphLookup": {
    "from": "PeopleDb",
    "startWith": "$parent",
    "connectFromField": "parent",
    "connectToField": "_id",
    "as": "people",
    "depthField": "depth"
  }}
])

然后返回所有连接 parent 的递归链。到 _id ，还要注意“可选” "depthField"设置以在返回的结果中包含匹配的实际“深度”:

{
    "_id" : 5,
    "parent" : 4,
    "first" : "Mor",
    "last" : "Dor",
    "people" : [
        {
            "_id" : 1,
            "parent" : null,
            "first" : "Bob",
            "last" : "Sagget",
            "depth" : NumberLong(3)
        },
        {
            "_id" : 2,
            "parent" : 1,
            "first" : "Jane",
            "last" : "Dor",
            "depth" : NumberLong(2)
        },
        {
            "_id" : 3,
            "parent" : 2,
            "first" : "Crane",
            "last" : "Dor",
            "depth" : NumberLong(1)
        },
        {
            "_id" : 4,
            "parent" : 3,
            "first" : "Ho",
            "last" : "Dor",
            "depth" : NumberLong(0)
        }
    ]
}

所以注意到 4返回，因为它是初始文档的父级，然后 3因为它是父级，然后是 2因为它是 parent 等等。

您可以使用“可选” "maxDepth" 限制匹配的“深度”。管道阶段的参数，或者在“排除根”元素的特定情况下，您可以使用 "restrictSearchWithMatch"使用 null 简单地排除结果的选项家长:

db.PeopleDb.aggregate([
  { "$match": { "_id": 5 } },
  { "$graphLookup": {
    "from": "PeopleDb",
    "startWith": "$parent",
    "connectFromField": "parent",
    "connectToField": "_id",
    "as": "people",
    "depthField": "depth",
    "restrictSearchWithMatch": { "parent": { "$ne": null } }
  }}
])

返回相同的结果，但不包括 "parent" 所在的“ROOT”文档字段为 null :

{
    "_id" : 5,
    "parent" : 4,
    "first" : "Mor",
    "last" : "Dor",
    "people" : [
        {
            "_id" : 2,
            "parent" : 1,
            "first" : "Jane",
            "last" : "Dor",
            "depth" : NumberLong(2)
        },
        {
            "_id" : 3,
            "parent" : 2,
            "first" : "Crane",
            "last" : "Dor",
            "depth" : NumberLong(1)
        },
        {
            "_id" : 4,
            "parent" : 3,
            "first" : "Ho",
            "last" : "Dor",
            "depth" : NumberLong(0)
        }
    ]
}

当然，同样的原则也适用于您的 "last"。只能匹配条件为真的文档的条件。这里我将展示这两种情况，但 $or 是可选的:

db.PeopleDb.aggregate([
  { "$match": { "_id": 5 } },
  { "$graphLookup": {
    "from": "PeopleDb",
    "startWith": "$parent",
    "connectFromField": "parent",
    "connectToField": "_id",
    "as": "people",
    "depthField": "depth",
    "restrictSearchWithMatch": {
      "$or": [
        { "parent": { "$ne": null }},
        { "last": "Dor" }
      ]
    }
  }}
])

备注 然而， "restrictSearchWithMatch"是“递归”条件，因此如果链中的任何“祖先”不满足 "last"条件然后链被打破并且没有检索到进一步的祖先。为了得到“所有的祖先”却只显示那些有匹配的 "last"那么你 $filter 生成的数组内容改为:

db.PeopleDb.aggregate([
  { "$match": { "_id": 5 } },
  { "$graphLookup": {
    "from": "PeopleDb",
    "startWith": "$parent",
    "connectFromField": "parent",
    "connectToField": "_id",
    "as": "people",
    "depthField": "depth",
  }},
  { "$addFields": {
    "people": {
      "$filter": {
        "input": "$people",
        "cond": { "$eq": [ "$$this.last", "Dor" ] }
      }
    }
  }}
])

或者实际上通过使用字段值表达式而不是硬编码值与“祖先”相关的初始匹配文档的值进行“动态”比较:

db.PeopleDb.aggregate([
  { "$match": { "_id": 5 } },
  { "$graphLookup": {
    "from": "PeopleDb",
    "startWith": "$parent",
    "connectFromField": "parent",
    "connectToField": "_id",
    "as": "people",
    "depthField": "depth",
  }},
  { "$addFields": {
    "people": {
      "$filter": {
        "input": "$people",
        "cond": { "$eq": [ "$$this.last", "$last" ] }
      }
    }
  }}
])

在这种情况下，它是相同的 2,3,4祖先，但如果说祖先 3 "last" 实际上有不同的值然后使用 $filter 实际返回 2,4而 "restrictSearchWithMatch"只会返回 4自从 3会“断链”。这是主要的区别。

N.B The one thing you cannot do of yet using $graphLookup is that "field comparison expression" is not allowed. If you wanted something along those lines then you would do further manipulation with $filter and possibly other operations such as $indexOfArray if your actual intent was indeed to "break the chain" at the point where that "field comparison" was not met within a recursive search.