$sort with least value inside an array mongodb

Question

I have the following documents in the database. What I would like is to be able to sort the documents by the value of one of its subdocuments that's inside an array. Is this possible to do?

{
    name: "Item A",
    subItems: [{
        name: "Subitem 1",
        value: 10
    },
    {
        name: "Subitem 2",
        value: 20
    }]
},
{
    name: "Item B",
    subItems: [{
        name: "Subitem 1",
        value: 5
    },
    {
        name: "Subitem 2",
        value: 60
    }]
}

I would like to sort the documents by the value of one of the subitems so that the expected outcome for sorting by ascending would be below (in this case sorting by descending would also give the same result because item B has both lowest and largest value items). I don't really care for the order of the subitems in this query, just the order of the parent documents.

Expected outcome:

{
    name: "Item B",
    subItems: [{
        name: "Subitem 1",
        value: 5
    },
    {
        name: "Subitem 2",
        value: 60
    }]
},
{
    name: "Item A",
    subItems: [{
        name: "Subitem 1",
        value: 10
    },
    {
        name: "Subitem 2",
        value: 20
    }]
}

I know it will have to use aggregation but no idea how to even start with this. Any help appreciated.

Saravana · Accepted Answer · 2018-12-28 03:54:30Z

1

Depends on which value you are talking about.

Scenario 1

If sorting by the array itself, it will use the min/max value if you are sorting ascending/descending. Simply do:

db.coll.find({...}).sort({"subItems.value"})

There's no way any index can support this sorting.

Scenario 2

Or, you can specify which element you want to use as sort key:

db.coll.find({...}).sort({"subItems.0.value"})

This sorting can be supported by:

db.coll.createIndex({"subItems.0.value": 1})

Scenario 3

If none of the above is what you wanted, I think you probably mean to filter by some condition and sort by that element. This is possible by using aggregation:

db.bar.aggregate([
    {$match: {"subItems.value": {$lte: 10}}}, 
    {$project: {
        subItems: {
            $filter: {
                input: "$subItems",
                as: "item",
                cond: {
                    $lte: ["$$item.value", 10]
                }
            }
        }
    }},
    {$sort: {"subItems.value": -1}}
])

The $filter operator essentially filter element by some condition. After that, it's no different than sorting by an array like we did in scenario 1.
In this query the first $match can leverage index {"subItems.value": 1} while the $project and $sort is pure CPU work.

edited Dec 28, 2018 at 3:54

Saravana

12.8k2 gold badges42 silver badges60 bronze badges

answered Dec 28, 2018 at 2:53

yaoxing

4,2332 gold badges24 silver badges33 bronze badges

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

1 Comment

Ashh Over a year ago

Non of the "Scenerio" is correct. OP needs to $sort with minimum value which is inside the array.

Ashh · Accepted Answer · 2018-12-28 06:13:12Z

0

There is simplest solution you can use here.

Use $min aggregation operator to find the lowest value inside the subItems array and then $sort by with it

db.collection.aggregate([
  { "$addFields": {
    "order": { "$min": "$subItems.value" }
  }},
  { "$sort": { "order": 1 }}
])

answered Dec 28, 2018 at 6:13

Ashh

46.6k16 gold badges111 silver badges137 bronze badges

Collectives™ on Stack Overflow

$sort with least value inside an array mongodb

2 Answers 2

Scenario 1

Scenario 2

Scenario 3

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Scenario 1

Scenario 2

Scenario 3

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related