Mongodb: Sort by custom expression

Question

How to sort results by a custom expression which I use in find?

The collection contains documents with the following attributes for example:

{
    "_id" : ObjectId("5ef1cd704b35c6d6698f2050"),
    "Name" : "TD",
    "Date" : ISODate("2021-06-23T09:37:51.976Z"),
    "A" : "19.36",
    "B" : 2.04,
}

I'm using the following find query to get the records with Date since "2022-01-01" and the ratio between A and B is lower than 0.1:

db.getCollection('my_collection').find(
    {
            "experationDate" : 
            {
                    $gte: new ISODate("2022-01-01 00:00:00.000Z")
            },
            "$expr": 
            {
                "$lte": [ 
                            { "$divide": ["$A", "$B"] },
                            0.1
                        ] 
            }
            
    })

Now, I can't find the right way to sort the results by this ratio.

You can use an aggregation query to do all these tasks. The $addFields stage can be used to calculate the derived value and the $sort will let you sort on the calculated value. — prasad_
– prasad_, Commented Nov 24, 2020 at 7:41

J.F. · Accepted Answer · 2020-11-24 08:13:53Z

1

You can use aggregate in this way:

Search the documents you want using $match, add a field named ratio and use it to sort. And finally, not shown the field using $project:

db.collection.aggregate([
  { "$match": {
      "Date": { "$gte": ISODate("2020-01-01") },
      "$expr": { "$lte": [ { "$divide": [ "$B", { "$toDouble": "$A" } ] }, 0.1 ] } }
  },
  {
    "$set": {
      "ratio": { "$divide": [ "$B", { "$toDouble": "$A" } ] }
    }
  },
  {
    "$sort": { "ratio": 1 }
  },
  {
    "$project": { "ratio": 0 }
  }
])

Example here

By te way, I've used other values to get results. Ratio between 2.04 and 19.36 is greater than 0.1. You have dividied A/B but I think you mean B/A.

By the way, this is not important, you can change values but the query will still works ok.

Also, maybe this could work better. Is the same query, but could be more efficient (maybe, I don't know) because prevent to divide each value into collection twice:

First filter by date, then add the field ratio to each document found (and in this way is not necessary divide every document). Another filter using the ratio, the sort, and not output the field.

db.collection.aggregate([
  {
    "$match": { "Date": { "$gte": ISODate("2020-01-01") } }
  },
  {
    "$set": { "ratio": { "$divide": [ "$B", { "$toDouble": "$A" } ] } }
  },
  {
    "$match": { "ratio": { "$lte": 0.1 } }
  },
  {
    "$sort": { "ratio": 1 }
  },
  {
    "$project": { "ratio": 0 }
  }
])

Example

edited Nov 24, 2020 at 8:13

answered Nov 24, 2020 at 8:00

J.F.

15.3k10 gold badges41 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Gal Dreiman Over a year ago

Why do you need to use $project here?

J.F. Over a year ago

I've added a new field ratio and I'm assuming you don't want to output the field.

Gal Dreiman Over a year ago

Got that, Thanks.

J.F. Over a year ago

You are welcome! Also, I've updated the query to not divide twice. There is only one division and only for document whose date has been matched.

Collectives™ on Stack Overflow

Mongodb: Sort by custom expression

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related