问使用带有条件的$graphLookup
EN

Stack Overflow用户

提问于 2018-06-14 06:05:24

回答 1查看 7.6K关注 0票数 6

我正试图找到一个孩子的祖先。在某种程度上，那个孩子的祖先的姓氏发生了变化。我想找到这个孩子最后一个姓的家长。例如：

{
    "_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，我不想看到，所以我不能只是查询姓氏"Dor“。

下面是我当前的聚合查询--这给了我所有的祖先，直到1的_id。我如何在2的_id停止呢？：

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

mongodb

mongodb-query

aggregation-framework

回答 1

Stack Overflow用户

回答已采纳

发布于 2018-06-14 17:39:07

相反，您要查找的基本查询实际上“开始”时限制只将单数文档与_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，等等。

您可以使用“可选”"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"字段为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" ] }
      }
    }
  }}
])