CODEWITHSUNDEEP
mongodbmongoose
AmateurAardvark
AmateurAardvark

Reputation: 69

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

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]
    }]
  }
}

Upvotes: 2

Views: 3420

Answers (1)

AmateurAardvark
AmateurAardvark

Reputation: 69

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") }
    } 
  })

Upvotes: 2

Related Questions