CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
ovntatar
ovntatar

Reputation: 416

Querying for a Date Range

I have the following data schema:

  {
      "Address" : "Test1",
      "City" : "London",
      "Country" : "UK",
      "Currency" : "",
      "Price_History" : {
         "2014-07-04T02:42:58" : [
            {
               "value1" : 98,
               "value2" : 98,
               "value3" : 98
            }
         ],
         "2014-07-04T03:50:50" : [
            {
                "value1" : 91,
               "value2" : 92,
               "value3" : 93
            }
         ]

      },
      "Location" : [
         9.3435,
         52.1014
      ],
      "Postal_code" : "xxx"
   }

how could generate a query in mongodb to search for all results between "2014-07-04T02:42:58" and "2014-07-04T03:50:50" or how could generate a query to select only results with values from 91 till 93 without to know the date ?

thanks

Upvotes: 0

Views: 70

Answers (1)

Neil Lunn
Neil Lunn

Reputation: 151112

Not a really good way to model this. A better example would be as follows:

  {
      "Address" : "Test1",
      "City" : "London",
      "Country" : "UK",
      "Currency" : "",
      "Price_History" : [
          { "dateEnrty": 1, "date": ISODate("2014-07-04T02:42:58Z"), "value": 98 },
          { "dateEntry": 2, "date": ISODate("2014-07-04T02:42:58Z"), "value": 98 },
          { "dateEntry": 3, "date": ISODate("2014-07-04T02:42:58Z"), "value": 98 },
          { "dateEntry": 1, "date": ISODate("2014-07-04T03:50:50Z"), "value": 91 },
          { "dateEntry": 2, "date": ISODate("2014-07-04T03:50:50Z"), "value": 92 },
          { "dateEntry": 3, "date": ISODate("2014-07-04T03:50:50Z"), "value": 93 },
      ],
      "Location" : [
         9.3435,
         52.1014
      ],
      "Postal_code" : "xxx"
   }

Or something along those lines that does not utilize the path dependency. Your query here would be relatively simple, but also considering that MongodDB searches documents and not arrays for something like this. But you can dissect with the aggregation framework:

db.collection.aggregate([

    // Still match first to reduce the possible documents
    { "$match": {
        "Price_History": {
            "$elemMatch": {
                "date": { 
                    "$gte": ISODate("2014-07-04T02:42:58Z"),
                    "$lte": ISODate("2014-07-04T03:50:50Z")
                },
                "value": 98
            }
        }
    }},

    // Unwind to "de-normalize"
    { "$unwind": "$Price_History" },

    // Match this time to "filter" the array which is now documents
    { "$match": {
        "Price_History.date": {
            "$gte": ISODate("2014-07-04T02:42:58Z"),
            "$lte": ISODate("2014-07-04T03:50:50Z")
        },
        "Price_Hisotry.value": 98
    }},

    // Now group back each document with the matches
    { "$group": {
        "_id": "$_id",
        "Address": { "$first": "$Address" },
        "City": { "$first": "$City" },
        "Country": { "$first": "$Country" },
        "Currency": { "$first": "$Currency" },
        "Price_History": { "$push": "$Price_History" },
        "Location": { "$first": "$Location" },
        "Postal_Code": { "$first": "$Postal_Code" }
    }}
])

Or otherwise better off hanging the "normalization" and just go for discrete documents that you can simply process via a standard .find(). Must faster and simpler.

  {
      "Address" : "Test1",
      "City" : "London",
      "Country" : "UK",
      "Currency" : "",
      "date": ISODate("2014-07-04T02:42:58Z"),          
      "value": 98
  }

Etc. So then just query:

db.collection.find({
    "date": {
       "$gte": ISODate("2014-07-04T02:42:58Z"),
       "$lte": ISODate("2014-07-04T03:50:50Z")
    },
    "value": 98
})

I would really go with that as a "de-normalized" "Price History" collection as it is much more efficient and basically what the aggregation statement is emulating.

The query you ask for is possible using something that evaluates JavaScript like MongoDB mapReduce, but as I have already said, it will need to scan the entire collection without any index assistance, and that is bad.

Take your case to the boss to re-model and earn your bonus now.

Upvotes: 1

Related Questions