Mongodb and sorting sub array

Question

Not sure if this can be done, so thought I would ask.

I have the following mongodb/s

{
 "store":"abc",
 "offers":[{
    "spend":"100.00",
    "cashback":"10.00",
    "percentage":"0.10"
  },{
    "spend":"50.00",
    "cashback":"5.00",
    "percentage":"0.10"
  }]
}

and

 {
     "store":def",
     "offers":[{
        "spend":"50.00",
        "cashback":"2.50",
        "percentage":"0.05"
      },{
        "spend":"20.00",
        "cashback":"1.00",
        "percentage":"0.05"
      }]
    }

and

 {
         "store":ghi",
         "offers":[{
            "spend":"50.00",
            "cashback":"5.00",
            "percentage":"0.10"
          },{
            "spend":"20.00",
            "cashback":"2.00",
            "percentage":"0.10"
          }]
        }

the sort needs to be by percentage.

I am not sure if I would have to use usort of another PHP function to do it, or if Mongodb is smart enough to do what I want to do.

Answer 1

Given your data structure of arrays within documents, I don't think it makes sense to do this sort in MongoDB -- Mongo will be returning entire documents (not arrays).

If you are trying to compare offers it would probably make more sense to have a separate collection instead of an embedded array. For example, you could then find offers matching a cashback of at least $5 sorted by spend or percentage discount.

If you are just trying to order the offers within a single document, you could do this in PHP with a usort().

Answer 2

Amazingly, yes, mongodb can do this:

// Sort ascending, by minimum percentage value in the docs' offers array.
db.collection.find({}).sort({ 'offers.percentage': 1 });

// Sort descending, by maximum percentage value in the docs' offers array.
db.collection.find({}).sort({ 'offers.percentage': -1 });