I am new to MongoDB and I am having trouble retrieving only a subset of an array of embedded documents. For instance, I have the following documents:

{
   "_id": "Stock1",
   "data": [{"value": 10.0, "date": "2000-01-01T00:00:00.000Z"},
            {"value": 12.0, "date": "2010-01-01T00:00:00.000Z"},
            {"value": 14.0, "date": "2020-01-01T00:00:00.000Z"}]
},
{
   "_id": "Stock2",
   "data": [{"value": 10.0, "date": "2000-01-01T00:00:00.000Z"},
            {"value": 8.0, "date": "2010-01-01T00:00:00.000Z"},
            {"value": 6.0, "date": "2020-01-01T00:00:00.000Z"}]
},
{
   "_id": "Stock3",
   "data": [{"value": 10.0, "date": "2000-01-01T00:00:00.000Z"},
            {"value": 10.0, "date": "2010-01-01T00:00:00.000Z"},
            {"value": 10.0, "date": "2020-01-01T00:00:00.000Z"}]
}

And I would like to retrieve data between date 2010-01-01 and 2020-01-01 (included) of "Stock1" and "Stock3", i.e. I would like to end up with this:

{
   "_id": "Stock1",
   "data": [{"value": 12.0, "date": "2010-01-01T00:00:00.000Z"},
            {"value": 14.0, "date": "2020-01-01T00:00:00.000Z"}]
},
{
   "_id": "Stock3",
   "data": [{"value": 10.0, "date": "2010-01-01T00:00:00.000Z"},
            {"value": 10.0, "date": "2020-01-01T00:00:00.000Z"}]
}

I have tried the find command:

{"_id": {$in: ["Stock1", "Stock3"]}, "data.date": {$gte: ISODate('2010-01-01'), $lte: ISODate('2020-01-01')}}

But I am retrieving all dates, which is undesirable.

I am aware of the aggregate command but I am unsure of how to construct the pipeline. Can someone pinpoint me on how I should proceed?

Any help would be greatly appreciated!

CodePudding user response：

You can use $elemMatch:

{"_id": {$in: ["Stock1", "Stock3"]}, "data":{$elemMatch:{date: {$gte: ISODate('2010-01-01'), $lte: ISODate('2020-01-01')}}}}

A Single Nested Document Meets Multiple Query Conditions on Nested Fields

Use $elemMatch operator to specify multiple criteria on an array of embedded documents such that at least one embedded document satisfies all the specified criteria.

Source: https://docs.mongodb.com/manual/tutorial/query-array-of-documents/

CodePudding user response：

To avoid $unwind and $group you can use $filter in an aggregate query like this:

db.collection.aggregate([
  {
    "$match": {
      "_id": {
        "$in": ["Stock1","Stock3"]
      }
    }
  },
  {
    "$project": {
      "data": {
        "$filter": {
          "input": "$data",
          "as": "d",
          "cond": {
            "$and": [
              {
                "$gte": [{"$toDate": "$$d.date"},ISODate("2010-01-01")]
              },
              {
                "$lte": [{"$toDate": "$$d.date"},ISODate("2020-01-01")]
              }
            ]
          }
        }
      }
    }
  }
])

Example here

CodePudding user response：

Solution 1

1. $unwind - Descontruct data array to documents.
2. $match - Filter based on id and date range for data.date.
3. $group - Group by id (Reverse for Step 1).

db.collection.aggregate([
  {
    $unwind: "$data"
  },
  {
    $match: {
      $expr: {
        $and: [
          {
            $in: [
              "$_id",
              [
                "Stock1",
                "Stock3"
              ]
            ]
          },
          {
            $gte: [
              {
                $toDate: "$data.date"
              },
              ISODate("2010-01-01")
            ]
          },
          {
            $lte: [
              {
                $toDate: "$data.date"
              },
              ISODate("2020-01-01")
            ]
          }
        ]
      }
    }
  },
  {
    $group: {
      "_id": "$_id",
      "data": {
        $push: "$data"
      }
    }
  }
])

Sample Solution 1 on Mongo Playground

Solution 2

1. $match - Filter document(s) based on _id.
2. $project - Display the document with $filter data array.

db.collection.aggregate([
  {
    $match: {
      "_id": {
        $in: [
          "Stock1",
          "Stock3"
        ]
      }
    }
  },
  {
    $project: {
      "_id": 1,
      "data": {
        "$filter": {
          "input": "$data",
          "cond": {
            "$and": [
              {
                $gte: [
                  {
                    $toDate: "$$this.date"
                  },
                  ISODate("2010-01-01")
                ]
              },
              {
                $lte: [
                  {
                    $toDate: "$$this.date"
                  },
                  ISODate("2020-01-01")
                ]
              }
            ]
          }
        }
      }
    }
  }
])

Sample Solution 2 on Mongo Playground