Mongodb recursive query not working as expected with $graphLookup

user_14867749 0
Viewed 630

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:

enter image description here

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'
    }
  }
]
mongodb
1 Answers

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

user_89871280
Related