get only searched fields in mongodb

Question

I've following documents in my mongodb collection:

     {
       "_id": ObjectId("579493bccdba841a0bf5ad1a"),
       "uniqueId": "bUbOaciS1g",
       "chat": [
         {
           "TS": ISODate("2016-07-24T10:09:00.631Z"),
           "user_ques": "hi",
           "rep_ans": "hello" 
        },
         {
           "TS": ISODate("2016-08-01T10:09:40.814Z"),
           "user_ques": "how are you?",
           "rep_ans": "i am fine"
        },
         {
           "TS": ISODate("2016-08-04T10:09:55.724Z"),
           "user_ques": "help me",
           "rep_ans": "sure"
        }
]
    }
    {
       "_id": ObjectId("579493bccdba841a0bf5ad1b"),
       "uniqueId": "xyzOaciS1g",
       "chat": [
         {
           "TS": ISODate("2016-08-09T10:09:00.631Z"),
           "user_ques": "hi",
           "rep_ans": "hello" 
        }
]
    }

what i want to do is get only fields with the specific dates and exclude others. query i tried:

db.collection.find({'chat.TS':{$gte:ISODate("2016-08-01T00:00:00.000Z"),$lte:ISODate("2016-08-10T00:00:00.000Z")}}).pretty()

result i expected:

{
       "TS": ISODate("2016-08-01T10:09:40.814Z"),
       "user_ques": "how are you?",
       "rep_ans": "i am fine"
    },
     {
       "TS": ISODate("2016-08-04T10:09:55.724Z"),
       "user_ques": "help me",
       "rep_ans": "sure"
    }
    {
       "TS": ISODate("2016-08-09T10:09:00.631Z"),
       "user_ques": "hi",
       "rep_ans": "hello" 
    }

instead what i got is expected result plus the field with date 2016-07-24. I wanted to get only result between the specified date. I'm new to mongo. How do I get the expected result?

MongoDB shell version: 2.4.9

Edit

solution provided by Prabhu and Ihor work fine for mongodb client, but I'm not able to translate the same query for pymongo. Can somebody help me with that.

score 1 · Answer 1 · answered Aug 10 '16 at 10:30

1

Use Aggregate Query as shown below. Edit the query to your required output.

db.collection.aggregate([
        {
            $match:{
            chat: {
               $elemMatch: 
                 {TS: 
                    {
                        $gt: ISODate('2016-08-01T00:00:00.000Z'),
                        $lt: ISODate('2016-08-10T00:00:00.000Z')
                    }
                 }
              } 
            }
        },
       {
          $project: {
             _id:1,             
             chat: {
                $filter: {
                   input: "$chat",
                   as: "item",
                   cond: { 
                       $and: [
                            {$gt: [ "$$item.TS", ISODate('2016-08-01T00:00:00.000Z') ]},
                            {$lt: ["$$item.TS", ISODate('2016-08-10T00:00:00.000Z')]}
                       ]
                   }
                }
             }
          }
       }
    ])

answered Aug 10 '16 at 10:30

Prabhu

96
1
10

Hi @Prabhu i'm getting: `Error: Printing Stack Trace at printStackTrace (src/mongo/shell/utils.js:37:15) at DBCollection.aggregate (src/mongo/shell/collection.js:897:9) at (shell):1:12 Wed Aug 10 16:48:20.377 aggregate failed: { "errmsg" : "exception: invalid operator '$filter'", "code" : 15999, "ok" : 0 } at src/mongo/shell/collection.js:898` i feel its due to the version of mongo i'm using, any suggestions ? – Prashant S Aug 10 '16 at 11:23
Hi Prashant, You are correct. Starting with the 3.2 release, you can use the new $filter aggregation operator to filter an array during projection, which has the benefit of including all matches, instead of just the first one. Please refer the below URL. http://stackoverflow.com/questions/3985214/retrieve-only-the-queried-element-in-an-object-array-in-mongodb-collection – Prabhu Aug 10 '16 at 11:53
hey @Prabhu, one more help, it is working fine now in mongo after i updated, but I'm not able to run the query with pymongo, can you please write the query for pymongo, would be a big help. – Prashant S Aug 10 '16 at 13:11
hi @PrashantS , sorry but I also don't have any experience with pymongo. – Prabhu Oct 24 '18 at 13:01

score 0 · Answer 2 · edited May 23 '17 at 12:15

0

I have answered for the same question about searching in array with date range. Look at MongoDb searching nested array between dateTimes using Robomongo

If it doesn't fit you, I will show you a query with your data.

edited May 23 '17 at 12:15

Community

1
1

answered Aug 10 '16 at 10:05

Ihor Fito

106
1
9

hey @Ihor, i was wondering if you help me out by telling the same query in pymongo, i tried it there but throws error. – Prashant S Aug 12 '16 at 10:16
hi, sorry but I don't have any experience with pymongo. – Ihor Fito Aug 15 '16 at 11:34

score -2 · Answer 3 · edited Aug 12 '16 at 14:48

-2

db.collection.find({
    'chat.TS':{
        $gte:ISODate("2016-08-01T00:00:00.000Z"),
        $lte:ISODate("2016-08-10T00:00:00.000Z")
    }
},
{'chat.TS':1})
.pretty()

edited Aug 12 '16 at 14:48

Vince Bowdren

8,326
3
31
56

answered Aug 10 '16 at 09:40

Meena

97
2

2

Normal Projection won't work here. This will still show the 07-24 chat object inside the chat array for the first document. – Ishan Soni Aug 10 '16 at 09:48

get only searched fields in mongodb

3 Answers3