MongoDB aggregation and $lookup always returning empty array

Question

Been struggling with this for a while can't work out why I am always getting an empty array for my loop up data.

I have data in my 'users' collection

{
  storeid: 1,
  name: 'joe bloggs'
}

I have a stores collection with the following data in

{
  _id: ObjectId(1),
  storeName: 'Store name'
}

I want to pull the storename that each user is part of when getting all users. I have a query like the below to do this:

User.aggregate([
  {
    $lookup: {
      from: "store",
      localField: "storeid",
      foreignField: "_id",
      as: "storeDetail"
    }
  }
])
.then(users => {
   res.send(users);
}).catch(err => {
  //error
});

However what I get here is 'storeDetail' always returning blank. Not too sure what I am doing wrong, I have checked that my collection names are correct as per db.getCollectionNames() from the mongo shell.

Thanks,

'User' or 'users' collection?

matthPen
– matthPen

2018-09-03 11:41:07 +00:00
Commented Sep 3, 2018 at 11:41 — matthPen
– matthPen, Commented Sep 3, 2018 at 11:41
Collection is users, User schema is User

MLWDEV
– MLWDEV

2018-09-03 11:51:28 +00:00
Commented Sep 3, 2018 at 11:51 — MLWDEV
– MLWDEV, Commented Sep 3, 2018 at 11:51

MLWDEV · Accepted Answer · 2018-09-03 12:17:45Z

2

Turns out I was trying to use different Schema types in localField (string) and foreignField (ObjectId). Converting the localField in the model to be of type ObjectId fixed it.

type: String,//Didn't work
type: mongoose.Schema.Types.ObjectId,//Did work

answered Sep 3, 2018 at 12:17

MLWDEV

659 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Piyush Bansal · Accepted Answer · 2018-09-03 12:18:14Z

Note: You mentioned wrong schema

{
  _id: ObjectId(1),
  storeName: 'Store name'
}

Here is the solution

ObjectId(1) is wrong. ObjectId always be the hash value of 24 lengths of string.

User Collection:

{
    "_id" : ObjectId("5b8d246730739cd950b7b314"),
    "storeid" : ObjectId("5b8d249b30739cd950b7b323"),
    "name" : "joe bloggs"
}

Store Collection

{
    "_id" : ObjectId("5b8d249b30739cd950b7b323"),
    "storeName" : "Store name"
}

Mongo Query

db.getCollection('user').aggregate([
{$lookup:{from: 'store',localField: 'storeid',foreignField: '_id',as: 'stores'}},
])

Result

{
    "_id" : ObjectId("5b8d246730739cd950b7b314"),
    "storeid" : ObjectId("5b8d249b30739cd950b7b323"),
    "name" : "joe bloggs",
    "stores" : [ 
        {
            "_id" : ObjectId("5b8d249b30739cd950b7b323"),
            "storeName" : "Store name"
        }
    ]
}

Hope it helps you.

Collectives™ on Stack Overflow

MongoDB aggregation and $lookup always returning empty array

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related