MongoDB grouping aggregate query with relationship

Question

Let's say that I have the following documents in the association collection:

{
    "id" : 1,
    "parentId" : 1,
    "position" : {
        "x" : 1,
        "y" : 1
    },
    "tag" : "Beta"
},
{
    "id" : 2,
    "parentId" : 2,
    "position" : {
        "x" : 2,
        "y" : 2
    },
    "tag" : "Alpha"
},
{
    "id" : 3,
    "parentId" : 1,
    "position" : {
        "x" : 3,
        "y" : 3
    },
    "tag" : "Delta"
},
{
    "id" : 4,
    "parentId" : 1,
    "position" : {
        "x" : 4,
        "y" : 4
    },
    "tag" : "Gamma"
},
{
    "id" : 5,
    "parentId" : 2,
    "position" : {
        "x" : 5,
        "y" : 6
    },
    "tag" : "Epsilon"
}

I would like to create an aggregate query to produce the following result:

{
    "_id" : 2,
    "position" : {
        "x" : 2,
        "y" : 2
    },
    "tag" : "Alpha",
    "children" : [
        {
            "position" : {
                "x" : 5,
                "y" : 6
            },
            "tag" : "Epsilon"
        }
    ]
},
{
    "_id" : 1,
    "position" : {
        "x" : 1,
        "y" : 1
    },
    "tag" : "Beta"
    "children" : [
        {
            "position" : {
                "x" : 3,
                "y" : 3
            },
            "tag" : "Delta"
        },
        {
            "position" : {
                "x" : 4,
                "y" : 4
            },
            "tag" : "Gamma"
        }
    ]
}

However, I was able only to create the following grouping query which puts "all-the-related" documents in children array:

db.association.aggregate([{
   $group  : {
       _id : "$parentId",
       children : {
           $push :  {
                   position : "$position",
                   tag :"$tag"
               }
       }
   }
}])

I don't know how to filter out "position" and "tag" specific to "parent" points and put them at the top level.

matthPen · Accepted Answer · 2020-03-20 22:53:46Z

2

Although Valijon's answer is working, it needs to be sorted before. Here's a solution without the need of sorting, but using graphLookup stage (which is perfect to achieve what you need)

db.collection.aggregate([
  {
    $graphLookup: {
      from: "collection",
      startWith: "$id",
      connectFromField: "id",
      connectToField: "parentId",
      as: "children",

    }
  },
  {
    $match: {
      $expr: {
        $gt: [
          {
            $size: "$children"
          },
          0
        ]
      }
    }
  },
  {
    $addFields: {
      children: {
        $filter: {
          input: "$children",
          as: "child",
          cond: {
            $ne: [
              "$id",
              "$$child.id"
            ]
          }
        }
      }
    }
  }
])

The first stage is doing the job.
The second one is here to filter documents that don't have any child.
The third is present only to remove parent from children array. But if you can remove self-reference in the parent, this last stage will not be needed anymore.

You can try it here

answered Mar 20, 2020 at 22:53

matthPen

4,3731 gold badge18 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Lukasz Over a year ago

Hey, $graphLookup is an option - in this case does the task better than $group!

Valijon Over a year ago

@Lukasz @matthpen I also thought about $graphLookup, but I always thought *Lookup had poor performance until now. I did small benchmarking: inserted abot 1.7M records and ran: $group, $graphLookup and $lookup (there are 3 possible solutions). Robo3t, performs aggregation and return first 50 records. So I've performed individually each solution by different operators and here is the execution time: $group : 18.5 sec, $graphLookup : 5.4 sec and $lookup : 96.8 sec wasted time. Indeed $graphLookup is the best solution, upvoted

Valijon · Accepted Answer · 2020-03-21 09:56:27Z

1

By making sure the documents are sorted (parent - children₁ - children₂ ... - children_n), we can merge grouped document with the 1st child (which is parent). In the last step, we need to remove parent from children array.

Try this one:

db.association.aggregate([
  {
    $sort: {
      parentId: 1,
      id: 1
    }
  },
  {
    $group: {
      _id: "$parentId",
      children: {
        $push: {
          position: "$position",
          tag: "$tag"
        }
      }
    }
  },
  {
    $replaceRoot: {
      newRoot: {
        $mergeObjects: [
          "$$ROOT",
          {
            $arrayElemAt: [
              "$children",
              0
            ]
          }
        ]
      }
    }
  },
  {
    $addFields: {
      children: {
        $slice: [
          "$children",
          1,
          {
            $size: "$children"
          }
        ]
      }
    }
  }
])

MongoPlayground

edited Mar 21, 2020 at 9:56

answered Mar 20, 2020 at 0:10

Valijon

13.2k4 gold badges40 silver badges74 bronze badges

2 Comments

Lukasz Over a year ago

Hey, I like your solution, but I cannot assume that my data will be ordered, but I'm giving +1 for your proposed solution, thanks!

Valijon Over a year ago

@Lukasz you can sort by parentId + id fields (make sure you have indexes for these fields)

Collectives™ on Stack Overflow

MongoDB grouping aggregate query with relationship

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related