Question 117787 in Mongodb Question posted in
The official documentation can be found here.

MongoDb filter on map with $gte and $lte on two different dates

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.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

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

  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search