CODEWITHSUNDEEP
mongodbmongodb-querypymongo
Gal Dreiman
Gal Dreiman

Reputation: 4009

Mongodb: Sort by custom expression

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.

Upvotes: 0

Views: 392

Answers (1)

J.F.
J.F.

Reputation: 15235

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

Upvotes: 1

Related Questions