I need to filter my documents with dates. My documents contain an array of object with a startDate and an endDate fields like this :

[
  {
    "key": 1,
    "dates": [
      {
        "id": 1,
        "startDate": "20220512",
        "endDate": "20220517"
      },
      {
        "id": 2,
        "startDate": "20220701",
        "endDate": "20220705"
      }
    ]
  },
  {
    "key": 2,
    "dates": [
      {
        "id": 3,
        "startDate": "20220501",
        "endDate": "20220505"
      }
    ]
  }
]

How can I filter this in aggregate with a request like : /documents?startDate=20220510&endDate=20220518 ? In this casa I m expeting only the first document output.

In php I wrote this request that works on mongoplayground but not here, I got an error "the match filter must be an expression in an object" :

$filters[] = [
   '$match' => [
     '$and' => [
        [
            'dates.startDate' => [
               '$gte' => '20220517',
              ],
         ],
         [
             'dates.endDate' => [
                 '$lte' => '20220517',
              ],
         ],

      ],
   ],

];

I code in php with Laravel and Jenssengers Package for MongoDb but I make my request in a Raw Query.

Thanks for helping me.

CodePudding user response：

Very similar:

EDIT: include $toInt:

db.collection.aggregate([
  {
    $project: {
      result: {
        $filter: {
          input: "$dates",
          as: "item",
          cond: {
            $and: [
              {
                $gte: [
                  {
                    $toInt: "$$item.startDate"
                  },
                  20220512
                ]
              },
              {
                $lte: [
                  {
                    $toInt: "$$item.endDate"
                  },
                  20220517
                ]
              }
            ]
          }
        }
      }
    }
  }
])

playground example

CodePudding user response：

Ok thanks a lot !

In my case, I had to use '$addFields' instead of '$project' because I have several pipeline stages in my aggregate ($match, $lookup, $addFields).

Everything is working well.

db.collection.aggregate([
  {
    $addFields: {
      result: {
        $filter: {
          input: "$dates",
          as: "item",
          cond: {
            $and: [
              {
                $gte: [
                  {
                    $toInt: "$$item.startDate"
                  },
                  20220512
                ]
              },
              {
                $lte: [
                  {
                    $toInt: "$$item.endDate"
                  },
                  20220517
                ]
              }
            ]
          }
        }
      }
    }
  }
])