Last Updated
Viewed 33 Times

I have 2 collections , Employees and Leads.

Consider the query :

  const collections = Employees.aggregate(
      [

        {
          $group: {
            _id: "$LeadId",
            total: { $sum: "$..." }
          }
        },
        {
          $lookup: {
            from: "leads",
            localField: "_id",
            foreignField: "LeadId",
            as: "Joined"
          }
        },
        { $unwind: "$Joined" },
        { $sort: { total: -1 } }
      ],
      function(err, results) {
        if (err) {
          console.log(err);
        }

        // whatever
      }
    );

It produces the result :

[0]    _id: '6822ace7-00c4-4a3c-ac8e-56c7d715066c',
[0]     total: 0,
[0]     Joined: {
[0]       _id: 5de1bda51406d20017e69dbb,
[0]       Rooms: '3',
[0]       PhoneNumberMasque: '541234567',
[0]       supplier: 5de1bd861406d20017e69db2,
[0]       PackageId: 'Package_1457d5a7-2798-4ad6-99ca-a634b94e845d',
[0]       LeadId: '6822ace7-00c4-4a3c-ac8e-56c7d715066c',
[0]       __v: 0
[0]     }
[0]   },

The rest of the result of lookup is inside the Joined array.

How can it be extracted out ?

EDIT With @Ashh suggestion the result is :

  {
[0]     _id: '559c02a3-d933-41ff-b605-82a23ee94702',
[0]     Rooms: '2',
[0]     PhoneNumberMasque: '52123456',
[0]     supplier: 5de1bd861406d20017e69db2,
[0]     PackageId: 'Package_1457d5a7-2798-4ad6-99ca-a634b94e845d',
[0]     LeadId: '559c02a3-d933-41ff-b605-82a23ee94702',
[0]     PublishDate: 2019-11-30T02:53:57.000Z,
[0]     Duplicate: false,
[0]     Valid: true,
[0]     __v: 0,
[0]     total: 2,
[0]     Joined: {
[0]       _id: 5de1bda51406d20017e69dba,
[0]       Rooms: '2',
[0]       PhoneNumberMasque: '52123456',
[0]       supplier: 5de1bd861406d20017e69db2,
[0]       PackageId: 'Package_1457d5a7-2798-4ad6-99ca-a634b94e845d',
[0]       LeadId: '559c02a3-d933-41ff-b605-82a23ee94702',
[0]       PublishDate: 2019-11-30T02:53:57.000Z,
[0]       Duplicate: false,
[0]       Valid: true,
[0]       __v: 0
[0]     }
[0]   },

I am doing a $lookup to find 'events' where a customer is an attendee. The list of attendants is an array like this:

attendee: [{customer: <ID>}]

I tried this but it always returns an empty array:

$lookup: {
    from: "events",
    let: { customer: "$_id" },
    pipeline: [
        {
            $match: {
                $expr: {
                    $and: [
                        { $eq: ['$attendee.customer', '$$customer'] },
                    ]
                },
            }
        },
        { $limit: 1 },
        { $sort: {start: -1} },
        { $project: { id: "$_id", start: 1, end: 1, name: 1, host: 1 } },
    ],
    as: "event"
}

Note: Edits below where I tried this directly using mongo shell and correct collection names, but still the same issue.

I am currently trying to learn Node and Mongodb. I am looking to understand how to add one document with another in a query. All the documentation points back to $lookup.

I have the two following models set up, which both work perfectly on their own

var BearSchema   = new Schema({
    name: String
});

module.exports = mongoose.model('Bear', BearSchema);

var CommentSchema   = new Schema({
    creator_id : { type: String, ref: 'Bear' },
    comment: String
});

module.exports = mongoose.model('Comment', CommentSchema);

I will omit other set up details and get straight to the queries.

When I run Bear.find() I get the expected result...

    [
      {
        "_id": "585887a29b7915f437742b88",
        "name": "new bear",
        "__v": 0
      }
    ]

