MongoDB Data Range Filter Subcollection

Question

I am trying to get the values of a subcollection base on a data range filter. However when I do the filtering I am getting all the values of the collection.

For example for the following data I want to obtain only the values for dates in range 2019-01-01 to 2019-12-31

[
  {
    symbol: "sensor1",
    data: [
      {
        date: ISODate("2017-11-25T00:00:00.000Z"),
        value: 2.45,
        
      },
      {
        date: ISODate("2019-11-26T00:00:00.000Z"),
        value: 2.2444
      },
      {
        date: ISODate("2019-12-25T00:00:00.000Z"),
        value: 2.5,
        
      },
      {
        date: ISODate("2020-01-25T00:00:00.000Z"),
        value: 2.461,
        
      },
      
    ]
  }
]

Any recomendation? Thanks!

Here is the playground: https://mongoplayground.net/p/UGpvgzjoFCi

Answer 1

you can do an aggregation with $project and $filter. $filter can be used on the array to filter out the dates based on a condition.

db.collection.aggregate([
  {
    $match: {
      symbol: "sensor1"
    }
  },
  {
    $project: {
      "symbol": 1,  //show symbol in final result
      "_id": 0,     //hide _id in final result
      "data": {
        $filter: {
          input: "$data",
          as: "d",
          cond: {
            $and: [
              {
                $gt: [
                  "$$d.date",
                  ISODate("2019-01-01")
                ]
              },
              {
                $lt: [
                  "$$d.date",
                  ISODate("2020-12-31")
                ]
              }
            ]
          }
        }
      }
    }
  }
])

demo