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" ?

Derick · Accepted Answer · 2013-11-27 15:02:20Z

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.

Parvin Gasimzade · Accepted Answer · 2013-11-27 16:24:41Z

0

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"}}}
);

edited Nov 27, 2013 at 16:24

answered Nov 27, 2013 at 15:54

Parvin Gasimzade

26.2k8 gold badges58 silver badges83 bronze badges

2 Comments

Derick Over a year ago

You don't need the $elemMatch here.

Derick Over a year ago

You do need the $match though!

rubenfa · Accepted Answer · 2013-11-27 15:08:31Z

0

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.

answered Nov 27, 2013 at 15:08

rubenfa

8511 gold badge7 silver badges24 bronze badges

Collectives™ on Stack Overflow

MongoDB custom sorting

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related