Mongodb: sort documents by array objects

问题:

I would like to return the documents in an order sorted by which holds the lowest foo.bar value (which are array objects).

I can do db.collection.find().sort({foo.0.bar: 1}), but this only matches the first element in the array - and as you can see in the exampe below would sort item 1 first (foo.0.bar = 5), wheras I am looking to return item 2 first (foo.2.bar = 4) as it has the object with the lowest value.

{
    "name": "Item 1",
    "foo": [
        {
            "bar": 5
        },
        {
            "bar": 6
        },
        {
            "bar": 7
        }
    ]
}
{
    "name": "item 2",
    "foo": [
        {
            "bar": 6
        },
        {
            "bar": 5
        },
        {
            "bar": 4
        }
    ]
}

回答1:

It seems mongo can do this.

For example, if I have the following documents:

{ a:{ b:[ {c:1}, {c:5 } ] } }
{ a:{ b:[ {c:0}, {c:12} ] } }
{ a:{ b:[ {c:4}, {c:3 } ] } }
{ a:{ b:[ {c:1}, {c:9 } ] } }

And run the following:

db.collection.find({}).sort({ "a.b.c":1 });
// produces:
{ a:{ b:[ {c:0}, {c:12} ] } }
{ a:{ b:[ {c:1}, {c:5 } ] } }
{ a:{ b:[ {c:1}, {c:9 } ] } }
{ a:{ b:[ {c:4}, {c:3 } ] } }

db.collection.find({}).sort({ "a.b.c":-1 });
// produces:
{ a:{ b:[ {c:0}, {c:12} ] } }
{ a:{ b:[ {c:1}, {c:9 } ] } }
{ a:{ b:[ {c:1}, {c:5 } ] } }
{ a:{ b:[ {c:4}, {c:3 } ] } }

As you can see, the sort by {"a.b.c":1} takes the min of all values in the array and sorts on that, whereas the sort by {"a.b.c":-1} takes the max of all the values.

回答2:

An alternative to using map/reduce, is to store the min value from the array out as a separate field in the document which you could then order on. When you add to the array, you also update this field if the new value is lower than the currently recorded minimum.

e.g. your first document would become this (note "minbar added"):

{
    "name": "Item 1",
    "minbar" : 5,
    "foo": [
        {
            "bar": 5,
        }
        {
            "bar": 6,
        }
        {
            "bar": 7,
        }
    ]
}

回答3:

You can do such a sort with aggregate command from mongo version 2.2:

    db.collection.aggregate([{$unwind: "$foo"},
    {$project: {bars:"$foo.bar"}},
    {$group: {_id:"$_id",min:{$min: "$bars"}}},
    {$sort: {min:1}}])

回答4:

There is no direct way to do this in mongo. You would need to use map/reduce to retrieve the min val in each array and then sort by that min value