0

Assuming we have multiple documents with the following schema:

{
        "_id" : ObjectId("55ec6108794ac3cc530041ac"),
        "product": "Product x",
        "desc": "Blah Blah Blah",
        "prices" : [
                {
                    "price": 12.3,
                    "is_price_active": false,
                },
                {
                    "price": 15.3,
                    "is_price_active": false,
                },
                {
                    "price": 15,
                    "is_price_active": true,
                }
        ]
}

Is it possible to order the result set according to active price or in the other mean is it possible to order the result based on the last price (since in my case, the active price is always the last price)?

something like this: orderBy('prices.[prices.length - 1].price', 'desc')

Thanks in Advance...

iSun
  • 1,714
  • 6
  • 28
  • 57
  • possible duplicate of [MongoDB: Sort Nested array of objects](http://stackoverflow.com/questions/12432727/mongodb-sort-nested-array-of-objects) – Vishwas Sep 07 '15 at 10:43
  • @Vishwas The problem is not nested sorting, but the problem is nested sorting based on last array item. – iSun Sep 07 '15 at 10:47
  • possible duplicate of [MongoDB sort vs aggregate $sort on array index](http://stackoverflow.com/questions/32347961/mongodb-sort-vs-aggregate-sort-on-array-index). Which actually **is** the same thing that you are asking here. Answered this just the other day. In fact, you can probably just use the syntax the OP did in their question as that works with normal projections and query sort. – Blakes Seven Sep 07 '15 at 10:52

1 Answers1

0

You can :

  • unwind prices
  • sort by is_price_active and price
  • regroup by product (reverse unwind)
  • eventually project to get the original shape of the document

This way you don't rely on the fact that the active price is always the last one ; the active price can be anywhere in the array.

Here the request : 

db.products.aggregate([
    {$unwind : "$prices"},
    {$sort : { "prices.is_price_active":-1, "prices.price" : 1 }},
    {$group : { "_id" : {"_id" : "$_id", "product" : "$product", "desc" : "$desc"}, "prices" : {$push : "$prices"} }},
    {$project : {"_id" : "$_id._id", "product" : "$_id.product", "desc" : "$_id.desc", "prices" : 1}}
]);
codename44
  • 867
  • 9
  • 19
  • Not even close. The OP is "actually asking" to sort the "documents" using the "last" value in the array. And your code uses at least one too many aggregation stages which causes overhead and costs time. There is a clear linked "duplicate" and therefore no need for your answer. Certainly not an incorrect one. Not sorting "array", but sorting "documents". – Blakes Seven Sep 07 '15 at 11:21