Mongodb find inside sub array

Question

I have a document that's setup like this:

{
  _id : ObjectId(),
  info : [ 
        [ 
            1399583281000, 
            20.13
        ], 
        [ 
            1399583282000, 
            20.13
        ], 
        [ 
            1399583283000, 
            20.13
        ], 
        [ 
            1399583285000, 
            20.13
        ], 
        [ 
            1399583286000, 
            20.13
        ]
    ]
}

This data could be spread across multiple documents. In general, each document contains data in the info for 59 periods (seconds).

What I would like to do is get all of the info data where the timestamp is greater than a specific time.

Any ideas how I would go about doing this?

Thank you

EDIT:

So, I've found that this seems to return all of the documents:

db.infos.find({
   info:{
      $elemMatch:{
         0:{
            $gt:1399583306000
         }
      }
   }
})

But maybe I need to use this in an aggregate query? so that it will return just all the values?

score 9 · Accepted Answer · answered May 08 '14 at 23:42

Your on the right track, but there are a few things to note here, aside from the part that nested arrays ( and especially with anonymous keys) are not exactly a great way to store things, but as long as you consistently know the position then that should be reasonably okay.

There is a distinct difference between matching documents and matching "elements of an array". Though your current value would actually not match (your search value is not within the bounds of the document), if the value actually was valid your query correctly matches the "document" here, which contains a matching element in the array.

The "document" contains all of the array elements, even those that do not match, but the condition says the "document" does match, so it is returned. If you just want the matching "elements" then use .aggregate() instead:

    db.infos.aggregate([
        // Still match the document
        { "$match": { 
            "info": { 
                "$elemMatch": { "0": {"$gte": 1399583285000} }
            }
        }},

        // unwind the array for the matched documents
        { "$unwind": "$info" },

        // Match only the elements
        { "$match": { "info.0": { "$gte": 1399583285000 } } },

        // Group back to the original form if you want
        { "$group": {
            "_id": "$_id",
            "info": { "$push": "$info" }
        }}

    ])

And that returns just the elements that matched the condition:

{
    "_id" : ObjectId("536c1145e99dc11e65ed07ce"),
    "info" : [
            [
                    1399583285000,
                    20.13
            ],
            [
                    1399583286000,
                    20.13
            ]
    ]
}

Or course if you only ever expected one element to match, then you could simply use projection with .find()**:

db.infos.find(
    {
       "info":{
          "$elemMatch":{
             "0": {
                "$gt": 1399583285000
             }
          }
       }
    },
    {
        "info.$": 1
    }
)

But with a term like $gt you are likely to get multiple hits within a document so the aggregate approach is going to be safer considering that the positional $ operator is only going to return the first match.

This is great! One thing I found is it separates by `_id`, it's not one big array containing arrays. — dzm, May 09 '14 at 00:03
@dave The "separation" is by the value you use in the `_id` field of the `$group` statement, so it is whatever you want it to be or `null` for everything in the collection. But it seemed to me that you were after filtering this per document. Use as you will. — Neil Lunn, May 09 '14 at 01:30
Any idea how I would add a sort to the `"info.0"` to be desc? I've tried `{ "$sort": { "info.0" : -1 } }` after the group, but no luck. Thank you — dzm, May 09 '14 at 14:42

Mongodb find inside sub array

1 Answers1

Linked