I have a collection named network in mongoldb, that has the objects like this

{
        "_id" : ObjectId("639755094ab8f1702b3b661a"),
        "Id" : "Tom",
        "otherId" : "Jack",
        "source" : "proposal",
        "year" : "2017"
}
{
        "_id" : ObjectId("6397550a4ab8f1702b3b6ff7"),
        "Id" : "Tom",
        "otherId" : "Jack",
        "source" : "grant",
        "year" : "2018"
}
{
        "_id" : ObjectId("6397550a4ab8f1702b3b7336"),
        "Id" : "Tom",
        "otherId" : "Jack",
        "source" : "tech",
        "year" : null
}
{
        "_id" : ObjectId("6397550b4ab8f1702b3b75ae"),
        "Id" : "Tom",
        "otherId" : "Jack",
        "source" : "proposal",
        "year" : "2019"
}
{
        "_id" : ObjectId("6397550b4ab8f1702b3b7a03"),
        "Id" : "Tom",
        "otherId" : "Jack",
        "source" : "proposal",
        "year" : "2018"
}
{
        "_id" : ObjectId("6397550b4ab8f1702b3b7a01"),
        "Id" : "Sarah",
        "otherId" : "Edward",
        "source" : "proposal",
        "year" : "2018"
}
{
        "_id" : ObjectId("6397550b4ab8f1702b3b7a04"),
        "Id" : "Sarah",
        "otherId" : "Edward",
        "source" : "grant",
        "year" : "2018"
}

I want to make another collection using $out function as the following desired output

{
        "_id" : ObjectId("6397550b4ab8f1702b3b7a03"),
        "Id" : "Tom",
        "otherId" : "Jack",
        "source" : ["proposal","grant","tech"]
        "count" : 5
}
{
        "_id" : ObjectId("6397550b4ab8f1702b3b7a02"),
        "Id" : "Sarah",
        "otherId" : "Edward",
        "source" : ["proposal","grant"]
        "count" : 2
}

count if the number of times they worked together so actually db.network.find({"Id":"Tom","otherId":"Jack"}).count() work to find count, but how to make it in list of source for each collaborators without repetition, I couldn't find the solution.

CodePudding user response：

Maybe like this:

db.collection.aggregate([
{
 $group: {
  _id: {
    Id: "$Id",
    otherId: "$otherId"
  },
  count: {
    $sum: 1
  },
  source: {
    $addToSet: "$source"
  }
}
},
{
$project: {
  _id: 0,
  Id: "$_id.Id",
  otherId: "$_id.otherId",
  count: 1,
  source: 1
 }
}
])

Explained:

1. Group with $addToSet so you get the source list without duplicates
2. Project only the needed fields as per your needs

Playground