Mongodb 递归查询与 $graphLookup 不按预期工作 [英] Mongodb recursive query not working as expected with $graphLookup

查看：39
本文介绍了Mongodb 递归查询与 $graphLookup 不按预期工作的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

在我的文档中，我有 _id、公司名称和赞助商(通过 _id 标识父文档).

例如，我有第一条没有赞助商(父母)的记录

_id:607536219910ef23e80e0bbe公司名称:主要公司"赞助商:"

然后是公司 1，主要公司是母公司:

 _id:607e16760a9d2c16e06bc252公司名称:公司 1"赞助商:607536219910ef23e80e0bbe"

还有公司 2，其中公司 1 是母公司:

_id:607e187b0a9d2c16e06bc253公司名称:公司 2"赞助商:607e16760a9d2c16e06bc252"

还有公司 3，其中公司 2 是母公司:

_id:607e1f470a9d2c16e06bc254公司名称:公司 3"赞助商:607e187b0a9d2c16e06bc253"

我正在做一个 $match 来为主要公司带来孩子们的记录

<代码>{赞助商:'607536219910ef23e80e0bbe'}

然后我 $addFields userid，这是一个 _Id 转换为字符串.这是稍后与赞助商匹配:

{"userid": { "$toString": "$_id";}}

现在，当我使用 graphLookup 时，我得到 Main Company 的子公司(公司 2)，但我没有将公司 3 作为公司 2 的子公司.我只得到公司 1 和公司 2:

这是我的graphLookup

<代码>{来自:'请'，startWith: "$userid",connectFromField: 'userid',connectToField: '赞助商',如:'下线'，最大深度:100，限制搜索匹配:{}}

任何帮助将不胜感激.

更新:

正如 Turivishal 在下面所说，查询有效，但这些是我期望的结果:

<代码>[{_id":607536219910ef23e80e0bbe"，公司名称":主要公司"，下线":[{_id":607e16760a9d2c16e06bc252"，公司名称":公司 1"，赞助商":607536219910ef23e80e0bbe"，下线":[{_id":607e187b0a9d2c16e06bc253"，公司名称":公司 2"，赞助商":607e16760a9d2c16e06bc252"，下线":[{_id":607e1f470a9d2c16e06bc254"，公司名称":公司 3"，赞助商":607e187b0a9d2c16e06bc253"}]}]}],赞助商":"，用户 ID":607536219910ef23e80e0bbe"}

TURIVISHAL 的解决方案:

根据 Turivishal 解决方案，这是最终的流水线，它提供了重复查询的完美下线/层次结构/树视图，并与 Angular Treeview 控件完美配合.非常感谢 Turivishal.我相信你应该发布一个答案，这样我才能接受它，它可以对其他人有用.

他的解决方案与他提出的解决方案非常相似，但要好得多.我最终创建了一个名为 PLID 的新字段，它复制了 _id 字段，并且效果非常好.我让管理员决定他们是否认为这个问题应该结束，因为 Turivishal 解决方案同样基于那个 Q，但在我看来更清楚.这是他的作品:

<预><代码>[{'$匹配':{'赞助商':'0'}}, {'$graphLookup':{'来自':'请'，'startWith': '$plid','connectFromField': 'plid','connectToField': '赞助商','depthField': '级别','作为':'孩子'}}, {'$放松':{'path': '$children','preserveNullAndEmptyArrays': 真}}, {'$排序':{'children.level':-1}}, {'$组':{'_id': '$plid',赞助商":{'$first': '$赞助商'},'公司名称': {'$first': '$公司名称'},'孩子们': {'$push': '$children'}}}, {'$addFields':{'孩子们': {'$减少':{'输入':'$儿童'，'初始值': {'1级，'presentChild': [],'上一个孩子':[]},'在': {'$let':{变量":{'上一个':{'$cond': [{'$eq': ['$$value.level', '$$this.level']}, '$$value.prevChild', '$$value.presentChild']},'当前的': {'$cond': [{'$eq': ['$$value.level', '$$this.level']}, '$$value.presentChild', []]}},'在': {'level': '$$this.level','prevChild': '$$prev','presentChild':{'$concatArrays': ['$$当前', [{'$mergeObjects': ['$$this', {'孩子们': {'$过滤器':{'输入': '$$prev','as': 'e','条件': {'$eq': ['$$e.sponsor', '$$this.plid']}}}}]}]]}}}}}}}}, {'$addFields':{'儿童':'$children.presentChild'}}]

解决方案

