Question 264278 in Mongodb Question posted in
The official documentation can be found here.

Mongodb – Why is array length 0 considered greatest number in sorting in Mongoose

I have a collection with array field:

const collectionSchema = new mongoose.Schema({
  title: {
    type: String,
    unique: true,
    trim: true,
    required: true,
    index: true,
  },
  description: {
    type: String,
    trim: true,
    required: true,
  },
  topic: {
    type: mongoose.Schema.Types.ObjectId,
    ref: "Topic",
    required: true,
  },
  author: {
    type: mongoose.Schema.Types.ObjectId,
    required: true,
    ref: "User",
  },,
  items: [{ type: mongoose.Schema.Types.ObjectId, ref: "Item" }],
});

I need to get top 5 collections with most items. For this I am using the following code:

const collections = await Collection.find({}).sort({ items: 1 }).limit(5);

It’s working correctly except for document with items length equal to 0. Mongoose is returning document with empty array before document with highest items length

result screenshot

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    The Mongoose version of the accepted answer:

    Collection.aggregate()
      .addFields({
        arrLength: {
          $size: "$arr",
        },
      })
      .sort({ arrLength: -1 });

  2. 0

    As we were discussing in the comments, the database is currently not doing what you think. The fact that the 4 sample documents happen to seemingly be coming out in an order based on array length is just a coincidence.

    Given the following documents:

    { _id: 0, arr: [ 3 ] }
{ _id: 3, arr: [ 6, 7, 8 ] }
{ _id: 4, arr: [ 1, 5 ] }

    The query .find({}).sort({ items: 1 }) will return the following:

    [
  { _id: 4, arr: [ 1, 5 ] }
  { _id: 0, arr: [ 3 ] }
  { _id: 3, arr: [ 6, 7, 8 ] }
]

    You can see that (with other sample documents) in this playground example. Clearly the sorting is not being done based on the length of the array.

    As @jQueeny linked to and what I was attempting to refer to was injecting a field during the query. In aggregation that could look like the following (again using arr as the name of the field holding the array):

    db.collection.aggregate([
  {
    "$addFields": {
      "arrLength": {
        $size: "$arr"
      }
    }
  },
  {
    $sort: {
      arrLength: -1
    }
  }
])

    This yields the result in the expected order as can be seen in this playground example.

    This is different than virtual fields in Mongoose (hence why it is possible). But doing this at runtime forces the aggregation to do a lot more work. If the size of the matching results being sorted is large and/or this operation will be run frequently, then it might make sense for the application to maintain this field directly when the document (in particular the array) is written to. Doing so could allow the sort to be satisfied by an index which would be much more efficient.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search