Select and order all subdocuments from a collection

Question

I have the following documents in a collection:

{
  "thread_object": "Sed consectetur, massa id aliquam lacinia",
  "thread_comments": [
    {
      "comment_date": "11/01/2014 17:23:19",
      "comment_user": "Integer",
      "comment_content": "In suscipit enim at eleifend auctor"
    },
    {
      "comment_date": "12/01/2014 17:23:19",
      "comment_user": "Suspendisse",
      "comment_content": "Quisque facilisis magna pellentesque diam mollis"
    }
  ]
},
{
  "thread_object": "Vivamus auctor mauris augue",
  "thread_comments": []
},
{
  "thread_object": "Phasellus volutpat, sem id convallis elementum",
  "thread_comments": [
    {
      "comment_date": "10/01/2014 17:23:19",
      "comment_user": "Donec",
      "comment_content": "Suspendisse a pellentesque justo"
    }
  ]
}

How can I retrieve an ordered list of all the comments?

{
  "comment_date": "10/01/2014 17:23:19",
  "comment_user": "Donec",
  "comment_content": "Suspendisse a pellentesque justo"
},
{
  "comment_date": "11/01/2014 17:23:19",
  "comment_user": "Integer",
  "comment_content": "In suscipit enim at eleifend auctor"
},
{
  "comment_date": "12/01/2014 17:23:19",
  "comment_user": "Suspendisse",
  "comment_content": "Quisque facilisis magna pellentesque diam mollis"
},

score 0 · Answer 1 · edited May 23 '17 at 10:31

You can use the aggregation framework to unwind the thread_comments array and then sort by comment_date. However, since the date is stored as String, if you sort by comment_date, the results will NOT be sorted. You might want to convert the comment_date to Date() using javascript. Here's an example. There are other similar questions on SO too.

Assuming you have converted comment_date to Date(), a query like below will work:

db.collection.aggregate([
    {$unwind:"$thread_comments"},
    {$sort:{"thread_comments.comment_date":-1}}
])

Select and order all subdocuments from a collection

1 Answers1