Use the output of one query to execute another query in Mongo shell

Question

I am executing query in Mongo shell and require the output of this query to execute the second query. The first query is the aggregate query to find the average.

The first query is :

db.table_name.aggregate([{$group: {_id:{userId:'$userId'},avgRating:{$avg:"$rating"}}}])

Now using the _id (userId) in the output, I wanna fetch the data from user table.

Answer 1

No need for another query, you can still run the same aggregate operation but this time adding another pipeline that uses the $lookup operator which pipes the results from the previous $group pipeline to do a left join on the user collection:

db.table_name.aggregate([
    {
        "$group": {
            "_id": "$userId",
            "avgRating": { "$avg": "$rating" }
        }
    },
    {
        "$lookup": {
            "from": "user",
            "localField": "_id",
            "foreignField": "_id",
            "as": "users"
        }
    }
])

---

If your MongoDB version does not support the $lookup operator, then you will need two queries, run as follows:

var cursor = db.table_name.aggregate([
    {
        "$group": {
            "_id": "$userId",
            "avgRating": { "$avg": "$rating" }
        }
    }
]);

var results = cursor.map(function(doc){ 
    var user = db.users.findOne({ "_id": doc._id })
    return {
        user: user,
        avgRating: doc.avgRating
    }; 
});
printjson(results);