Mongoose select record with subdocuments count less than X

Question

I have nodejs/mongodb and mongoose setup. Everything is the last version.

I'm kinda "brand new" in NOSQL and trying to rewrite using NOSQL and mongoose ORM with such SQL query:

select * from documents where count(select * from subdocuments where subdocuments.documentId = documents.id) = 5 order by documents.id desc limit 1

In my case (nested collections) the main collection structure is:

   documents: [{
         _id: ObjectId
         subdocuments: [{
              _id: ObjectId
         }]
   }]

So I need only get last one object that has count subdocuments = 5 using documents.findOne mongoose function.

Is this possible to make all those aggregations with mongodb/mongoose? I can't find anything relevant to my case.

Thank you!

Answer 1

There is an operator called $expr which allows you to use aggregation expressions within the query language. This comes in quite handy here where you would need aggregation operators in your find query.

Breaking down your query into chunks for understanding, let's take the check

subdocuments.documentId = documents.id

In mongo, this means iterating the subdocuments array searching for subdocuments which meet the above criteria and can be expressed as follows using $filter:

{
    '$filter': {
        'input': '$subdocuments',
        'cond': { '$eq': ['$$this._id', '$_id'] }
    }
}

The count bit is usually expressed with the $size operator so taking the result from the above, this query

count(select * from subdocuments where subdocuments.documentId = documents.id)

is translated into the expression

{
    '$size': {
        '$filter': {
            'input': '$subdocuments',
            'cond': { '$eq': ['$$this._id', '$_id'] }
        }
    }
}

Use the comparison operator $gt to satisfy the condition

where count(select * from subdocuments where subdocuments.documentId = documents.id) > 5

as

{
    '$gt': [
        { '$size': {
            '$filter': {
                'input': '$subdocuments',
                'cond': { '$eq': ['$$this._id', '$_id'] }
            }
        } },
        5
    ]
}

For equality, it is trivial to show with $eq

{
    '$eq': [
        { '$size': {
            '$filter': {
                'input': '$subdocuments',
                'cond': { '$eq': ['$$this._id', '$_id'] }
            }
        } },
        5
    ]
}

The above can then be used with the find() query together with $expr as

db.collection.find({
    '$expr': {
        '$eq': [
            { '$size': {
                '$filter': {
                    'input': '$subdocuments',
                    'cond': { '$eq': ['$$this._id', '$_id'] }
                }
            } },
            5
        ]
    }
})

Sorting and limiting follows using sort() and limit() cursor methods respectively

db.collection.find({
    '$expr': {
        '$eq': [
            { '$size': {
                '$filter': {
                    'input': '$subdocuments',
                    'cond': { '$eq': ['$$this._id', '$_id'] }
                }
            } },
            5
        ]
    }
}).sort({ '_id': -1 }).limit(1)

---

This query has an aggregation variant as well. With the aggregation framework, the expressions used in pipeline are similar.

The find part is done within a $match pipeline step:

db.collection.aggregate([
    { '$match': {
        '$expr': {
            '$eq': [
                { '$size': {
                    '$filter': {
                        'input': '$subdocuments',
                        'cond': { '$eq': ['$$this._id', '$_id'] }
                    }
                } },
                5
            ]
        }
    } }
])

Sorting and limiting are piped within $sort and $limit pipeline stages respectively

db.collection.aggregate([
    { '$match': {
        '$expr': {
            '$eq': [
                { '$size': {
                    '$filter': {
                        'input': '$subdocuments',
                        'cond': { '$eq': ['$$this._id', '$_id'] }
                    }
                } },
                5
            ]
        }
    } },
    { '$sort': { '_id': 1 } },
    { '$limit': 1 }
])

Answer 2

You can use $size operator to find documents with an array field has specified length:

await documents.findOne({ subdocuments: { $size: 5 } });

In your case, you want to get last document with _id in descending order so you can add sort option to findOne():

await documents.findOne({ subdocuments: { $size: 5 } }, {}, { sort: { '_id' : -1 } });

Or:

await documents.findOne({ subdocuments: { $size: 5 } }).sort({_id: -1});

To find document with subdocuments length less than 5, you can use $where operator:

await documents.findOne({$where: 'this.subdocuments.length<5'}, {}, { sort: { '_id' : -1 } });