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.

Answer 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

Answer 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