CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
MaTya
MaTya

Reputation: 782

Get elements of an array in date range

This is an example document in my MongoDB collection:

 var user: [{
            "_id" : ObjectId("5391b11a3c0a9ac01300006d"),
            "injurydata" : {                
                "injuryinformation" : [ 
                    {
                        "status" : "current",
                        "updateddate" : ISODate("2014-06-06T12:16:20.306Z"),
                        "updatedby" : "",
                        "dateofinjury" : ISODate("2014-06-27T18:30:00.000Z"),                          
                        "_id" : ObjectId("5391b11a3c0a9ac01300006f")
                    }                   
                ]
            }
         },
         {
            "_id" : ObjectId("5391b11a3c0a9ac01300006d"),
            "injurydata" : {                
                "injuryinformation" : [ 
                    {
                        "status" : "current",
                        "updateddate" : ISODate("2014-06-06T12:16:20.306Z"),                           
                        "dateofinjury" : ISODate("2014-06-28T18:30:00.000Z"),                            
                        "_id" : ObjectId("5391b11a3c0a9ac01300006f")
                    }                   
                ]
            }
         },
         {
            "_id" : ObjectId("5391b11a3c0a9ac01300006d"),
            "injurydata" : {                
                "injuryinformation" : [ 
                    {
                        "status" : "current",                            
                        "dateofinjury" : ISODate("2014-08-10T18:30:00.000Z"),                            
                        "_id" : ObjectId("5391b11a3c0a9ac01300006f")
                    }                   
                ]
            }
         }]

From this document, I want only those injuryinformation array elements for which dateofinJURY is between 2014-06-28 and 2014-06-30, so I will only get first two elements.

I tried this query but I still get the whole array

db.user.find(
{
    $and: 
    [
        {"injury.injurydata.injuryinformation.dateofinjury": 
        {"$gte": ISODate("2014-05-21T08:00:00Z") , "$lt": ISODate("2014-06- 03T08:00:00Z")}},       
        {"_id":ObjectId("538d9a1dd173e5202a00005d")}
    ],

})

Upvotes: 2

Views: 3053

Answers (1)

Neil Lunn
Neil Lunn

Reputation: 151190

Well the _id match is fairly explicit in your statement so not only will that negate the need for the "range" match on array entries but it also negates any usage of $and which is actually implicit in MongoDB queries. The $and condition is essentially the "default" and is not required unless you are actually asking for multiple conditions on te same field. But here you are not.

Matching multiple array elements you need to use .aggregate() as the "projection" available to .find() cannot show you more than one matching entry from an array:

db.collection.aggregate([

    // Match the document but not actually the array
    { "$match": {
        "injury.injurydata.injuryinformation.dateofinjury": {
            "$gte": ISODate("2014-05-21T08:00:00Z"), 
            "$lt": ISODate("2014-06-03T08:00:00Z")
        },       
        "_id": ObjectId("538d9a1dd173e5202a00005d")
    }},

    // Unwind the arrays to "de-normalize" as documents
    { "$unwind": "$injury" },
    { "$unwind": "$injury.injurydata" }, 
    { "$unwind": "$injury.injurydata.injuryinformation" },

    // Match the actual array elements
    { "$match": {
        "injury.injurydata.injuryinformation.dateofinjury": {
            "$gte": ISODate("2014-05-21T08:00:00Z"), 
            "$lt": ISODate("2014-06-03T08:00:00Z")
        }
    }},

    // Group those back to and array, maybe?
    { "$group": {
        "_id": "$_id",
        "information": {
            "$push": "$injury.injrydata.injuryinformation"
        }
    }}
])

Or "filtering" with $map under MongoDB 2.6 and greater, looks longer but faster:

db.collection.aggregate([

    // Match the document but not actually the array
    { "$match": {
        "injury.injurydata.injuryinformation.dateofinjury": {
            "$gte": ISODate("2014-05-21T08:00:00Z"), 
            "$lt": ISODate("2014-06-03T08:00:00Z")
        },       
        "_id": ObjectId("538d9a1dd173e5202a00005d")
    }},

    // Unwind the arrays to "de-normalize" as documents
    { "$unwind": "$injury" },
    { "$unwind": "$injury.injurydata" }, 

    // Project with the "$map" filter
    { "$project": {
        "information": {
            "$setDifference": [
                "$map": {
                    "input": "$injury.injurydata.injuryinformation",
                    "as": "el",
                    "in": {
                        "$cond": [
                            {
                                "$and": [
                                    { 
                                        "$gte": [ 
                                            "$$el.dateofinjury",
                                            ISODate("2014-05-21T08:00:00Z")
                                        ]
                                    },
                                    {
                                        "$lt": [
                                            "$$el.dateofinjury",
                                            ISODate("2014-06-03T08:00:00Z")
                                        ]
                                    }
                                ]
                            }
                        ],
                        false
                    }
                },
                [false]
            ]
        }
    }}

])

The $and condition used in there is a different form that is available to aggregation framework expressions outside of $match operations that are equivalent to .find() queries.

Not sure where the "injury" top line comes from there as it is not represented in your sample, but I assume you actually have it.

Upvotes: 2

Related Questions