Skip to content
Advertisement

join object from another documnt with localfield key

i have a competition doc with field teams array of object with _id of team and a score doc with teamId field

competitions.teams = [{_id: 100,..}, {..}] score.teamId = 100

when aggregatig score i want to group it to the competition teams but imm getting all team inside the group innstead of matching id

sample document https://mongoplayground.net/p/yJ34IBnnuf5

db.scores.aggregate([
  {
    "$match": {
      "type": "league"
    }
  },
  {
    "$lookup": {
      "from": "competitions",
      "localField": "competitionId",
      "foreignField": "_id",
      "as": "comp"
    }
  },
  {
    "$unwind": {
      "path": "$comp",
      "preserveNullAndEmptyArrays": true
    }
  },
  {
    "$project": {
      "comp.teams": 1,
      "teamId": 1
    }
  },
  {
    "$group": {
      "_id": "$teamId",
      "results": {
        "$push": "$comp.teams"
      }
    }
  }
])

returns all team in group instead of matched teamid

{ 
    "_id" : 100
    "results" : [
        {
           "_id": 100,
           "name": "team 1"
        },
        {
           "_id": 101,
           "name": "team 2"
        }
    ]
}
{ 
    "_id" 101
    "results" : [
        {
           "_id": 100,
           "name": "team 1"
        },
        {
           "_id": 101,
           "name": "team 2"
        }
    ]
}

this is the result im trying to accomplish please guide me

{ 
    "_id" : 100
    "results" : [
        {
           "_id": 100,
           "name": "team 1"
        }
    ]
}
{ 
    "_id" 101
    "results" : [
        {
           "_id": 101,
           "name": "team 2"
        }
    ]
}

what should i do i’ve read the docs this seems to be the way?

Advertisement

Answer

Demo – https://mongoplayground.net/p/ETeroLftcZZ

You have to add $unwind: { "path": "$comp.teams" } and after that group by { $group: { "_id": "$comp.teams._id" ... }

db.scores.aggregate([
  { $match: { "type": "league" } },
  { $lookup: { "from": "competitions", "localField": "competitionId", "foreignField": "_id", "as": "comp" } },
  { $unwind: { "path": "$comp",  "preserveNullAndEmptyArrays": true  } },
  { $unwind: { "path": "$comp.teams",  "preserveNullAndEmptyArrays": true }},
  { $group: { "_id": "$comp.teams._id",  "results": { $push: "$comp.teams" } } }
])

Demo with more data – https://mongoplayground.net/p/b41Ch5ge2Wp

Advertisement