CODEWITHSUNDEEP
mongodb
VSO
VSO

Reputation: 12666

MongoDb: Nest one Collection's Documents in Another For Query Results

Let's say I have two MongoDb collections (with example fields):

People: 
_id: 2
name: 'bob'
carIds: [1,2,3]

and

Cars:
_id: 82
model: 'Camry'

Is there a way to return a list of People with a new field Cars which will have an array of Cars that have a carId that matches carIds in People.

I do NOT want to create the field permanently, just for the query results. Can I do this with map somehow? I tried:

var peopleWithCars = db.people.find({
}).map(function(doc) {

    var carIds = doc.carIds; 

    var cars = db.cars.find({
        _id: { $in: carIds} 
    })

    doc.cars= cars;

    return doc;
});

return peopleWithCars ;

And got:

Error: Line 18: Illegal return statement

Edit - this is what finally worked:

db.people.find({
        }).map(function(doc) {

        var carIds = doc.carIds; 

        var cars = db.cars.find({
            _id: { $in: carIds} 
        }).toArray(); 

        doc.cars= cars;

        return doc;
    });

For some reason it still doesn't like vars, but this forces items to be returned, instead of a query for them.

Upvotes: 1

Views: 131

Answers (2)

chridam
chridam

Reputation: 103455

One approach you could take with a single query is by using the aggregation framework and running a pipeline that has the $lookup operator. This performs a left outer join to an unsharded collection in the same database to filter in documents from the "joined" collection for processing. The $lookup stage does an equality match between a field from the input documents with a field from the documents of the "joined" collection.

The following example shows how you can apply this with your case:

db.people.aggregate([
    { "$unwind": "$carIds" },
    {
        "$lookup": {
            from: "cars",
            localField: "carIds",
            foreignField: "_id",
            as: "cars"
        }
    },
    { "$unwind": "$cars" },
    {
        "$group": {
            "_id": "$_id",
            "name": { "$first": "$name" },
            "cars": { "$push": "$cars" }
        }
    }
])

Upvotes: 0

GeertPt
GeertPt

Reputation: 17874

The find() method returns a cursor. You need to convert it to an array ( cursor.toArray () IIRC). That should solve both your problems.

var cars= db.find(...).toArray()

But to be honest, that is not the best design if you want to use this query very often. You'd better add a field 'personId' to the car and query on the cars collection with a single query.

Upvotes: 3

Related Questions