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!

3 answers

  • answered 2018-05-16 05:26 mintekhab

    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.

  • answered 2018-05-16 06:08 Ashish

    You need to $unwind first and then set correct values for localField and foreignField

    db.db('database').collection("business").aggregate([
        {"$unwind": "$boxes"} ,
        {$lookup:{
            from: "boxes",
            localField: "boxes._id",
            foreignField: "_id",
            as: "box"
        }}
    ])
    

  • answered 2018-05-16 06:26 IftekharDani

    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'
                     } 
                  }
           }
        }
    
    ])