How to retrieve objects inside an array, if matches given condition, using mongodb 3.0.0

Question

I want to project all the objects from an array, if it matches the given condition.

I have following data

{
    _id : 1,
    em : 'abc@12s.net',
    name : 'NewName',
    od : 
    [
        {
            "oid" : ObjectId("1234"),
            "ca" : ISODate("2016-05-05T13:20:10.718Z")
        },
        {
            "oid" : ObjectId("2345"),
            "ca" : ISODate("2016-05-11T13:20:10.718Z")
        },
        {
            "oid" : ObjectId("57766"),
            "ca" : ISODate("2016-05-13T13:20:10.718Z")
        }
    ]       
},
{
    _id : 2,
    em : 'ab6c@xyz.net',
    name : 'NewName2',
    od : 
    [
        {
            "oid" : ObjectId("1234"),
            "ca" : ISODate("2016-05-11T13:20:10.718Z")
        },
        {
            "oid" : ObjectId("2345"),
            "ca" : ISODate("2016-05-12T13:20:10.718Z")
        },
        {
            "oid" : ObjectId("57766"),
            "ca" : ISODate("2016-05-05T13:20:10.718Z")
        }
    ]       
}

I want to get all the objects from od array, if 'od.ca' comes between range say, if greater than 10th may and less than 15th may.

I tried using aggregate method of mongodb and I am new to this method. My query is as given below.

db.userDetail.aggregate(
        {
            $match: 
            {
                'od.ca': 
                {
                    '$gte': '10/05/2016',
                    '$lte': '15/05/2016' 
                },
                lo: { '$ne': 'd' }
            }
        },
        {
            $redact:
            {
                $cond: 
                {
                    if: 
                    {
                        $gte: [ "$$od.ca", '10/05/2016' ],
                        $lte : ["$$od.ca" , '15/05/2016']
                    },
                    then: "$$DESCEND",
                    else: "$$PRUNE"
                }
            }
        })

When I am trying to use this command, getting error :-

assert: command failed: { "errmsg" : "exception: Use of undefined variable: od", "code" : 17276, "ok" : 0 } : aggregate failed

Since I am using mongodb 3.0.0 I can not use $fiter. So I tried using $redact.

Can someone tell me what wrong I am doing? Is the query correct?

Also referred question Since I am not using 3.2 of mongodb (as I have mentioned), can not use the accepted answer of the question.

Answer 1

The following query gave me the desired result. If you are using mongodb-2.6.X up to 3.0.X can use this solution.

var object = {st : "10/05/2016", et : "13/05/2016"};
db.userDetail.aggregate(
  [ 
    {
      $match: 
      {
        "od.ca": 
        {
          '$gte': new Date(object.st),
          '$lte': new Date(object.et)
        },
        "lo" : {$ne : 'd'}
      }
	},
    {
        $project:
        {
			em: 1,
			fna : 1,
			lna : 1,
			ca :1,
			od:
			{
				"$setDifference":
				[{
					"$map":
					{
					  "input": "$od",
                      "as": "o",
                      "in":
                      {
                        "$cond":[
                          {
                            "$and":
                            [
                              { "$gte": [ "$$o.ca", new Date(object.st) ] },
                              { "$lte": [ "$$o.ca", new Date(object.et) ] },
                              { "$ne": [ "$$o.oid", ObjectID(config.pid.toString()) 
                                       ] }
                            ]
                          },
                          "$$o",false]
                      }
                    }
                },[false]
                ]
            }
        }
   },
   {$sort : {_id : 1}}
  ];
)

If you are using 3.2.X, use $filter to get the result.

Answer 2

query explanation:

1. $match - match documents for criteria - limit documents to process
2. $unwind - econstructs od array field from the input documents to output a document for each element. Each output document is the input document with the value of the array field replaced by the element.
3. $match - match documents for criteria
4. $group - this is opposite of $unwind in our case - so we are recreating results array

If you are expecting document like this:

{
    "_id" : 2,
    "od" : [{
            "oid" : 1234,
            "ca" : ISODate("2016-05-11T13:20:10.718Z")
        }, {
            "oid" : 2345,
            "ca" : ISODate("2016-05-12T13:20:10.718Z")
        }
    ]
}, {
    "_id" : 1,
    "od" : [{
            "oid" : 2345,
            "ca" : ISODate("2016-05-11T13:20:10.718Z")
        }, {
            "oid" : 57766,
            "ca" : ISODate("2016-05-13T13:20:10.718Z")
        }
    ]
}

you can use query bellow:

db.userDetail.aggregate([{
            $match : {
                "od.ca" : {
                    $lt : new Date(new Date().setDate(new Date().getDate() + 2)),
                    $gte : new Date(new Date().setDate(new Date().getDate() - 4))
                }
            }
        }, {
            $unwind : "$od"
        }, {
            $match : {
                "od.ca" : {
                    $lt : new Date(new Date().setDate(new Date().getDate() + 2)),
                    $gte : new Date(new Date().setDate(new Date().getDate() - 4))
                }
            }
        }, {
            $group : {
                _id : "$_id",
                od : {
                    $push : "$od"
                }
            }
        }
    ])