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

Omega

Answer 1

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).

Dreeda

Answer 2

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.

Beverage

Thank you for the response, but the collections are sharded so no lookup is allowed. I will update the question – Omega

Have you looked at docs.mongodb.com/manual/core/… . – Beverage

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

Festination

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

Barytone

Thanks for your response, but this is not was i am looking for. I need to do the distinct query in db not move the distinct results back and forth. I want to do the query as you can see in database passing in from mongoose – Omega

@Omega unfortunately, as of v4.2, MongoDB doesn't have any other way to refer to other collections other than $lookup. And as you mentioned, your collections are both sharded so $lookup is not an option. – Barytone

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));

Omphalos