Mongodb search field with range inside array of object

I have multiple documents in a collection like this

[
  {
    _id: 123,
    data: 1,
    details: [
      {
        item: "a",
        day: 1
      },
      {
        item: "a",
        day: 2
      },
      {
        item: "a",
        day: 3
      },
      {
        item: "a",
        day: 4
      }
    ],
    someMoreField: "xyz"
  }
]

Now I want document with _id: 123 and details field should only contain day within range of 1 to 3. So the result will be like below.

{
    _id: 123,
    data: 1,
    details: [
        {
            item: 'a',
            day: 1,
        },
        {
            item: 'a',
            day: 2,
        },
        {
            item: 'a',
            day: 3,
        },
    ],
    someMoreField: 'xyz',
};

I tried to do this by aggregate query as:

db.collectionaggregate([
      {
        $match: {
          _id: id,
          'details.day': { $gt: 1, $lte: 3 },
        },
      },
      {
        $project: {
          _id: 1,
          details: {
            $filter: {
              input: '$details',
              as: 'value',
              cond: {
                $and: [
                  { $gt: ['$$value.date', 1] },
                  { $lt: ['$$value.date', 3] },
                ],
              },
            },
          },
        },
      },
    ])

But this gives me empty result. Could someone please guide me through this?

Answer

You are very close, you just need to change the $gt to $gte and $lt to $lte.

Another minor syntax error is you’re accessing $$value.date but the schema you provided does not have that field, it seems you need to change it to $$value.day, like so:

db.collection.aggregate([
  {
    $match: {
      _id: 123,
      "details.day": {
        $gt: 1,
        $lte: 3
      }
    }
  },
  {
    $project: {
      _id: 1,
      details: {
        $filter: {
          input: "$details",
          as: "value",
          cond: {
            $and: [
              {
                $gte: [
                  "$$value.day",
                  1
                ]
              },
              {
                $lte: [
                  "$$value.day",
                  3
                ]
              },
              
            ],
            
          },
          
        },
        
      },
      
    },
    
  },
])

Mongo Playground