Check if all elements in MongoDB array match a given query

Question

Here's how my document looks:

nookstore = {
     "name": "Nook store",
     "categories": ["bookstore"],
     "working_hours": [
        {"opens": 8*60*60, "closes": 21*60*60 },
        {"opens": 8*60*60, "closes": 21*60*60 },
        {"opens": 8*60*60, "closes": 21*60*60 },
        {"opens": 8*60*60, "closes": 21*60*60 },
        {"opens": 8*60*60, "closes": 21*60*60 },
        {"opens": 8*60*60, "closes": 21*60*60 },
        {"opens": 9*60*60, "closes": 20*60*60 },
     ]
  }

  ...

kindlestore = {
     "name": "Kindle store",
     "categories": ["bookstore"],
     "working_hours": [
        {"opens": 0, "closes": 24*60*60 },
        {"opens": 0, "closes": 24*60*60 },
        {"opens": 0, "closes": 24*60*60 },
        {"opens": 0, "closes": 24*60*60 },
        {"opens": 0, "closes": 24*60*60 },
        {"opens": 0, "closes": 24*60*60 },
        {"opens": 0, "closes": 24*60*60 },
     ],
  }

I'm looking for the 24-hours stores, i.e. the stores in which every element of working_hours opens at 0 and closes at 24*60*60 (seconds after midnight).

I tried using $all and $elemMatch, but they work in cases where I need to match if at least one of the elements of an array matches a given criteria (which I don't need).

It would be most efficient if you stored that as a distinct field when the document was saved. — WiredPrairie, Oct 24 '13 at 19:37
Or, you'd need to check every array index for each condition with an `$and` ... which would look messy and may be a bit slow. If you used the aggregation framework, you could group and sum based on the opens/closes time and check for times when it was == 7. — WiredPrairie, Oct 24 '13 at 19:39

WiredPrairie · Answer 1 · 2013-10-24T20:23:31.160

While I think this would be best if you modified the schema to include the special status as another field (open24hours), you could use the aggregation framework to find matches.

db.stores.aggregate({$unwind : '$working_hours' }, 
   { $group : { 
       _id : { 
         name: '$name', 
         opens: '$working_hours.opens', 
         closes: '$working_hours.closes' }, 
      total: { $sum : 1 }
      }
  }, 
  { $match : { total : 7 } })

This unwinds the hours opened, then groups on the name, opens and closes times, thereby making a unique combination, and then totals the number of matches for each combination. If there were 7 for the total, then it matched on all 7 days. You could do further matches to only find opens at 0 and closes at 86400.

Results:

{
    "result" : [
            {
                    "_id" : {
                            "name" : "Kindle store",
                            "opens" : 0,
                            "closes" : 86400
                    },
                    "total" : 7
            }
    ],
    "ok" : 1
}

Joachim Isaksson · Answer 2 · 2013-10-24T21:19:44.153

You could do it using the aggregation framework, probably in multiple ways. This is one that just groups to find unique open/close times and matches at the end on a single open/close time that's midnight to midnight.

db.stores.aggregate(
  [ 
    { $unwind: '$working_hours' },
    { $group: { _id: '$name', times: { $addToSet: '$working_hours' } } },
    { $unwind: '$times' },
    { $group: { _id: '$_id', times: { $push: '$times' }, cnt: { $sum: 1 } } },
    { $match: { 'times.opens': 0, 'times.closes': 86400, cnt: 1 } }
  ]
)

EDIT: Another, probably better alternative is to match as early as possible to not have to group values that aren't relevant;

db.stores.aggregate(
  [
    { $unwind: '$working_hours' },
    { $match: { 'working_hours.opens': 0, 'working_hours.closes': 86400 } },
    { $group: { _id: '$name', cnt: { $sum: 1 } } },
    { $match: { 'cnt': 7 } }
  ]
)

EDIT2: It can also be done without the aggregate framework by finding all stores that don't have a non matching time;

db.stores.find({
  'working_hours': { 
    $not: { 
      $elemMatch: {
        $or:[ { 'opens': { $ne: 0 } }, { 'closes': { $ne: 86400 } } ]
      }
    }
  }
}).pretty()

Check if all elements in MongoDB array match a given query

2 Answers2