CODEWITHSUNDEEP
mongodbmongodb-querynode-mongodb-native
Benjamin RD
Benjamin RD

Reputation: 12034

Add two extra filter of 1 collection using mongo

I have 4 collections:

users

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

matches

{ 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

{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

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

My query:

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);
    });

Just now, I have the relation between matches, and countries (two times). How I can add the additional filter to forecast doing a relation with matchid and userid ?

Upvotes: 0

Views: 48

Answers (2)

Ashh
Ashh

Reputation: 46441

It will be quite simple using the mongodb 3.6 $lookup version with using nested pipeline

db.matches.aggregate([
  {
    $lookup: {
      from: 'countries',
      let: { 'team1': '$team1' },
      pipeline: [
        { $match: { $expr: { $eq: [ '$id', '$$team1' ] } }}
      ],
      as: 'team1'
    }
  },{
    $lookup: {
      from: 'countries',
      let: { 'team2': '$team2' },
      pipeline: [
        { $match: { $expr: { $eq: [ '$id', '$$team2' ] } }}
      ],
      as: 'team2'
    }
  }, {
    $lookup: {
      from: 'forecast',
      let: { "match_id": "$id" },
      pipeline: [
        { $match: { $expr: { $eq: [ '$matchid', '$$match_id' ] } }},
        { $lookup: {
          from: 'users',
          let: { 'userid': '$userid' },
          pipeline: [
            { $match: { $expr: { $eq: [ '$id', '$$userid' ] } }}
          ],
          as: 'user'
        }}
      ],
      as: 'forecast'
    }
  }
])

Upvotes: 1

mickl
mickl

Reputation: 49945

$lookup works as left outer join but it inserts "joined" results as an array in your document. So every field you add (using as option) will be an array.

To get data both from forecasts and users you have to transform that array into an object and you can do it using $arrayElemAt operator:

db.matches.aggregate([
    {
        $lookup: {
          from: 'countries',
          localField: 'team1',
          foreignField: 'id',
          as: 'team1'
        }
    },{
        $lookup: {
          from: 'countries',
          localField: 'team2',
          foreignField: 'id',
          as: 'team2'
        }
    },{
        $lookup: {
          from: 'forecast',
          localField: 'id',
          foreignField: 'matchid',
          as: 'forecast'
        }
    }, {
        $addFields: {
          forecast: { $arrayElemAt: [ "$forecast", 0 ] }
        }
    },{
        $lookup: {
          from: 'users',
          localField: 'forecast.userid',
          foreignField: 'id',
          as: 'forecast.user'
        }
    }
])

Upvotes: 0

Related Questions