CODEWITHSUNDEEP
mongodbmongodb-query
Wild Goat
Wild Goat

Reputation: 3579

Mongo db pulling part of the document

I've a document which has another nested document in it represent a changes (logging). Each change document has a timestamp, field, old and new values. Basically as you can see this document would grow a lot, and I really don't want to get all changes but only a recent one.

What I want to do is make a query and get only those changes which falls in between two time stamps. I am not interested in any other information in document, so I dont want to pull it, just recent changes. 

    {
          .......
        "adwordsChanges":[
          {
             "timestamp":NumberLong("1400162491325"),
             "field":"syncState",
             "old":null,
             "new":"OK"
          },
          {
             "timestamp":NumberLong("1400162491325"),
             "field":"keywordId",
             "old":null,
             "new":NumberLong("23918779329")
          },
          {
             "timestamp":NumberLong("1400162491325"),
             "field":"adGroupId",
             "old":null,
             "new":NumberLong("16972286472")
          }
       ]
    }

This is what I've tried! 

db.keywords.find(
{
    $and :[{"_id" : ObjectId("5374c7a7ac58b0d3b5e970fa")}, {"adwordsChanges.field" : "keywordId"}, {"adwordsChanges.timestamp" : {$gte:NumberLong("11111111"), $lte:NumberLong(99999999999999) }}]
})

Running Andrei's query I am getting compilation error:

assert: command failed: {
        "errmsg" : "exception: The top-level _id field is the only field currently supported for exclusion",
        "code" : 16406,
        "ok" : 0
} : aggregate failed
Error: command failed: {
        "errmsg" : "exception: The top-level _id field is the only field currently supported for exclusion",
        "code" : 16406,
        "ok" : 0
} : aggregate failed
    at Error (<anonymous>)
    at doassert (src/mongo/shell/assert.js:11:14)
    at Function.assert.commandWorked (src/mongo/shell/assert.js:244:5)
    at DBCollection.aggregate (src/mongo/shell/collection.js:1149:12)
    at (shell):1:13
2014-05-27T15:23:54.945+0100 Error: command failed: {
        "errmsg" : "exception: The top-level _id field is the only field currently supported for exclusion",
        "code" : 16406,
        "ok" : 0

Thanks for any help!

Upvotes: 0

Views: 2235

Answers (1)

Andrei Beziazychnyi
Andrei Beziazychnyi

Reputation: 2917

You could use aggregation framework to filter subdocuments, like this:

db.keywords.aggregate({$unwind:"$adwordsChanges"},
                      {$match:{"adwordsChanges.timestamp" : 
                                      {$gte:1400162491325, $lte:23918779329},
                               "adwordsChanges.field" : "keywordId"}},
                      {$project:{_id:0,
                                 timestamp:"$adwordsChanges.timestamp",   
                                 field:"$adwordsChanges.field", 
                                 old:"$adwordsChanges.old",   
                                 new:"$adwordsChanges.new"   
                                 }});

Explanation of difference between project and group

Initially I thought original document should be returned and group was used to restore original document structure, i.e in this case groupis opposite operation to unwind. After clarification it became clear that only subdocuments were needed, then I replaced group operation with project, so that subdocuments were projected to root level.

Upvotes: 1

Related Questions