How can I filter query subdocuments using aggregate in Mongoose?-CodePudding

I have the following document information.

[
  {
    _id: ObjectId("61d67f0a74ec8620f34c57ed"),
    shot: [
      {
        shotId: "undefinedMKSf*Tf#!qHxWpz1hPzUBTz%",
        clubType: "driver",
        shotCount: 20,
        unit: "meter",
        _id: ObjectId("61d67f0a74ec8620f34c57ef"),
        createdAt: ISODate("2022-01-06T05:32:58.391Z")
      },
      {
        shotId: "undefinedMKSf*Tf#!qHxWpz1hPzUBTz%",
        clubType: "wood",
        shotCount: 20,
        unit: "meter",
        _id: ObjectId("61d67f0a74ec8620f34c57f0"),
        createdAt: ISODate("2022-01-08T05:32:58.391Z")
      },
      {
        shotId: "undefinedMKSf*Tf#!qHxWpz1hPzUBTz%",
        clubType: "wood",
        shotCount: 15,
        unit: "yard",
        _id: ObjectId("61d67f0a74ec8620f34c57f0"),
        createdAt: ISODate("2022-01-08T05:32:58.391Z")
      },
      {
        shotId: "undefinedMKSf*Tf#!qHxWpz1hPzUBTz%",
        clubType: "wood",
        shotCount: 15,
        unit: "yard",
        _id: ObjectId("61d67f0a74ec8620f34c57f0"),
        createdAt: ISODate("2022-01-08T05:32:58.391Z")
      }
    ]
  }
]

In the above document, how can I find only documents with clubType of wood, shotCount of 15, and unit of yard?

I have written a query statement as follows, but the expected result doesn't come out.

db.collection.aggregate([
  {
    "$match": {
      _id: ObjectId("61d67f0a74ec8620f34c57ed"),
      "shot.clubType": "wood",
      shot: {
        $elemMatch: {
          $and: [
            {
              "createdAt": {
                $gte: ISODate("2022-01-07T05:32:58.391Z")
              }
            },
            {
              "createdAt": {
                $lte: ISODate("2022-01-09T05:32:58.391Z")
              }
            }
          ]
        }
      }
    }
  },
  {
    "$set": {
      shot: {
        "$filter": {
          "input": "$shot",
          "as": "s",
          "cond": {
            $and: [
              {
                "$eq": [
                  "$$s.clubType",
                  "wood"
                ]
              },
              {
                "$gte": [
                  "$$s.createdAt",
                  ISODate("2022-01-07T05:32:58.391Z")
                ]
              },
              {
                "$lte": [
                  "$$s.createdAt",
                  ISODate("2022-01-09T05:32:58.391Z")
                ]
              }
            ]
          }
        }
      }
    }
  }
])

How do I modify the query statement?

Link for reference : [MongoPlayGround]

CodePudding user response：

add $project in the end of aggregate

db.collection.aggregate([
  {
    "$match": {
      _id: ObjectId("61d67f0a74ec8620f34c57ed"),
      "shot.clubType": "wood",
      shot: {
        $elemMatch: {
          $and: [
            {
              "createdAt": {
                $gte: ISODate("2022-01-07T05:32:58.391Z")
              }
            },
            {
              "createdAt": {
                $lte: ISODate("2022-01-09T05:32:58.391Z")
              }
            }
          ]
        }
      }
    }
  },
  {
    "$set": {
      shot: {
        "$filter": {
          "input": "$shot",
          "as": "s",
          "cond": {
            $and: [
              {
                "$eq": [
                  "$$s.clubType",
                  "wood"
                ]
              },
              {
                "$gte": [
                  "$$s.createdAt",
                  ISODate("2022-01-07T05:32:58.391Z")
                ]
              },
              {
                "$lte": [
                  "$$s.createdAt",
                  ISODate("2022-01-09T05:32:58.391Z")
                ]
              }
            ]
          }
        }
      }
    }
  },
  {
    "$project": {
      "shot.clubType": 1,
      "shot.shotCount": 1,
      "shot.unit": 1
    }
  }
])

mongoplayground