When I run Comment.find() I get the expected result...

[
  {
    "_id": "585887ae9b7915f437742b89",
    "creator_id": "584de876238179030d7d7916",
    "comment": "yoyoyo",
    "__v": 0
  },
  {
    "_id": "585887e09b7915f437742b8a",
    "creator_id": "585887a29b7915f437742b88",
    "comment": "ok lets give this a go",
    "__v": 0
  }
]

Note the creator_id in the second comment is the same as the _id in the bear result.

I then run

    Bear.aggregate([
        {
            $lookup: {
                from: "Comment",
                localField: "_id",
                foreignField: "creator_id",
                as: "comments"
            }
        }
    ], function (err, bears) {
        if (err)
            res.send(err);

        res.json(bears);
    });

and get the following:

[
  {
    "_id": "585887a29b7915f437742b88",
    "name": "new bear",
    "__v": 0,
    "comments": []
  }
]

I was hoping the following would appear:

[
  {
    "_id": "585887a29b7915f437742b88",
    "name": "new bear",
    "__v": 0,
    "comments": [      
       {
         "_id": "585887e09b7915f437742b8a",
         "creator_id": "585887a29b7915f437742b88",
         "comment": "ok lets give this a go",
         "__v": 0
       }
     ]
  }
]

I cant understand in this situation how it would know what "Comment" is referring to.

EDIT: From the documentation I can see the from field says: Specifies the collection in the same database to perform the join with. The from collection cannot be sharded.

EDIT 2: In mongoshell I have ran the following queries and their results, as you can see the same issue is still appearing even with the correct collection name, however I can now see ObjectId() may be the issue...

> show collections
bears
comments


> db.bears.find();
{ "_id" : ObjectId("585887a29b7915f437742b88"), "name" : "new bear", "__v" : 0 }

> db.comments.find();
{ "_id" : ObjectId("585887ae9b7915f437742b89"), "creator_id" : "584de87623817903
0d7d7916", "comment" : "yoyoyo", "__v" : 0 }
{ "_id" : ObjectId("585887e09b7915f437742b8a"), "creator_id" : "585887a29b7915f4
37742b88", "comment" : "ok lets give this a go", "__v" : 0 }


> db.bears.aggregate([ { $lookup: { from: "comments", localField: "_id", foreign
Field: "creator_id", as: "comments" } } ]);
{ "_id" : ObjectId("585887a29b7915f437742b88"), "name" : "new bear", "__v" : 0,
"comments" : [ ] }

So I am trying to do a semi-complicated query in mongoose. It is as follows:

Event.findOne({ users: { $elemMatch: { user: someUserId, going: 1 }, sort: {createTime: -1} } }

Basically what I would like to do is find a single Event that has a user in its user array that matches an Id and is attending. I have multiple user records for each user in the user array so I want to find the most recent one, to do this I would like to sort the user array by createTime. This is where the error is coming in and it just returns undefined. It works just fine when I don't include the sort function, is there any way to include that?

Here is what my Event object looks like:

{ 
  _id: 1,
  endTime: 1429060173865,
  createTime: 1429051773902,
  startTime: 1429052973865,
  description: 'some desc',
  creator: 2,
  users:
   [ { user: 1,
       status: 1,
       going: 1,
       createTime: 1429051773749,
       _id: 552d997d8e923847306e2c21 },
     { user: 1,
       status: 1,
       going: 1,
       createTime: 1429051773922,
       _id: 552d997d8e923847306e2c25 },
     { user: 1,
       status: 9,
       going: 0,
       createTime: 1429051773942,
       _id: 552d997d8e923847306e2c26 } ],
  destroyed: 0 }

Is there any way to make this query entirely in mongoose?

Similar Question 4 (1 solutions) : How to change order of array with MongoDB?

Similar Question 7 (2 solutions) : How to convert Mongodb collection results into Array?

Similar Question 8 (2 solutions) : How to delete broken references from mongodb array?

cc