How to get counting of documents from another collection in mongoose?

Question

I'd like to know how can I get a specific field value?

In my mysql :

select *, 
(select count(qty) from score where id = a.id) as d_qty 
from mlbhitters as a order by no desc

In my mongoose :

 this.aggregate([
    {
        $match:options.query
    }, 
    {
        $sort:{
            no:-1
        }
    },
    {
        $limit:options.limit
    }
]).exec(callback);

Here is the point that where should I insert sub query in my mongoose?

(select count(qty) from score where id = a.id) as d_qty 

MongoDB is hard for me to understand how it works :(

Answer 1

Considering simplest scenario where you have two collections like below:

db.col1.save({ _id: 1 })
db.col2.save({ col1_id: 1 })
db.col2.save({ col1_id: 1 })
db.col2.save({ col1_id: 1 })

you can use $lookup to get the data from col2 into col1 by specifying which fields define the "relationship" and then you can use $size to get the number of elements, try:

db.col1.aggregate([
    {
        $lookup: {
            from: "col2",
            localField: "_id",
            foreignField: "col1_id",
            as: "col2docs"
        }
    },
    {
        $project: {
            col2size: { $size: "$col2docs" }
        }
    }
])

outputs:

{ "_id" : 1, "col2size" : 3 }