MongoDB - Can I request an associative array key?

Question

I need to save some data, which essentially is just an array of key / value pairs date / ints, where the dates will always be unique.

I would like to be able to store it as an associative array:

array( "2012-02-26" => 5, "2012-02-27" => 2, "2012-02-28" => 17, "2012-02-29" => 4 ) 

but I also need to be able to request dates (i.e. get everything where date> 2012-02-27), and therefore suspect that I will need to use the scheme more:

 array( array("date"=>"2012-02-26", "value"=>5), array("date"=>"2012-02-27", "value"=>2), array("date"=>"2012-02-28", "value"=>17), array("date"=>"2012-02-29", "value"=>4), ) 

Obviously the first is much cleaner and more concise, but can I request it the way I want, and if there are no other schemes that might be more suitable?

Answer 1

You described two methods, let me break them down.

Method # 1 - Associative Array

The key tool for querying with the "associative array" is the $exists operator. Provides information about the operator here.

Thus, you can definitely execute the query as follows:

 db.coll.find( { $exists: { 'field.2012-02-27' } } ); 

Depending on your description, you are looking for range queries that do not match the $exists operator. The associative array version is also hard to index.

Method # 2 - Array of Objects

This definitely has the best queries:

 db.coll.find( { 'field.date': { $gt: '2012-02-27' } } ); 

It can also be indexed.

 db.coll.ensureIndex( { 'field.date': 1 } ); 

However, there is a trade-off in updating. If you want to increase the value for a specific date, you must use this inconvenient $ positioning operator. This works for an array of objects, but it is not suitable for anything with further nesting.

Other problems

One of the problems with any of these methods is long-term data growth. As the size of the object expands, more disk and memory space is required. If you have an object with data for two years, the entire array of 700 elements must be in memory for you to update the data for today. This may not be a problem for your specific data, but it should be considered.

In the same vein, MongoDB queries always return a top-level object. Again, if you have an array of 700 elements, you will get all of them for each document that matches. There are ways to filter the returned fields, but they do not work for "arrays of objects."