What I want to achieve is finding a specific document on that current month based on the provided date. The date is stored as a string, in order for me to compare the date I need to convert the date first. However I have trouble on converting the datestring in a nested array of objects.

My collections:

{
    sections: [{
            fields: [{
                    name: 'Date',
                    value: '2020-11-30T15:59:59.999Z' // this is string 
                },
                {
                    name: 'Title',
                    value: 'My book' 
                },
                {
                    name: 'Author',
                    value: 'Henry'
                }
            ]
        ]
    }
}

What I have tried:

1)

const existingReport = await Report.find({
      $expr: {
        $gte: [
          {
            $dateFromString: {
              dateString: "$sections.field[0].value",
            },
          },
          moment(payload.forPeriod).startOf("month").toDate(),
        ],
        $lt: [
          {
            $dateFromString: {
              dateString: "$sections.field[0].value",
            },
          },
          moment(payload.forPeriod).endOf("month").toDate(),
        ],
      },
    });

const existingReport1 = await Report.aggregate([
      {
        $addFields: {
          formattedData: {
            $cond: {
              if: {
                $eq: ["$sections.field.value", "Date"],
              },
              then: {
                $dateFromString: {
                  dateString: "$sections.field.value",
                },
              },
              else: "$sections.field.value",
            },
          },
        },
      },
    ]);

CodePudding user response：

You can simply do a $toDate with the help of 2 $reduce to iterate the sections and fields array.

db.collection.aggregate([
  {
    "$match": {
      $expr: {
        $eq: [
          true,
          {
            "$reduce": {
              "input": "$sections",
              "initialValue": false,
              "in": {
                "$reduce": {
                  "input": "$$this.fields",
                  "initialValue": false,
                  "in": {
                    $or: [
                      "$$value",
                      {
                        $and: [
                          {
                            $gte: [
                              {
                                "$toDate": "$$this.value"
                              },
                              new Date("2020-11-01")
                            ]
                          },
                          {
                            $lte: [
                              {
                                "$toDate": "$$this.value"
                              },
                              new Date("2020-11-30")
                            ]
                          }
                        ]
                      }
                    ]
                  }
                }
              }
            }
          }
        ]
      }
    }
  }
])

Here is the Mongo playground for your reference.