$lookup not working

Working with Data
,
1

Hey everyone,

I’m new to MongoDB and I’m trying to get a lookup to work. I’ve build a demo project with tournaments and each tournament document has an array of matches (ObjectId’s to be specific), which obviously are documents in a matches collection.

I wrote this in JavaScript, but am I doing something wrong since it’s about ObjectId’s?

db.collection('tournaments').aggregate([
    {
      $lookup: {
        from: 'matches',
        localField: 'matches',
        foreignField: '_id',
        as: 'matchesList',
      },
    },
  ]);

Thanks in advance!

Gr,

Andy

2

Hello @AndyLemaire, welcome to the MongoDB Community forum!

Your code looks fine to me. I have a small example here, though I used integers for _id values - but that should not matter. You can also include some sample documents for further discussion.

tournaments:

{ "_id" : 1, "name" : "t1", "matches" : [ 11, 19 ] }

matches:

{ "_id" : 11, "match" : "m-1" }
{ "_id" : 12, "match" : "m-2" }
{ "_id" : 19, "match" : "m-9" }

The result is:

{
        "_id" : 1,
        "name" : "t1",
        "matches" : [
                11,
                19
        ],
        "matchesList" : [
                {
                        "_id" : 11,
                        "match" : "m-1"
                },
                {
                        "_id" : 19,
                        "match" : "m-9"
                }
        ]
}

Note the code runs from mongo shell. Here is another example from the MongoDB Manual: Use lookup with an array.

3

Hey @Prasad_Saya,

Thanks for your reply, that’s exactly what I have (I did follow the manual you linked) and also the result I’m expecting. Then there must be something wrong with my JavaScript in a further stage.

I wasn’t able to view or print the result.

Gr,

Andy

4

Are you using the code in mongo shell or are you writing code using NodeJS driver? You can include the code you are trying.

5

@Prasad_Saya NodeJS.

const updated = await db.collection('tournaments').aggregate([
    {
      $lookup: {
        from: 'matches',
        localField: 'matches',
        foreignField: '_id',
        as: 'matchesList',
      },
    },
  ]);
  const updatedTournament = updated.findOne({ ranking });
  const result = JSON.parse(JSON.stringify(updatedTournament));

I’d even like to do another $lookup of matches, it contains teams. Is that possible?

6

@AndyLemaire, the aggregate query returns a cursor (see collection.aggregate). To get all the data from the cursor at once use the toArray method on the cursor. Then you can pass the returned array to anywhere in the application code. Here is some code showing that:

const MongoClient = require('mongodb').MongoClient;
const url = 'mongodb://localhost:27017';
const client = new MongoClient(url, { useUnifiedTopology: true });

(async function() {
    try {
		await client.connect();
		console.log("Connected to db server");
		coll = await client.db('test').collection('tournaments');
		const lookupStage = {
			$lookup: {
				from: 'matches',
				localField: 'matches',
				foreignField: '_id',
				as: 'matchesList',
			}
		};
		cursor = await coll.aggregate([lookupStage]);
        docsList = await cursor.toArray();
		console.log('OUTPUT: ', JSON.stringify(docsList));
	} 
	catch (e) { 
		console.error('*** ERROR:', e); 
	}
	finally {
		console.log("Closing connection.");
		client.close();
	}
})();

You can have multiple lookups within the same aggregate. Since, your collection in the aggregate is tournaments you can also do another “join” with teams on it.

7

@AndyLemaire , here are some useful topics related to coding with NodeJS Driver.

https://www.mongodb.com/quickstart/node-aggregation-framework/

8

@Prasad_Saya ok clear, but then I can’t get teams nested in matches since teams is also an array in those documents…?

9

You need to post sample input documents of all the collections you are referring to (I can’t visualize your data, sometimes!).

10

@Prasad_Saya

tournaments
{"_id": 11,"matches":[22,23,24]}

matches
{"_id":22,"teams":[{33,34}]}, {"_id":23,"teams":[{34,33}]}, {"_id":24,"teams":[{33,34}]}

teams
{"_id":33,"players":[44,45]}, {"_id":34,"players":[46,47]}

I don’t know how you format it so readable though…

11

Hello @AndyLemaire, here is your query with the three collections. Note the two new stages I have used in the aggregate query: $unwind and $group.

db.tournament.aggregate([
  {
      $lookup: {
          from: 'matches',
          localField: 'matches',
          foreignField: '_id',
          as: 'matchesList',
      }
  },
  { 
      $unwind: "$matchesList" 
  },
  {
      $lookup: {
          from: 'teams',
          localField: 'matchesList.teams',
          foreignField: '_id',
          as: 'matchesList.teams',
      }
  },
  { 
      $group: { 
          _id: "$_id",
          name: { $first: "$name" },   // <------ this field I added is the tournament name
          matchesList: { $push: "$matchesList" }
      }
  }
])
12

Nice thanks a lot! Is there any reason for the $group?
Also now the array of matches is being replaced? I won’t have every match anymore?

13

Only the matching matches with the tournament will be selected.


The $unwind makes an array of elements/documents into single documents. After the second lookup, we put the data back into the array using the $group.


The data I had used and the output is as follows:

> db.tournament.find()
{ "_id" : 1, "name" : "t1", "matches" : [ 11, 19 ] }
> db.matches.find()
{ "_id" : 11, "match" : "m-1", "teams" : [ 33, 34 ] }
{ "_id" : 12, "match" : "m-2" }
{ "_id" : 13, "match" : "m-3" }
{ "_id" : 19, "match" : "m-9", "teams" : [ 33, 35 ] }
> db.teams.find()
{ "_id" : 33, "players" : [ 44, 45 ] }
{ "_id" : 34, "players" : [ 46, 47 ] }
{ "_id" : 35, "players" : [ 46, 47 ] }

The result from the aggregate query was:

{
        "_id" : 1,
        "name" : "t1",
        "matchesList" : [
                {
                        "_id" : 11,
                        "match" : "m-1",
                        "teams" : [
                                {
                                        "_id" : 33,
                                        "players" : [
                                                44,
                                                45
                                        ]
                                },
                                {
                                        "_id" : 34,
                                        "players" : [
                                                46,
                                                47
                                        ]
                                }
                        ]
                },
                {
                        "_id" : 19,
                        "match" : "m-9",
                        "teams" : [
                                {
                                        "_id" : 33,
                                        "players" : [
                                                44,
                                                45
                                        ]
                                },
                                {
                                        "_id" : 35,
                                        "players" : [
                                                46,
                                                47
                                        ]
                                }
                        ]
                }
        ]
}
14

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.