您可以使用 $graphLookup 和其他有用的数组运算符，

  • $match 过滤器只有 sponsor 的记录是 ""
  • $graphLookup 获取depthField level
    • 中的子记录和深度数
  • $unwind 解构 downline 数组并允许不删除空子元素
  • $sort 按深度级别字段 level 按降序排列
  • $group 通过 id 字段并重构 downline 数组
  • $addFields 现在找到嵌套级别的子级并分配给它的级别，
    • $reduce 迭代 downline 数组的循环.
    • 初始化默认字段 level 默认值为 -1，presentChild 为 []，prevChild 为 [] 用于条件目的
    • $let 初始化字段:
      • prev 根据条件如果两个 level 相等则返回 prevChild 否则返回 presentChild
      • current 根据条件如果两个 level 相等则返回 presentChild 否则返回 []
    • in 从初始化字段返回 level 字段和 prevChild 字段
      • presentChild $filter downline 从prev数组返回，将当前对象与downline<合并/code> 数组使用 $mergeObjects 并使用 $concatArrays
        • 与 let 的 current 数组连接
  • $addFields 只返回 presentChild 数组，因为我们只需要处理过的数组

db.collection.aggregate([{ $match: { 赞助商: ";} },{$graphLookup:{来自:收藏"，startWith: "$_id",connectFromField: "_id",connectToField: "赞助商",深度字段:级别"，如:下线"}},{$展开:{路径:$下线"，preserveNullAndEmptyArrays: 真}},{ $sort: { "downline.level": -1 } },{$组:{_id: "$_id",赞助商:{ $first: "$sponsor";},公司名称:{ $first:$公司名称"；},下线:{ $push:$下线"；}}},{$addFields:{下线:{$减少:{输入:$下线"，initialValue: { level: -1, presentChild: [], prevChild: [] },在: {$let: {变量:{上一个:{$cond: [{ $eq: ["$$value.level", "$$this.level"] }, "$$value.prevChild", "$$value.presentChild"]},当前的: {$cond: [{ $eq: ["$$value.level", "$$this.level"] }, "$$value.presentChild", []]}},在: {级别:$$this.level"，prevChild: "$$prev",礼物孩子:{$concatArrays: [$$current"，[{$合并对象:[$$this"，{下线:{$过滤器:{输入:$$prev"，如:e"，cond: { $eq: ["$$e.sponsor", "$$this._id"] }}}}]}]]}}}}}}}},{ $addFields: { downline: "$downline.presentChild";} }])

游乐场

In my documents, I have the _id, a companyName and a sponsor (which identifies the parent document, by the _id).

For example, I have this first record which has no sponsor (parent)

_id:607536219910ef23e80e0bbe
companyname:"Main Company"
sponsor:"

Then Company 1, where the Main Company is the parent:

  _id:607e16760a9d2c16e06bc252
    companyname:"Company 1"
    sponsor:"607536219910ef23e80e0bbe"

And Company 2, where Company 1 is the parent:

_id:607e187b0a9d2c16e06bc253
companyname:"Company 2"
sponsor:"607e16760a9d2c16e06bc252"

And Company 3, where Company 2 is the parent:

_id:607e1f470a9d2c16e06bc254
companyname:"Company 3"
sponsor:"607e187b0a9d2c16e06bc253"

Im doing a $match to bring the children records for the main company

{
  sponsor: '607536219910ef23e80e0bbe'
}

And then I $addFields userid, which is a _Id converted to string. This is to match later with sponsor:

{"userid": { "$toString": "$_id" }}

Now, when I graphLookup I get the child company (Company 2) for Main Company, but I do not get Company 3 as a child of Company 2. I just get Company 1, and Company 2:

Here is my graphLookup

{
  from: 'pls',
  startWith: "$userid",
  connectFromField: 'userid',
  connectToField: 'sponsor',
  as: 'downline',
  maxDepth: 100,
  restrictSearchWithMatch: {}
}

Any help will be appreciated.

UPDATE:

As Turivishal said below, the query works, but these are the result I expect:

[{
        "_id": "607536219910ef23e80e0bbe",
        "companyname": "Main Company",
        "downline": [{
            "_id": "607e16760a9d2c16e06bc252",
            "companyname": "Company 1",
            "sponsor": "607536219910ef23e80e0bbe",
            "downline": [{
                "_id": "607e187b0a9d2c16e06bc253",
                "companyname": "Company 2",
                "sponsor": "607e16760a9d2c16e06bc252",
                "downline": [{
                    "_id": "607e1f470a9d2c16e06bc254",
                    "companyname": "Company 3",
                    "sponsor": "607e187b0a9d2c16e06bc253"
                }]
            }]
        }],
        "sponsor": "",
        "userId": "607536219910ef23e80e0bbe"
    }

SOLUTION BY TURIVISHAL:

As per Turivishal solution, this is the final Pipeline that provides a PERFECT downline/hierarchy/tree view of the recurring query and works perfect with Angular Treeview controls. Thank you very much Turivishal. I believe you should post an answer so I can accept it and it can be useful for others.

His solution is quite similar to the one he proposed, but much better. I ended up creating a new field called PLID which duplicates the _id field, and it works amazingly well. I let the administrators decide if they believe this question should be closed, because again, Turivishal solution is based on that Q, but clearer in my opinion. Here is his work:

[
  {
    '$match': {
      'sponsor': '0'
    }
  }, {
    '$graphLookup': {
      'from': 'pls', 
      'startWith': '$plid', 
      'connectFromField': 'plid', 
      'connectToField': 'sponsor', 
      'depthField': 'level', 
      'as': 'children'
    }
  }, {
    '$unwind': {
      'path': '$children', 
      'preserveNullAndEmptyArrays': true
    }
  }, {
    '$sort': {
      'children.level': -1
    }
  }, {
    '$group': {
      '_id': '$plid', 
      'sponsor': {
        '$first': '$sponsor'
      }, 
      'companyname': {
        '$first': '$companyname'
      }, 
      'children': {
        '$push': '$children'
      }
    }
  }, {
    '$addFields': {
      'children': {
        '$reduce': {
          'input': '$children', 
          'initialValue': {
            'level': -1, 
            'presentChild': [], 
            'prevChild': []
          }, 
          'in': {
            '$let': {
              'vars': {
                'prev': {
                  '$cond': [
                    {
                      '$eq': [
                        '$$value.level', '$$this.level'
                      ]
                    }, '$$value.prevChild', '$$value.presentChild'
                  ]
                }, 
                'current': {
                  '$cond': [
                    {
                      '$eq': [
                        '$$value.level', '$$this.level'
                      ]
                    }, '$$value.presentChild', []
                  ]
                }
              }, 
              'in': {
                'level': '$$this.level', 
                'prevChild': '$$prev', 
                'presentChild': {
                  '$concatArrays': [
                    '$$current', [
                      {
                        '$mergeObjects': [
                          '$$this', {
                            'children': {
                              '$filter': {
                                'input': '$$prev', 
                                'as': 'e', 
                                'cond': {
                                  '$eq': [
                                    '$$e.sponsor', '$$this.plid'
                                  ]
                                }
                              }
                            }
                          }
                        ]
                      }
                    ]
                  ]
                }
              }
            }
          }
        }
      }
    }
  }, {
    '$addFields': {
      'children': '$children.presentChild'
    }
  }
]

解决方案

You can use $graphLookup and other useful array operators,

  • $match filter that records only have sponsor is ""
  • $graphLookup to get child records and depth number in depthField level
  • $unwind deconstruct downline array and allow to not remove empty children
  • $sort by depth level field level in descending order
  • $group by id field and reconstruct downline array
  • $addFields now find the nested level children and allocate to its level,
    • $reduce to iterate loop of downline array.
    • initialize default field level default value is -1, presentChild is [], prevChild is [] for the conditions purpose
    • $let to initialize fields:
      • prev as per condition if both level are equal then return prevChild otherwise return presentChild
      • current as per condition if both level are equal then return presentChild otherwise []
    • in to return level field and prevChild field from initialized fields
      • presentChild $filter downline from prev array and return, merge current objects with downline array using $mergeObjects and concat with current array of let using $concatArrays
  • $addFields to return only presentChild array because we only required that processed array

db.collection.aggregate([
  { $match: { sponsor: "" } },
  {
    $graphLookup: {
      from: "collection",
      startWith: "$_id",
      connectFromField: "_id",
      connectToField: "sponsor",
      depthField: "level",
      as: "downline"
    }
  },
  {
    $unwind: {
      path: "$downline",
      preserveNullAndEmptyArrays: true
    }
  },
  { $sort: { "downline.level": -1 } },
  {
    $group: {
      _id: "$_id",
      sponsor: { $first: "$sponsor" },
      companyname: { $first: "$companyname" },
      downline: { $push: "$downline" }
    }
  },
  {
    $addFields: {
      downline: {
        $reduce: {
          input: "$downline",
          initialValue: { level: -1, presentChild: [], prevChild: [] },
          in: {
            $let: {
              vars: {
                prev: {
                  $cond: [{ $eq: ["$$value.level", "$$this.level"] }, "$$value.prevChild", "$$value.presentChild"]
                },
                current: {
                  $cond: [{ $eq: ["$$value.level", "$$this.level"] }, "$$value.presentChild", []]
                }
              },
              in: {
                level: "$$this.level",
                prevChild: "$$prev",
                presentChild: {
                  $concatArrays: [
                    "$$current",
                    [
                      {
                        $mergeObjects: [
                          "$$this",
                          {
                            downline: {
                              $filter: {
                                input: "$$prev",
                                as: "e",
                                cond: { $eq: ["$$e.sponsor", "$$this._id"] }
                              }
                            }
                          }
                        ]
                      }
                    ]
                  ]
                }
              }
            }
          }
        }
      }
    }
  },
  { $addFields: { downline: "$downline.presentChild" } }
])

Playground

这篇关于Mongodb 递归查询与 $graphLookup 不按预期工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
其他开发最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