how to get rule Hierarchies along with 1-1 relationship with mongo projection-CodePudding

I have a mongodb document like below Where 2 collection Demo and Rule has 1-1 relationship, like Demo1 has a linked RuleId,

{"_id":{"$oid":"62165ded19477d42a62629e0"},"Name":"Demo1","RuleId":{"$oid":"62165c6242615a8f9341495b"}}

Rule collection has parent-child kind of relationship where Rule1 has No parent

{"_id":{"$oid":"62165c5c42615a8f93414959"},"Name":"Rule1","ParentRuleId":{"$oid":"000000000000000000000000"}}

Rule2 parent = Rule1

{"_id":{"$oid":"62165c6142615a8f9341495a"},"Name":"Rule2","ParentRuleId":{"$oid":"62165c5c42615a8f93414959"}}

and Rule3 parent = Rule2 and so on....(NOT FIXED)

{"_id":{"$oid":"62165c6242615a8f9341495b"},"Name":"Rule3","ParentRuleId":{"$oid":"62165c6142615a8f9341495a"}}

Now I want to JOIN both collection Demo and Rule and need below data kind of data,

Name: Demo1
Rule Hierarchies: [Rule3, Rule2, Rule1]

Can this projection possible in C# with MongoDb driver, please suggest mongo language projection query also, if possible?

CodePudding user response：

Here's one way to do it. You can use "$graphLookup"to recursively follow all the parents. Strangely, "$graphLookup" doesn't guarantee to maintain order of the lookups, but "depthField" can be used to track it. To put everything in order, "$unwind" the rules and sort on the "depthField". "$group" assembles your desired output.

db.Demo.aggregate([
  { "$match": { "Name": "Demo1" } },
  {
    "$graphLookup": {
      "from": "Rule",
      "startWith": "$RuleId",
      "connectFromField": "ParentRuleId",
      "connectToField": "_id",
      "as": "rules",
      "depthField": "recursiveDepth"
    }
  },
  { "$unwind": "$rules" },
  { "$sort": { "rules.recursiveDepth": 1 } },
  {
    "$group": {
      "_id": "$_id",
      "Name": { "$first": "$Name" },
      "Rule Hierarchies": { "$push": "$rules.Name" }
    }
  },
  { "$unset": "_id" }
])

Try it on mongoplayground.net.