CODEWITHSUNDEEP
mongodb
Bernd Matzner
Bernd Matzner

Reputation: 1

mongodb sort by match in field

I'm look to sort results based on in which field matches. Given I have a collection with documents like this:

{
   "foo": "orange",
   "bar": "apple",
   "baz": "pear",
}

{
   "foo": "kiwi",
   "bar": "orange",
   "baz": "banana",
}

How can I sort a result for documents matching "orange" in either foo, bar, or baz by the field in which they match, i.e. all documents matching on field foo should appear in the result before documents in field bar etc.?

Thanks!

Upvotes: 0

Views: 1679

Answers (2)

ronasta
ronasta

Reputation: 2569

You want a list of documents in the following order:

  • where foo = "orange"
  • where bar = "orange"
  • where baz = "orange"

This can not be done with a single find().sort() command, because there is no way to sort by the key (name) of a field, only by it's content.

It is possible, though, with an aggregate():

> db.xx.find()
{ "_id" : 1, "bar" : "apple", "baz" : "pear", "foo" : "orange" }
{ "_id" : 2, "foo" : "banana", "bar" : "apple", "baz" : "orange" }
{ "_id" : 3, "foo" : "banana", "bar" : "orange", "baz" : "pear" }
{ "_id" : 4, "foo" : "banana", "bar" : "apple", "baz" : "pear" }
{ "_id" : 5, "foo" : "orange", "bar" : "apple", "baz" : "pear" }
>     db.xx.aggregate([
...         { $match: { $or: [ { foo: "orange" }, { bar: "orange" }, { baz: "orange" } ] } },
...         { $project: { "_id": 1,
...                       "which": { "$cond": [{ "$eq": [ "$foo", "orange" ]}, "01foo", 
...                                { "$cond": [{ "$eq": [ "$bar", "orange" ]}, "02bar", "03baz" ] }
...                       ] }
...         } },
...         { $group: { _id: { which: "$which", _id: "$_id" } } },        
...         { $sort: { "_id.which": 1, "_id._id": 1 } },
...         { $project: { which: { $substr: ["$_id.which", 2, -1] }, _id: "$_id._id" } },        
...     ]);
{
    "result" : [
        {
            "_id" : 1,
            "which" : "foo"
        },
        {
            "_id" : 5,
            "which" : "foo"
        },
        {
            "_id" : 3,
            "which" : "bar"
        },
        {
            "_id" : 2,
            "which" : "baz"
        }
    ],
    "ok" : 1
}

You're right, if you think that is aggregate is too complicated. It would be easier if your data were organized differently, something like

{ type: "foo", value: "orange" }

and have the type names sortable - like "ba1","ba2","ba3" instead of "foo","bar","baz"

For more information on aggregate, see http://docs.mongodb.org/manual/reference/aggregation and http://docs.mongodb.org/manual/tutorial/aggregation-examples/

Upvotes: 1

Sachin
Sachin

Reputation: 3554

Try below query: 

 db.test.find({$or:[{'foo':"orange"},{'bar':"orange"}]}).sort({'foo':-1,'bar':-1})

Upvotes: 0

Related Questions