Filtering an array, in a subdocument, in an array, in a document in Mongoose/MongoDB

Question

Using Mongoose (or even just MongoDB queries), I'd like to return results for a query that matches all the documents where both:

a) the "units" field (an array of subdocuments) contains a subdocument with a "unit" field, which itself contains a subdocument with an "_id" field that matches a given string value,

b) the "units" field contains a subdocument with a "period" field (an array of Date objects) where a given date falls between the first and second elements in the array.

The data structure looks like this:

{
  "_id" : ObjectId("5984bdfdb3ac279e39f156d4"),
  "surname" : "Dare",
  "firstname" : "Dan",
  "units" : [{
               "period" : [
                            ISODate("2018-01-01T00:00:00.000Z"), 
                            ISODate("2019-12-31T00:00:00.000Z")
                          ],
               "unit" : {
                          "unit_name" : "My test unit",
                           "_id" : "5979437c6d346eb7d074497a"
                        }
            }]
}

I've tried using various combinations of .find() and .aggregate(), for example using $project and $filter on the period array, following $elemMatch on the unit._id, but to no avail - I get errors such as "can't use $filter on an Array".

Any pointers in the right direction would be appreciated - at least in terms of the most appropriate query type and most efficient way to combine the operators for producing the dataset I'm after.

The schema (as requested):

{
    surname: {
    type: String
  },
    firstname: {
    type: String
  },
  units: {
    type: [{
      unit: {
        _id: String,
        unit_name: String,
      },
      period: [Date]
    }]
  }
}

Post your schema(s).

Mikey
– Mikey

2017-08-17 16:49:25 +00:00
Commented Aug 17, 2017 at 16:49 — Mikey
– Mikey, Commented Aug 17, 2017 at 16:49

Seth Falco · Accepted Answer · 2021-03-17 00:29:42Z

2

Think I was overcomplicating the problem. Not sure whether this is the most efficient way to do it, but using the $and operator seems to provide what I'm looking for:

db.getCollection("records").find({ "$and": [
{"units.unit._id": "5979437c6d346eb7d074497a"},
{"units.unit_period.0": {"$gte": new Date('2018-12-31T00:00:00.000Z')} },
{"units.unit_period.1": {"$lte": new Date('2020-12-31T00:00:00.000Z')} } ]
})

Update

As per Neil Lunn's suggestion in the comments, using $elemMatch rather than $and certainly seems the better option, for the reasons given.

As such, this is the solution I'll go with; namely using #elemMatch to match those element(s) which contain embedded subdocuments and arrays where values may be accessed and matched though simple dot notation on the keys.

I was guilty of trying to over-code a solution using $filter and $aggregate, when retrieving the dataset I need is actually as simple as this:

db.getCollection("records").find({ "units": 
  { "$elemMatch": 
    { "unit._id": "5979437c6d346eb7d074497a", 
      "unit_period.0": { "$gte": new Date("2019-12-31") },
      "unit_period.1": { "$lte": new Date("2020-12-31") }
    } 
  })

edited Mar 17, 2021 at 0:29

Seth Falco

6941 gold badge11 silver badges25 bronze badges

answered Aug 17, 2017 at 18:59

AmateurAardvark

691 silver badge8 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Neil Lunn Over a year ago

Skip the $and and actually apply $elemMatch instead

.find({ "units": { "$elemMatch": { "unit._id": "5979437c6d346eb7d074497a", "unit_period.0": { "$gte": new Date('2019-12-31") }, "unit_period.1": { "$lte": new Date("2020-12-31") } } })

The $elemMatch part is actually important to ensure that all those conditions are actually on the same element. The $and is not needed as all MongoDB query expressions are basically AND conditions already. You rarely need to write an explicy $and. But in basic principle this is "just a query" and no need to overcomplicate.

AmateurAardvark Over a year ago

Many thanks for that, Neil. That works perfectly and I'm very grateful for the heads-up about ensuring the conditions are on the same element (may well have saved me a debugging headache further down the line!). If you throw that down as an answer I'll mark as the accepted solution.

Neil Lunn Over a year ago

The comment was there because you basically answered it yourself in that all you really needed to do was query on the specified array indexes. The $elemMatch ensures that those conditions are actually applied to the same element but in the main context of your question it was really about matching those "inner" element values. You probably really should consider "named properties" rather than using an array though as it certainly looks a lot cleaner and clearer when you do.

Collectives™ on Stack Overflow

Filtering an array, in a subdocument, in an array, in a document in Mongoose/MongoDB

1 Answer 1

Update

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Update

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related