CODEWITHSUNDEEP
javascriptnode.jsmongodbmongooseinner-query
mspapant
mspapant

Reputation: 2050

How to pass inner query in mongodb from javascript

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

Upvotes: 2

Views: 1044

Answers (5)

otw
otw

Reputation: 650

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

Upvotes: 0

thammada.ts
thammada.ts

Reputation: 5245

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

Upvotes: 0

Nafeo Alam
Nafeo Alam

Reputation: 4692

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

Upvotes: 0

Evangelos Parchas
Evangelos Parchas

Reputation: 56

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

Upvotes: 2

Plpicard
Plpicard

Reputation: 1066

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.

Upvotes: 0

Related Questions