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.

Seems a little vague as to what you want to sort on. Do you just want the arrays sorted or do you want to sort the documents on the latest array result? That point could be made more clear. — Neil Lunn
– Neil Lunn, Commented Dec 29, 2014 at 10:37
I want the elements named date inside the the array date sorted. — miniwolf
– miniwolf, Commented Dec 29, 2014 at 11:03
I think @Disposer actually posted a solution to do that. But as per my comments, there is a way to do this as you insert the array elements and also to sort the elements permanently. This is why you were asked exactly what it is you really want to do. — Neil Lunn
– Neil Lunn, Commented Dec 29, 2014 at 11:07
In all fairness your edit is still not really clear. Do you now just want to return simply that "date" element. Or do you want everything that matches beneath it? The now edited response from @BatScream still contains some valid points. If not entirely clear why. — Neil Lunn
– Neil Lunn, Commented Dec 29, 2014 at 11:11
As I am used to SQL queries. I am new to mongo's way of needing everything explained. Select date.date from measure where installation = 200278 and date.date = 2014-12-18 would return date and not anything else. — miniwolf
– miniwolf, Commented Dec 29, 2014 at 11:15

BatScream · Accepted Answer · 2014-12-29 11:06:13Z

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.

edited Dec 29, 2014 at 11:06

answered Dec 29, 2014 at 10:31

BatScream

19.7k4 gold badges54 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Neil Lunn Over a year ago

The approach with .aggregate() is all fine. But new users could possibly have better clarifications on their real problems. As above. +1 for the effort but I'm not sure this solves the real problem without asking.

BatScream Over a year ago

Actually i am in process of replying with why the code doesn't work as OP expects it to.

Neil Lunn Over a year ago

Actually there is no response from the OP or any further explanation of what they are doing or whether aggregation and sorting of the array post "record" is the right way to do so. Hence the comment, hence "you could have asked", hence the general. It's not an "attack" just something to consider when the overall objective seems vague (IMHO).

BatScream Over a year ago

Yes its fine Neil, i understand. Should have asked for a clarification before posting. Will do that the next time. I simply assumed what the OP was trying to ask in this case.

BatScream Over a year ago

@miniwolf - Its fine. You need to look into aggregation solutions to solve problems involving sub documents. The beginning is always a bit tough for whatever you learn.

|

miniwolf · Accepted Answer · 2014-12-29 13:14:59Z

0

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 } 
             ]
         } }

answered Dec 29, 2014 at 13:14

miniwolf

3391 gold badge4 silver badges19 bronze badges

Collectives™ on Stack Overflow

MongoDB finding specific elements and sorting

2 Answers 2

8 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related