1

I am very new to MongoDb and experimenting to see if I can use it to store time series data.

I have inserted the following data...

{
"_id" : ObjectId("5785f186ed936527c05efa10"),
"Timestamp" : ISODate("2006-07-13T07:42:00.000Z"),
"Label1" : "Lab1",
"Attr" : "atrr1",
"Readings" : [ 
    {
        "DateTime" : ISODate("2006-07-13T07:42:06.355Z"),
        "Value" : "22"
    }, 
    {
        "DateTime" : ISODate("2006-07-13T07:42:07.355Z"),
        "Value" : "22"
    }, 
    {
        "DateTime" : ISODate("2006-07-13T07:42:08.355Z"),
        "Value" : "22"
    }, 
  ....

So, each document as the array Readings that hold DateTimes in 1 second intervals.

So, if I want to query between 2 datetimes, I am trying the following in the Robomongo query window...

db.getCollection('Timedata').find(
 {
 'Readings.DateTime':    
    { $gt: '2005-07-13 07:42:13.355Z',  $lt: '2010-07-13 07:42:13.355Z'}        
  })

However this always returns Fetched 0 record(s) in 11ms where as it should actually return them all.

My syntax must be incorrect, but I just cannot find what is wrong with it and how to search for datetimes in a nested array as I have here. Would anyone have any ideas?

Thanks in advance!

peterc
  • 6,921
  • 9
  • 65
  • 131

1 Answers1

1
db.getCollection('Timedata').find(
 {
 Readings: {
   $elemMatch: 
     {DateTime: 
        {
            $gt: ISODate('2005-07-13 07:42:13.355Z'),
            $lt: ISODate('2010-07-13 07:42:13.355Z')
        }
     }
  }   
});


db.getCollection('Timedata').aggregate([
    {
        $match:{
        Readings: {
           $elemMatch: 
             {DateTime: 
                {
                    $gt: ISODate('2005-07-13 07:42:13.355Z'),
                    $lt: ISODate('2010-07-13 07:42:13.355Z')
                }
             }
          } 
        }
    },
   {
      $project: {
         _id:1,
         Timestamp:1,
         Label1:1,
         Attr:1,
         Readings: {
            $filter: {
               input: "$Readings",
               as: "item",
               cond: { 
                   $and: [
                        {$gt: [ "$$item.DateTime", ISODate('2006-07-13 07:00:13.355Z') ]},
                        {$lt: ["$$item.DateTime", ISODate('2010-07-15 07:42:13.355Z')]}
                   ]
               }
            }
         }
      }
   }
])
Ihor Fito
  • 106
  • 1
  • 9
  • 1
    Thankyou very much, works perfectly! I shall study up on this syntax, which is quite different to the way I thought I saw in some documentation. – peterc Jul 14 '16 at 01:04
  • Actually, I think I may have jumped the gun here.The above returns the whole documents that have any Readings within the time period, including readings that fall outside of the filter. Was hoping there was a way to also have the first and the last document just have the readings included within the time period. Would this be possible with doing "manually"? – peterc Jul 14 '16 at 05:41
  • Look at updated answer. Just change the dates in fields. – Ihor Fito Jul 14 '16 at 07:47
  • Great, Thankyou! Yes now the results are have filtered readings. – peterc Jul 15 '16 at 02:08