CODEWITHSUNDEEP
mongodb
charnould
charnould

Reputation: 2907

How to find() a document when some subdocument array record is before date 'X'

I'm stuck (again) with a mongoDB query. It must be simple, but I can't find it out...

Let's say I have a collection/document like this one:

{
  _id: ObjectId("588055a656f0dc1c9327eaf9"),
  type: "house",
  timeline: [
    {
      action: "abc",
      date: 2017-01-01,
    },
    {
      action: "xyz",
      date: 2016-12-12,
    }
   ]
}

I would like to query only docs with type house and where action xyz is before - let's say - 2015... How to do so?

Obviously, I know how to query by type...

db.collection().find({ 'type': 'house' }).toArray()

I also know how to query with dot.notation

db.collection().find({ 'timeline.action': 'xyz' }).toArray()

But how to build a query where action xyz is before - let's say - 2015? Thank you very much.

Upvotes: 0

Views: 49

Answers (3)

Andy Macleod
Andy Macleod

Reputation: 3059

you have to be careful with this kind of match because of the way mongo saves data.

If you query 

{
    action: 'xyz',
    date: {$lt: '2015-01-01'}
}

but your object is saved as 

{
    date: {$lt: '2015-01-01'},
    action: 'xyz'
}

then mongo will not match due to the underlying structure of the saved data.

If you need to match on multiple elements it it recommended to use $elemMatch.

{timeline : {
    $elemMatch: {
        action: 'xyz', 
        date: {$lt: '2015-01-01'}
    }
}}

as it will match on each element, not the object as a whole.

Your total query should look something like 

{
    type: 'house'
    timeline : {
        $elemMatch: {
            action: 'xyz', 
            date: {$lt: '2015-01-01'}
        }
    }
}

Upvotes: 0

radhakrishnan
radhakrishnan

Reputation: 1459

Use $match to find type condition, and $redact for getting only matched 'timeline' objects ,

      db.collection.aggregate([{$match:{type:"house"}},{
        $redact: {
            "$cond": [{
                $and: [{
                    "$eq": [{
                            "$ifNull": ["$action", "xyz"]
                        },
                        "xyz"
                    ]
                }, {
                    "$lte": [{
                            "$ifNull": ["$date", ISODate("2015-01-01T00:00:02.015Z")]
                        },
                       ISODate("2015-01-01T00:00:02.015Z")
                    ]
                }]
            }, "$$DESCEND", "$$PRUNE"]
        }
    }]).pretty()

Upvotes: 1

s7vr
s7vr

Reputation: 75934

You can use the $elemMatch operator to match on the embedded fields. (missed that operator in the comment)

db.collection.find({'timeline': {$elemMatch:{action: 'xyz', 'date':{$lt: "2015-01-01"}}}})

More here https://docs.mongodb.com/manual/reference/operator/query/elemMatch/#array-of-embedded-documents

Upvotes: 1

Related Questions