Query for date range inside nested arry not working

Question

I have two queries I am running. Both query for documents inside a date range. The first example works using this:

db.collection('' + site_id + '_page_visits').find({created_at: {"$gte": new Date("" + date + "T00:00:00.000Z"), "$lte": new Date("" + date + "T23:59:59.999Z")}}).toArray(...

However, the date I need to check on in the second query is nested inside an array inside call_to_action_responses, and using the same setup for the date range filter does not work, example:

db.collection('' + site_id + '_leads').find({"call_to_action_responses.response_date": {$gte: new Date("" + date + "T00:00:00.000Z"), $lte: new Date("" + date + "T23:59:59.999Z")}, "call_to_action_responses.page_url": {$not: regex}, "call_to_action_responses.page_url": {$ne: ''} }).toArray(...

Using that code it returns all documents matching $lte only. It seems to ignore the $gte part of the filter, which works in the first example.

A Sample record looks like this:

{
  _id: ObjectId("XXxXXxxxxxXXxXx"),
  cookie: "xxcXXXXxxx-xxxXXXx-XXXXxx",
  updated_at: ISODate("2016-09-20T01:31:56.677Z"),
  created_at: ISODate("2015-04-22T08:32:34.864Z"),
  call_to_action_responses: [
    {
      response_date: ISODate("2015-04-22T08:32:34.863Z"),
      page_version: "1",
      template_response_path_base: "page_path",
      page_url: "http://www.webiste.com/about",
      page_id: ObjectId("5527d6c40de2c02a0b0000f2"),
      email: "user_email_at@email.com",
      lead_data: {
        phone: "XXXXXXXXX",
        viewed_assets: "This_Asset",
        campaign_medium: "",
        campaign_content: "",
        first_name: "first",
        last_name: "last",
        campaign_source: "",
        campaign_name: "",
        company: "Test Company",
        title: ""
       },
      _id: ObjectId("XXXXxxXXxXXxXXXX"),
      name: "View ",
      target_url: "http://www.webiste.com/about"
    }
  ]
}

Can anyone see what I am doing wrong here? Ir let me know the syntax to this because I cannot seem to get it dialed in. Thanks for any help!

Does this answer your question? [Retrieve only the queried element in an object array in MongoDB collection](https://stackoverflow.com/questions/3985214/retrieve-only-the-queried-element-in-an-object-array-in-mongodb-collection) Filtering on arrays you either need `$elemMatch` or `$filter` — whoami - fakeFaceTrueSoul, Jun 10 '20 at 18:40
`call_to_action_responses` is an array field, you should use `$filter` operator. — ngShravil.py, Jun 10 '20 at 18:45
So looking through those answers it looks like they are matching, I need everything in a range. I couldn't find an answer there addressing that. — Rockwell Rice, Jun 10 '20 at 18:45
Wait, so I need to use `$and` and `$match`? Is it possible to just show an example of what the syntax here is, this is what I am struggling to figure out. Just telling me to use operators doesn;t really help much. — Rockwell Rice, Jun 10 '20 at 18:50
What is different from this query to what you are trying: https://mongoplayground.net/p/peWBHRc-UKZ — Joe, Jun 11 '20 at 02:04
Not sure seems to work correctly but need to try it with multiple records. I did not know this page existed, thanks for linking to it — Rockwell Rice, Jun 11 '20 at 02:19

score 0 · Answer 1 · answered Jun 11 '20 at 04:58

Below query will be helpful:

db.collection.aggregate([
  {
    $addFields: {
      call_to_action_responses: {
        $filter: {
          input: "$call_to_action_responses",
          as: "i",
          cond: {
            $and: [
              {
                $gte: [
                  "$$i.response_date",
                  new Date("2015-03-23T00:00:00.000Z")
                ]
              },
              {
                $lte: [
                  "$$i.response_date",
                  new Date("2015-06-23T23:59:59.999Z")
                ]
              }
            ]
          }
        }
      }
    }
  }
])

MongoPlayGroundLink

Query for date range inside nested arry not working

1 Answers1