mongodb - How to find out key-value which matches to given value for a week using mongo? -

i have following collection structure -

    [{ "runtime":1417510501850, "vms":[{     "name":"a",     "state":"on",    },{     "name":"b",     "state":"off",    }]  },  { "runtime":1417510484000, "vms":[{     "name":"a",     "state":"on",  }, {   "name":"b",   "state":"off",  }]  },{ "runtime":1417510184000, "vms":[{     "name":"a",     "state":"off",  }, {   "name":"b",   "state":"off",  }]  },{ "runtime":1417509884000, "vms":[{     "name":"a",     "state":"on",  }, {   "name":"b",   "state":"off",  }]  },{ "runtime":1416905084000, "vms":[{     "name":"a",     "state":"on",  }, {   "name":"b",   "state":"off",  }]  } ] 

the difference between these 2 documents 5 minutes represented 'runtime'. have many such documents.

i want find names state off week. condition state should off through out week (should not have single value 'on' key state).

e.g. in above data, if name 'b' off 1 week (by considering 1417510501850 current timestamp), expected output -

{  "name":"b",  "state":"off" } 

currently doing following-

1) find documents state 'off' greater 1 week using (currenttimestamp- 60*60*24*7) 2) apply loop result find name , check state.

can above output??

i suppose query should this

db.yourcollection.aggregate([{$unwind: "$vms"}, //unwind convenience {$match: {"vms.state": {$eq: "off"}, runtime: {$lt: date.now() - 7*24*60*60*1000}}}, //filter {$project: {name: "$vms.name", state: "$vms.state"}}]) //projection 

update

this corrected query docs didn't have "on" status week. bit more difficult, see comments

db.yourcollection.aggregate([{$unwind: "$vms"}, //unwind convenience {$match: {runtime: {$lt: date.now() - 7*24*60*60*1000}}}, //filter period  {$project: {_id: "$vms.name", state: "$vms.state"}}, //projection docs {_id: "a", state: "on"} {$group: {_id: "$_id", states: {$push: "$state"}}}, //group id see states in array {$match: {states: {$eq: "off", $ne: "on"}}}, //take docs have state "off" , not have state "on" {$project: {_id: "$_id", state: {$literal: "off"}}}]) //and convert required output 

to understand query idea add 1 one pipe aggregate function , check result. hope helps.