Grouping mongoDB

Question

I have a series of documents in MongoDB that look like this:

{
    "playerId" : ObjectId("5c58363b5c226c24b0b37860"),
    "gameId" : ObjectId("5c59697f57ef0f512c1cb228"),
    "state" : 4
}

{
    "playerId" : ObjectId("5beab425c0d75e5afabc1638"),
    "gameId" : ObjectId("5c59697f57ef0f512c1cb228"),
    "state" : 4
}

I would like to aggregate and have the below result:

{
    "_id" : ObjectId("5beab425c0d75e5afabc1638"), // the playerId
    "state" : 4,
    "opponents": ["","","",""] // all the opponents gameId
}

Group them by the playerId, find all games, user is playing and get all the opponents playerId.

Any thoughts?

Answer 1

You can try below aggregation:

db.col.aggregate([
    {
        $group: {
            _id: "$playerId",
            games: { $push: "$gameId" }
        }
    },
    {
        $lookup: {
            from: "col",
            localField: "games",
            foreignField: "gameId",
            as: "games"
        }
    },
    {
        $project: {
            _id: 1,
            opponents: {
                $map: {
                    input: {
                        $filter: {
                            input: "$games",
                            cond: { $ne: [ "$$this.playerId", "$_id" ] }
                        }
                    },
                    in: "$$this.playerId"
                }
            }
        }
    }
])

Basically you need to start with $group which will give you all the games for every player. Then you can use $lookup to merge those games with initial collection. In the next step you can use $filter to get only documents with opponents (exclude the ones that have the same _id as current player) and $map to get only playerIds

Prints:

{ "_id" : ObjectId("5beab425c0d75e5afabc1638"), "opponents" : [ ObjectId("5c58363b5c226c24b0b37860") ] }
{ "_id" : ObjectId("5c58363b5c226c24b0b37860"), "opponents" : [ ObjectId("5beab425c0d75e5afabc1638") ] }