'How to sort documents based on length of an Array field

In my small ExpressJS app, I have a Question model which was defined like that

var mongoose = require('mongoose'),
    Schema   = mongoose.Schema;

/**
 * Question Schema
 */
var Question = new Schema({
  title: {
    type: String,
    default: '',
    trim: true,
    required: 'Title cannot be blank'
  },
  content: {
    type: String,
    default: '',
    trim: true
  },
  created: {
    type: Date,
    default: Date.now
  },
  updated: {
    type: Date,
    default: Date.now
  },
  author: {
    type: Schema.ObjectId,
    ref: 'User',
    require: true
  },
  answers : [{
    type: Schema.ObjectId,
    ref: 'Answer'
  }]
});

module.exports = mongoose.model('Question', Question);

And I want to get list popular questions based on the answer numbers. The query I used to perform my purpose

Question.find()
  .sort({'answers.length': -1})
  .limit(5)
  .exec(function(err, data) {
    if (err) return next(err);
    return res.status(200).send(data);
  });

But I don't get anything. Do you have any solutions?

node.jsmongodbmongodb-queryaggregation-framework


Solution 1:[1]

What you seem to mean here is that you want to "sort" your results based on the "length" of the "answers" array, rather than a "property" called "length" as your syntax implies. For the record, that syntax would be impossible here as your model is "referenced", meaning the only data present within the array field in the documents of this collection is the ObjectId values of those referenced documents.

But you can do this using the .aggregate() method and the $size operator: 

Question.aggregate(
    [
        { "$project": {
            "title": 1,
            "content": 1,
            "created": 1,
            "updated": 1,
            "author": 1,
            "answers": 1,
            "length": { "$size": "$answers" }
        }},
        { "$sort": { "length": -1 } },
        { "$limit": 5 }
    ],
    function(err,results) {
        // results in here
    }
)

An aggregation pipeline works in stages. First, there is a $project for the fields in the results, where you use $size to return the length of the specified array.

Now there is a field with the "length", you follow the stages with $sort and $limit which are applied as their own stages within an aggregation pipeline.

A better approach would be to alway maintain the length property of your "answers" array within the document. This makes it easy to sort and query on without other operations. Maintaining this is simple using the $inc operator as you $push or $pull items from the array:

Question.findByIdAndUpdate(id,
    {
        "$push": { "answers": answerId },
        "$inc": { "answerLength": 1 } 
    },
    function(err,doc) {

    }
)

Or the reverse when removing:

Question.findByIdAndUpdate(id,
    {
        "$pull": { "answers": answerId },
        "$inc": { "answerLength": -1 } 
    },
    function(err,doc) {

    }
)

Even if you are not using the atomic operators, then the same principles apply where you update the "length" as you go along. Then querying with a sort is simple:

Question.find().sort({ "answerLength": -1 }).limit(5).exec(function(err,result) {

});

As the property is already there in the document.

So either do it with .aggregate() with no changes to your data, or change your data to alway include the length as a property and your queries will be very fast.

Solution 2:[2]

You can also use :

db.question.find().sort({"answers":-1}).limit(5).pretty();

Solution 3:[3]

New in version 3.4. is the .addFields, which really simplifies this call:

const sortBy = "answers"
const orderBy = 1 | -1 | 'asc' | 'desc' | 'ascending' | 'descending'
const questions = await Questions.aggregate()
          .addFields({"length": {"$size": `$${sortBy}`}}) //adds a new field, to the existing ones (incl. _id)
          .sort({"length": orderBy })
return res.json(questions)

Note: I use the call in a module.exports{} async function. Therefore, the await and the return part is necceassarry. But addFields works similarly with function(err, result).

Solution 4:[4]

"answers.length" will not work unless length was a property of answers It's enough to write the array property name

Question.find({}).sort({ "answers": -1 }).limit(5)

+1 for smallest to largest -1 for largest to smallest

You can also add more than one field to handle length ties and always guarantee the same result, also can sort dates

.sort({ "answers": -1, "created": 1 })

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Dominic Tancredi
Solution 2 Nitesh singh
Solution 3 Olivér Palotás
Solution 4 leflores-fisi

Related Questions