How to pass inner query in mongodb from javascript

Question

I want to create an aggregation pipeline which one of the matches use results from other collection (inner query). This is an example:

db.users.aggregate([
    {"$match":{"id":{"$in":["0","1","2"]},"p": {$in: db.groups.distinct("p", {"enable":true)}}},
    {"$group":{"_id":"$v","number":{"$sum":1}}}
])

and i need to do the query from javascript. Actually the application is nodejs with mongoose.

Unfortunately when mongoose executes the query i get:

MongoError: $in needs an array

This is the query that mongoose prints:

Mongoose: users.aggregate([
    {"$match":{"id":{"$in":["0","1","2"]},"p": {$in: 'db.groups.distinct("p", {"enable":true)'}},
    {"$group":{"_id":"$v","number":{"$sum":1}}}
])

Can anyone help me how can i pass the inner query from javascript?

UPDATE: The collections are sharded so i cannot user $lookup this is the reason i want to use the $in with distinct

Answer 1

You could execute first the inner query and use the result in the aggregate.

It is also possible to do this directly in the aggregate using $lookup stage. Here is the doc https://docs.mongodb.com/manual/reference/operator/aggregation/lookup/#pipe._S_lookup

The result would look like

Mongoose: users.aggregate([{
$lookup:
     {
       from: "groups",
       localField: "p",
       foreignField: "p",
       as: "p_group"
     }
},
{"$match":{"id":{"$in":["0","1","2"]},"p_group.enable": true},
{"$group":{"_id":"$v","number":{"$sum":1}}}
]);

You can $lookup which attach a document to each user based on a decided field.

// in user collection of database
{ id: '0', p: 'somevalue'} 
// in group collection of data
{ p: 'somevalue', enable: true }
// After a lookup
{ id: '0', p: 'somevalue', asLookupField: { p: 'somevalue', enable: true }} 

Then you match and group.

Note it is also possible to match with part of the query before to lookup to reduce the number in the lookup phase.

Answer 2

TLDR;

The mongoose equivalent would be to run the nested query first, and pass the results to the aggregation.

groups.distinct("p", {"enable": true}).exec().then(matchingGroups => {
    return users.aggregate([
        {$match: {"id": {$in: ["0", "1", "2"]}, p: {$in: matchingGroups}}},
        {$group:{_id:"$v", number:{$sum:1 }}}
    ]).exec();
}).then(aggregationResult => {
    console.log(aggregationResult);
});

Explanation

When executing the following script in mongo shell, what happens is that the inner query (distinct) is submitted first, and then the result is passed to the outer query (aggregation) which is then submitted for execution. This can be confirmed by capturing a packet trace. In the attached images, we can see the first query submission (packet 9) the response received (packets 10 and 11), and the aggregation query submission (packets 12 and 13).

Answer 3

As you are using mongoose, you can get a response from one query then run another query inside then.

Example Code:

 router.route("/update-payment-amount")
        .get((req, res) => {
            Order.find({
                "$and": [
                    { "order_no": 100413 }
                ]
            })
                .then(orders => {
                    return updateCollectedAmount(orders);
                })
                .then(result => {
                    res.json(result)
                })
        })

    function updateCollectedAmount(orders) {
        let updates = orders.map(order => {
            return new Promise((resolve, reject) => {
                let totalPaid = order.payment_collection.total_paid;

                Order.update({
                    _id: mongoose.Types.ObjectId(order._id)
                }, {
                    $set: {
                        "payment_collection.collection_info.0.collected_amount": totalPaid
                    }
                }, {
                    multi: true
                })
                    .exec()
                    .then(status => {
                        resolve(status)
                    })
                    .catch(err => {
                        //console.log(err)
                        reject(err)
                    })
            })
        })
        return Promise.all(updates)
    }

Answer 4

Using async/await you could get the same result with very little code change from your original MongoDB shell code.

const result = await users.aggregate([
    {"$match":{"id":{"$in":["0","1","2"]},"p": {$in: await groups.distinct("p", {"enable":true)}}},
    {"$group":{"_id":"$v","number":{"$sum":1}}}
])

This is equivalent to executing the "inner" query first and pass it to the "outer" query

const distinctP = await groups.distinct("p", { "enable": true })
const result = await users.aggregate([
  {
    "$match": {
      "id": { "$in": ["0","1","2"] },
      "p": { $in: distinctP }
    }
  },
  {
    "$group": { "_id": "$v", "number": { "$sum": 1 } }
  }
])

Answer 5

Not sure if this is just a typo but you are missing a bracket: {"enable":true):

Corrected:

db.users.aggregate([
    {"$match":{"id":{"$in":["0","1","2"]},"p": {$in: db.groups.distinct("p", {"enable":true})}}},
    {"$group":{"_id":"$v","number":{"$sum":1}}}
])

How I would convert this to Mongoose:

    const docs = await db.collection("users").aggregate([{
            $match: {
                id: {
                    $in: ["0", "1", "2"]
                },
                p: {
                    $in: []
                }
            }
        }, {
            $group: {
                _id: "$v",
                number: {
                    $sum: 1
                }
            }
        }])
        .toArray();

    console.log(docs.map(it => it._id));