MongoDB custom sorting

Question

i have a collection of records as follows

{
"_id":417,
"ptime":ISODate("2013-11-26T11:18:42.961Z"),
"p":{

    "type":"1",
    "txt":"test message"
},  

"users":[
    {
        "uid":"52872ed59542f",
        "pt":ISODate("2013-11-26T11:18:42.961Z")
    },
    {
        "uid":"524eb460986e4",
        "pt":ISODate("2013-11-26T11:18:42.961Z")
    },
    {
        "uid":"524179060781e",
        "pt":ISODate("2013-11-27T12:48:35Z")
    }
],

},

{
"_id":418,

"ptime":ISODate("2013-11-25T11:18:42.961Z"),
"p":{

    "type":"1",
    "txt":"test message 2"
},  

"users":[
    {
        "uid":"524eb460986e4",
        "pt":ISODate("2013-11-23T11:18:42.961Z")
    },
    {
        "uid":"52872ed59542f",
        "pt":ISODate("2013-11-24T11:18:42.961Z")
    },

    {
        "uid":"524179060781e",
        "pt":ISODate("2013-11-22T12:48:35Z")
    }
],

}

How to sort the above records with descending order of ptime and pt where users uid ="52872ed59542f" ?

Answer 1

If you want to do such a sort, you probably want to store your data in a different way. MongoDB in generally is not near as good with manipulating nested documents as top level fields. In your case, I would recommend splitting out ptime, pt and uid into their own collection:

messages

{
    "_id":417,
    "ptime":ISODate("2013-11-26T11:18:42.961Z"),
    "type":"1",
    "txt":"test message"
},  

users

{
    "id":417,
    "ptime":ISODate("2013-11-26T11:18:42.961Z"),
    "uid":"52872ed59542f",
    "pt":ISODate("2013-11-26T11:18:42.961Z")
},
{
    "id":417,
    "ptime":ISODate("2013-11-26T11:18:42.961Z"),
    "uid":"524eb460986e4",
    "pt":ISODate("2013-11-26T11:18:42.961Z")
},
{
    "id":417,
    "ptime":ISODate("2013-11-26T11:18:42.961Z"),
    "uid":"524179060781e",
    "pt":ISODate("2013-11-27T12:48:35Z")
}

You can then set an index on the users collection for uid, ptime and pt.

You will need to do two queries to also get the text messages themselves though.

Answer 2

db.yourcollection.find(
{
  users:{
     $elemMatch:{uid:"52872ed59542f"}
  }
}).sort({ptime:-1})

But you will have problems with order by pt field. You should use Aggregation Framework to project data or use Derick's approach.

Answer 3

You can use the Aggregation Framework to sort by first ptime and then users.pt field as follows.

db.users.aggregate(
  {$sort : {'ptime' : 1}},
  {$unwind : "$users"},
  {$match: {"users.uid" : "52872ed59542f"}},
  {$sort : {'users.pt' : 1}},
  {$group : {_id : {id : "$_id", "ptime" : "$ptime", "p" : "$p"}, users : {$push : "$users"}}},
  {$group : {_id : "$_id.id", "ptime" : {$first : "$_id.ptime"}, "p" : {$first : "$_id.p"}, users : {$push : "$users"}}}
);