MongoDB聚合查询：为matches集合关联forecast并添加用户过滤

Hey there! Let's break down how to add the forecast collection association with the required matchid and userid filters to your existing aggregation query. First, let's recap your collection structures and current query for clarity:

users Collection #

{ id: '123123123', name: 'MrMins' }

matches Collection #

{ id: 1, team1: 23, team2: 24, date: '6/14', matchday: 1, locked: false, score1: null, score2: null }
{ id: 2, team1: 9, team2: 32, date: '6/15', matchday: 1, locked: false, score1: null, score2: null }

countries Collection #

{id: 23, country: "Russia", pais: "Rusia", group: 'A' }
{id: 24, country: "Saudi Arabia", pais: "Arabia Saudita", group: 'A' }
{id: 9, country: "Egypt", pais: "Egipto", group: 'A' }
{id: 32, country: "Uruguay", pais: "Uruguay", group: 'A' }

forecast Collection #

{ matchid: 1, score1: 3, score2: 4, userid: '123123123' }
{ matchid: 2, score1: 3, score2: 0, userid: '123123123' }

db.collection('matches').aggregate([
  { 
    $lookup: { 
      from: 'countries', 
      localField: 'team1', 
      foreignField: 'id', 
      as: 'team1' 
    } 
  },
  { 
    $lookup: { 
      from: 'countries', 
      localField: 'team2', 
      foreignField: 'id', 
      as: 'team2' 
    } 
  }
]).toArray(function(err, res) { 
  callback(err, res); 
});

To link the forecast collection and filter by matchid (mapped to matches.id) and a specific userid, we'll add a pipeline-enabled $lookup stage. This lets us apply the user filter directly during the lookup, so we only fetch relevant forecast entries for each match.

Here's the modified query:

db.collection('matches').aggregate([
  // Existing lookup for team1 details
  { 
    $lookup: { 
      from: 'countries', 
      localField: 'team1', 
      foreignField: 'id', 
      as: 'team1' 
    } 
  },
  // Existing lookup for team2 details
  { 
    $lookup: { 
      from: 'countries', 
      localField: 'team2', 
      foreignField: 'id', 
      as: 'team2' 
    } 
  },
  // New lookup for forecast with match + user filters
  {
    $lookup: {
      from: 'forecast',
      let: { currentMatchId: '$id' }, // Store current match's ID as a reusable variable
      pipeline: [
        {
          $match: {
            $expr: {
              $and: [
                { $eq: ['$matchid', '$$currentMatchId'] }, // Match forecast to current match
                { $eq: ['$userid', '123123123'] } // Filter for your target user
              ]
            }
          }
        }
      ],
      as: 'userForecast' // Field name to store the forecast data
    }
  },
  // Optional: Flatten the forecast array into a single object (cleaner results)
  {
    $addFields: {
      userForecast: { $arrayElemAt: ['$userForecast', 0] }
    }
  }
]).toArray(function(err, res) { 
  callback(err, res); 
});

Key Details: #

• let clause: Creates a variable (currentMatchId) that references the id of the current match from the matches collection, so we can use it in the lookup pipeline.
• $match with $expr: Uses MongoDB expression operators to compare fields across collections. We check that forecast.matchid matches the current match's ID, and filter for the specific userid.
• Optional $addFields stage: Since each match should have at most one forecast per user, this converts the userForecast array (returned by $lookup) into a single object. If no forecast exists for the user/match, userForecast will be null.

Dynamic User ID Tip: #

If you want to use a dynamic user ID instead of hardcoding it, pass it as a variable into the pipeline:

const targetUserId = '123123123'; // Replace with your dynamic user ID

db.collection('matches').aggregate([
  // ... existing lookup stages ...
  {
    $lookup: {
      from: 'forecast',
      let: { currentMatchId: '$id', userId: targetUserId },
      pipeline: [
        {
          $match: {
            $expr: {
              $and: [
                { $eq: ['$matchid', '$$currentMatchId'] },
                { $eq: ['$userid', '$$userId'] }
              ]
            }
          }
        }
      ],
      as: 'userForecast'
    }
  },
  // ... optional flattening stage ...
]).toArray(function(err, res) { 
  callback(err, res); 
});

内容的提问来源于stack exchange，提问作者Benjamin RD