Convert Date to String in nested array in mongodb

Question

I have a mongodb collection called cases and inside cases I have an array of cases per company object.

So the structure is:

Inside each case I want to use the createddate (which is a string) and endDate (also string) and convert it to a mongodb date.

When I use NoSQLBooster I add the following query:

db.cases.aggregate([
{ $match: { companyID: 218 }},
{ $unwind: "$cases" },
{ $match: { 'cases.id': '299' }},
{ $addFields: { 'cases.created':  new Date('2010-06-21T00:00:00.000'), 'cases.closed': new Date('2014-08-29T00:00:00.000') }},
{ $group: { _id: "$_id", cases: { $push: "$cases" }}}])

This will add a date in a new field - created and then closed. This is exactly what I want.

However, in my code (using mongoose) I have the following:

scripts.commponent.ts:

runThroughCasesAndConvertDates(id) {
    this.scriptsService.getAllCasesToModify({ companyID : id}).subscribe( res => {
      if (res.length > 0) {
        for (let i = 0; i < res[0].cases.length; i++) {
          const caseID = res[0].cases[i].id;
          const data = {
            companyID: id,
            caseID: caseID,
            created: moment(res[0].cases[i].createddate, 'DD-MMM-YYYY h:mm a').format('YYYY-MM-DD[T00:00:00.000Z]'),
            closed: ''
          };
          if (res[0].cases[i].endDate !== '') {
             data.closed = moment(res[0].cases[i].endDate, 'DD-MMM-YYYY h:mm a').format('YYYY-MM-DD[T00:00:00.000Z]');
           }
          this.scriptsService.updateDates(data).subscribe();
        }
      }
    });
  }

scripts.service.ts

updateDates(body) {
    return this.http.post('/db/cases/updateAllDates', body).pipe(
      map(res => res.json())
    );
  }

casesDB.js

    router.post('/updateAllDates', (req, res) => {
  const { body } = req;
Cases.aggregate([
    { $match: { companyID: body.companyID }},
    { $unwind: "$cases" },
    { $match: { 'cases.id': body.caseID }},
    { $addFields: { 'cases.created':  new Date(body.created), 'cases.closed': new Date(body.closed) } },
    { $group: { _id: "$_id" }
  }],
  function (err, data) {
    res.json(data)
   });
});

But it does not add anything into the array. Im really confused as to what Im doing wrong. Maybe there is a better way / approach to doing this?

Thank you

Ashh · Accepted Answer · 2020-01-24 09:37:34Z

3

You can $map over the cases array and can change the date string fields to date object fields.

Cases.aggregate([
  { "$addFields": {
    "cases": {
      "$map": {
        "input": "$cases",
        "in": {
          "$mergeObjects": [
            "$$this",
            {
              "createddate": {
                "$dateFromString": { "dateString": "$$this.createddate" }
              },
              "endDate": {
                "$dateFromString": { "dateString": "$$this.endDate" }
              }
            }
          ]
        }
      }
    }
  }}
])

Update: If dates are blank string

Cases.aggregate([
  { "$addFields": {
    "cases": {
      "$map": {
        "input": "$cases",
        "in": {
          "$mergeObjects": [
            "$$this",
            {
              "createddate": {
                "$cond": [
                  { "$eq": ["$$this.createddate", ""] },
                  null
                  { "$dateFromString": { "dateString": "$$this.createddate" } }
                ]
              },
              "endDate": {
                "$cond": [
                  { "$eq": ["$$this.endDate", ""] },
                  null
                  { "$dateFromString": { "dateString": "$$this.endDate" } }
                ]
              }
            }
          ]
        }
      }
    }
  }}
])

edited Jan 24, 2020 at 9:37

answered Jan 17, 2020 at 11:47

Ashh

46.6k16 gold badges111 silver badges137 bronze badges

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

10 Comments

Andrew Over a year ago

Hi @ashh just a quick question regarding the above query - There are a few entries that have a closed date of '' (blank) and so the query throws an error. Is there a way of putting a match in to set only cases.status: 'CLOSED' (this will mean it definitely has a closed date) ?

Ashh Over a year ago

dates ("") are blank or does not exists (null)?

Andrew Over a year ago

Dates are blank

Andrew Over a year ago

Hi do you know what to do with a blank date? Ive tried lots of ways but still get an error when doing it.

Ashh Over a year ago

@Andrew Please ask a new question if you have further doubts.

|

Collectives™ on Stack Overflow

Convert Date to String in nested array in mongodb

1 Answer 1

10 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related