Get top 100 documents based on multiple fields-CodePudding

I am trying to get the 100 documents from my DB based on the sum a few fields.

The data is similar to this:

{
    "userID": "227837830704005140",
    "shards": {
        "ancient": {
            "pulled": 410
        },
        "void": {
            "pulled": 1671
        },
        "sacred": {
            "pulled": 719
        }
    }
}

I want to sum the "pulled" number for the 3 types of shard, and then use that to determine the top 100.

I tried this in nodejs:

let top100: IShardData[] = await collection.find<IShardData>({}, {
            projection: {
                "shards.ancient.pulled": 1, "shards.void.pulled": 1, "shards.sacred.pulled": 1, orderBySumValue: { $add: ["$shards.ancient.pulled", "$shards.void.pulled", "$shards.sacred.pulled"] }
            }, sort: { orderBySumValue: 1 }, limit: 100
        }).toArray()

This connects to the DB, gets the right collection, and seems to sum up the values correctly but is not sorting them for the top 100 by the sum of the fields. I used this as a basis for my code: https://www.tutorialspoint.com/mongodb-order-by-two-fields-sum

Not sure what I need to do to make it work. Any help is appreciated.

Thanks in advance!

CodePudding user response：

Here's one way to do it using an aggregation pipeline.

db.collection.aggregate([
  {
    // create new field for the sum
    "$set": {
      "pulledSum": {
        "$sum": [
          "$shards.ancient.pulled",
          "$shards.void.pulled",
          "$shards.sacred.pulled"
        ]
      }
    }
  },
  {
    // sort on the sum
    "$sort": {
      "pulledSum": -1
    }
  },
  {
    // limit to the desired number
    "$limit": 10
  },
  {
    // don't return some fields
    "$unset": [
      "_id",
      "pulledSum"
    ]
  }
])

Try it on mongoplayground.net.

CodePudding user response：

sort should be written with a dollar sign.

$sort: { orderBySumValue: 1 }