MongoDB finding specific elements and sorting

Question

This is my data in the collection:

{ "installation" : 200278 , 
      "date" : [ 
           { "date" : "2014-12-28" , 
                "values" : [ 
                      { "time" : "2014-12-28 00:15:26" , "value" : 26}, 
                      { "time" : "2014-12-28 00:30:26" , "value" : 15} , 
                      { "time" : "2014-12-28 00:45:26" , "value" : 7} , 
                      { "time" : "2014-12-28 01:00:26" , "value" : 32}
                ]
           }, 
           { "date" : "2014-12-29" , 
                "values" : [ 
                      { "time" : "2014-12-29 00:15:26" , "value" : 26}, 
                      { "time" : "2014-12-29 00:30:26" , "value" : 15} , 
                      { "time" : "2014-12-29 00:45:26" , "value" : 7} , 
                      { "time" : "2014-12-29 01:00:26" , "value" : 32}
                ]
           }
     ]
}, 
{ "installation" : 200312
    ...

And I try to query this:

db.measure.find({"installation" : 200278 , "date.date" : "2014-12-28"}, 
                {"date.date" : 1 , "_id" : 0})
                .sort({"date.date" : 1})

First of all I do not understand why "date.date" does not only return the element with "2014-12-28". In addition maybe I get sorting wrong but not matter my sign on the 1 it still returns the same order.

Result:

{ "date" : [ { "date" : "2014-12-28" }, { "date" : "2014-12-29" } ] }

Clarification

What I want my query to return is only the element:

{ "date" : "2014-12-28" }

The sorting problem is an extension of my hacking on the Mongo api. And a wondering of how this querying works.

Answer 1

A beautified solution for completeness with help from our own @BatScream

db.measure.aggregate([ {$match:{ "installation" : 200278}}, 
                       {$unwind:"$date"}, 
                       {$match:{"date.date":"2014-12-28"}}, 
                       {$project:{"_id" : 0,"date.date":1, "date.values":1}} 
                    ])

Result:

{ date" : { "date" : "2014-12-28", "values" : [ 
             { "time" : "2014-12-28 00:15:26", "value" : 26 }, 
             { "time" : "2014-12-28 00:30:26", "value" : 15 }, 
             { "time" : "2014-12-28 00:45:26", "value" : 7 }, 
             { "time" : "2014-12-28 01:00:26", "value" : 32 } 
             ]
         } }

Answer 2

You need to aggregate the result.

When you perform a sort() chained with a find(), the sort applies to the root documents that were filtered by the find() query, and not the array sub documents.

In order to sort array elements, you need to use the aggregation pipeline as below:

db.measure.aggregate([
{$match:{"installation" : 200278,"date.date" : "2014-12-28"}},
{$unwind:"$date"},
{$sort:{"date.date":-1}},
{$group:{"_id":"$id","installation":{$first:"$installation"},"date":{$push:"$date"}}},
{$project:{"_id" : 0,"installation":1,"date.date":1}}
])

First of all I do not understand why "date.date" does not only return the element with "2014-12-28".

To get only the first array element that matches the query, you need to use the $(positional operator).

db.measure.find({"installation" : 200278 , 
                 "date.date" : "2014-12-28"}, 
                 {"date.$" : 1,"_id":0})

If you wish to keep the date array always sorted by date, you could ensure that while making updates to the array, by making use of the $each and $sort update operators.

db.measures.update({"installation":200278},
            {$push:{
                    date:{
                          $each:[{date object},
                                 {date object},...],
                          $sort:{"date":-1}, // sort the date array by `date` 
                                             // field during updates
                         }
            }}) 

So a find query would always return a sorted array of date documents for each installation.