CODEWITHSUNDEEP
mongodbaggregatelookupref
Bruno Ornelas
Bruno Ornelas

Reputation: 331

Mongodb: Not expected result from aggregate ($lookup)

I´m looking for some help.

I have a collection called 'business' where I have objects like this:

[{
    "_id": ObjectId("5aefa97166763d28fc984c7a"),
    "name": "Business 1",
    "boxes": [
        {
            "_id": ObjectId("5ac6bb69f3c36e17f0d34bd2")
        }
    ]
}]

There is another collection called boxes which has objects like:

[{
    _id: ObjectId("5ac6bb69f3c36e17f0d34bd2"),
    name:"Box1",
    color:"blue"
}]

The idea here is that there are businesses that own boxes and I want to keep both collection separated.

That said, I would like to retrieve this result:

[{
    "_id": ObjectId("5aefa97166763d28fc984c7a"),
    "name": "Business 1",
    "boxes": [{
        _id: ObjectId("5ac6bb69f3c36e17f0d34bd2"),
        name:"Box1",
        color:"blue"
    }]
}]

But I am getting this result:

[{
    "_id": ObjectId("5aefa97166763d28fc984c7a"),
    "name": "Business 1",
    "boxes": [
        {
            "_id": ObjectId("5ac6bb69f3c36e17f0d34bd2")
        }
    ]
}]

Using $lookup as you can see below:

db.db('database').collection("business").aggregate({
    $lookup:{
        from: "boxes",
        localField: "_id",
        foreignField: "_id",
        as: "box"
    },
    "$unwind": "$boxes" 
    }).toArray(function(err, result) {
        if (err) throw err; 
        res.send(result);
        db.close();
        res.end();
    });

What am I doing wrong?

Thank you all!

Upvotes: 0

Views: 416

Answers (2)

IftekharDani
IftekharDani

Reputation: 3729

If You have multiple objectIds and need in one array Boxes:

Ex: "boxes": [
        {
            "_id": ObjectId("5ac6bb69f3c36e17f0d34bd2")
        },
       {
            "_id": ObjectId("5ac6bb69f3c36e17f0d34bd3")
        }
    ]

Then u need to do group

Query : 

db.getCollection('business').aggregate([
    {"$unwind": "$boxes"} ,
    {$lookup:{
        from: "boxes",
        localField: "boxes._id",
        foreignField: "_id",
        as: "box"
    }},
    {
      $group: {
        _id: '$_id',
        name: { $first: '$name' },
        boxes: { 
           $push: {
                  _id: '$boxes._id',
                  name: '$boxes.name'
                   color: '$boxes.color'
                 } 
              }
       }
    }

])

Upvotes: 0

mintekhab
mintekhab

Reputation: 203

This should help 

db.business.aggregate([{$lookup:{from: "boxes", localField: "boxes._id", foreignField: "_id", as: "box" }},{"$project":{"_id":1,"name":1,"boxes":"$box"}}])

The lookup creates an array "box" which has all matching documents from the boxes collection.The next stage in the pipeline, $project , selects _id and name from the new document and renames the box array to boxes.

Upvotes: 1

Related Questions