converting the datestring in a nested array of objects (MongoDB)

Question

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",
            },
          },
        },
      },
    ]);

ray · Accepted Answer · 2021-10-14 09:20:51Z

2

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.

edited Oct 14, 2021 at 9:20

answered Oct 14, 2021 at 8:34

ray

15.6k12 gold badges96 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Thomas Over a year ago

I am still not able to filter out the correct docs. I edited the playground you provided, possible for you to help me take a look, thanks. mongoplayground.net/p/gwFVoi_miW_

ray Over a year ago

@Thomas updated the answer to use $reduce to better suit your needs.

Thomas Over a year ago

thanks for your help, but once I change the date, the error occurred

ray Over a year ago

whats the error message? and what is your latest trial?

Takis Over a year ago

try this similar code with one extra check of name field before try to convert

|

Collectives™ on Stack Overflow

converting the datestring in a nested array of objects (MongoDB)

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related