Query arrays within array in MongoDB; update if exists, insert if empty

Question

I would like to alter a document in MongoDB. Below is an example extract of the relevant document structure:

{ values : { M1 : [[1395964800, 0.083434], ... , [1395964860, 0.043424]] } }

Firstly, key M1 contains an array of arrays. I must search over the [0] item in the nested arrays (epoch time, e.g. 1395964800, 1395964860). How do I find a document by [0] nested array value? For example, I have tried searching:

db.myCollection.find({ "values" : { "M1" : [1395964800, 0.083434] } }).limit(1);

Admittedly, I expected this approach would search by both the [0] nested array value && the [1] nested array value. Even still, this does not work, and returns no results (but does not error).

Secondly, if this [0] array value already exists (epoch time) I would like to update/overwrite the [1] element in that nested arrays (data value). If the [0] array value does not exist within the M1 array, I would like to add it (and an accompanying data value) to the M1 array.

I expect I would use something similar to this:

db.collection("myCollection").findAndModify(
   { ........ }, // query
   { }, // sort
   { $set : { ... : ... }}, // set
   { upsert: true }, // insert if does not exist
   function (err, result) {
      (err) throw err
   }
);

Questions

1. How to search as described
2. How to update as described

Answer 1

The below code is the solution I created to solve my problem. In particular, the following threads helped me:

• MongoDB nested array query
• Mongodb query on nested array elements

To be clear, in my situation the unixTime value is unique both within the array itself, and across the nested arrays. I hope that someone else finds this helpful.

var unixTime = 1395964800; // e.g. This can be programmatically changed
var _query = {'values.M1' : { '$elemMatch' : { '$in' : [unixTime] } } } ; // This allows us to search within the nested arrays for a specific value (in any position).

db.collection("myCollection").findAndModify(
    _query, // query
    { start_timestamp : -1 }, // sort
    { $set : { 'values.M1.$' : [unixTime, value] }}, // set with $, which is crucual because it uses the previously found/matched array item
    { upsert: true}, // insert if does not exist
    function (err, result) {
        if (err) console.log(error);
        console.log('results',results);
    }
);

Answer 2

You have anaonymous nested arrays here which is not a very good format for dealing with data in MongoDB. Apart from the problem of selection, matching an item to update is a real problem as you are limited to the first index match *only when trying to actually match a position.

See the positional $ operator documentation.

To work around this you actually need a schema that will suit, such as this:

{ 
    values : { 
        M1 : [
            { "time": 1395964800, "value": 0.083434 },
            { "time": 1395964860, "value": 0.043424 } 
        ]
    }
}

With that structure, then you can search on the "time" values like this:

db.collection.find(
    { "values.M1.time": 1395964860 },
    { "values.M1.$": 1 }
)

And that will project the "matched" element of the array in the document where it was found.

For updates you get the same use of a positional operator:

db.collection.update(
   { "values.M1.time": 1395964860 },
   { "$set": { "values.M1.$.value": 0.055000 } }
)

But with the current nested form you have you cannot any better than explicit matching:

db.collection.find({ "values.M1.0.0": 1395964800 })

And you would need to know the index values for what you are trying to match, which defeats the purpose of storing it.

Change your schema and it will be a much more flexible solution.