Convert string to date format within arrays in MongoDB with $dateFromString (presence of NaT)

Question

I am trying to convert some string into date format with MongoDB using $dateFromString. However, since my fields of interested are part of an array, I have encountered some problems in writing down the correct code. Following the this discussion, I attempted to convert string to date format. Unfortunately, the date fields are not always filled, sometime the string-date (because of an absence of information) is NaT. Therefore, the code that I returned the following error:

[js] uncaught exception: Error: command failed: {
            "ok" : 0,
            "errmsg" : "Error parsing date string 'Na'; 0: passing a time zone identifier as part of the string is not allowed 'N'",
            "code" : 241,
            "codeName" : "ConversionFailure"
    } : aggregate failed :

Here there is an example of my documents:

[
    {
        "cflavoratore_crip": "00753DCF12E23D69F5E4CF95A04700AC",
        "annonascita": 1978,
        "codgenere": "M",
        "attivazioni": [
            {
                "cfdatore_crip": "6C1DFCC6596D219ADAAE5ABA9C853015",
                "rapporto_datainizio": "2009-12-30 00:00:00",
                "codregionelavoro": "Puglia",
                "codprovincialavoro": 73.0,
                "dtcessazioneeffettiva": "2010-01-01 00:00:00",
                "dtfineprevista": "2010-01-01 00:00:00"
            }
        ]
    },
    {
        "cflavoratore_crip": "0083422D66F4C2EAEBB1B296DF86975A",
        "annonascita": 1985,
        "codgenere": "M",
        "attivazioni": [
            {
                "cfdatore_crip": "27E232D343049C13213C4DCA5756B5A5",
                "rapporto_datainizio": "2015-07-29 00:00:00",
                "codregionedomicilio": "Sicilia",
                "codprovincialavoro": 87.0,
                "dtcessazioneeffettiva": "2015-08-13 00:00:00",
                "dtfineprevista": "NaT"
            }
        ]
    }
]

The variables that I want to convert into date format are the following: rapporto_datainizio, dtcessazioneeffettiva, and dtfineprevista. However, is some case they can assume the value of NaT. I guess I should use the $cond to solve this problem (?).

So far the code that I used has been the following one (collection name: datacico). Quite long...

db.datacico.aggregate([
    {
        '$addFields': {
            'attivazioni': {
                '$map': {
                    'input': '$attivazioni', 
                    'as': 'attivazioni', 
                    'in': {  
                        'cfdatore_crip': '$$attivazioni.cfdatore_crip',
                        'rapporto_datainizio': {
                            '$toDate': {
                                '$substr': [
                                    '$$attivazioni.rapporto_datainizio', 0, {
                                        '$subtract': [ 
                                            {
                                                '$strLenCP': '$$attivazioni.rapporto_datainizio'
                                            }, 1
                                        ]
                                    }
                                ]
                            }     
                        }, 
                        'codregionedomicilio': '$$attivazioni.codregionedomicilio', 
                        'codregionelavoro': '$$attivazioni.codregionelavoro',
                        'codprovincialavoro': '$$attivazioni.codprovincialavoro',                 
                        'dtcessazioneeffettiva': {
                            '$toDate': {
                                '$substr': [
                                    '$$attivazioni.dtcessazioneeffettiva', 0, {
                                        '$subtract': [
                                            {
                                                '$strLenCP': '$$attivazioni.dtcessazioneeffettiva'
                                            }, 1
                                        ]
                                    }
                                ]
                            }     
                        },                    
                        'dtfineprevista': {
                            '$toDate': {
                                '$substr': [
                                    '$$attivazioni.dtfineprevista', 0, {
                                        '$subtract': [
                                            {
                                                '$strLenCP': '$$attivazioni.dtfineprevista'
                                            }, 1
                                        ]
                                    }
                                ]
                            }     
                        }
                    }
                }
            }
        }
    }, {
        '$out': 'datacico'
    }
])

Nonetheless, I think that with the use of $dateFromString it could become quite more easier and shorter. I used the following one, but it does not work. In this case I referred only to the filed of rapporto_datainizio.

db.datacico.aggregate([{
"$project": {
    "attivazioni": {
      "$map": {
        "input": "$attivazioni",
       "in": {
         "rapporto_datainizio": {
            "$dateFromString": {
              "dateString": '$rapporto_datainizio'
             }
           }
         }
       }
     }
   }
}])

I hope someone could give me some hints. Thank you in advance!

prasad_ · Accepted Answer · 2019-11-19 12:25:04Z

1

This aggregation query will work. Note the value of "NaT" cannot be converted to a Date object. So, what is your logic about it? In the query I substituted "NaT" with today's date (see the $cond in the $map); but you can fill it with what your application needs.

db.dates.aggregate( [
{ $project: { 
       attivazioni: { 
           $map: {
               input: "$attivazioni",
                  as: "att",
                  in: {
                      "cfdatore_crip" : "$$att.cfdatore_crip",
                      "rapporto_datainizio" : { $toDate: "$$att.rapporto_datainizio" },
                      "codregionelavoro" : "$$att.codregionelavoro",
                      "codprovincialavoro" : "$$att.codprovincialavoro",
                      "dtcessazioneeffettiva" : { $toDate: "$$att.dtcessazioneeffettiva" },
                      "dtfineprevista" : { $cond: [ { $eq: [ "$$att.dtfineprevista", "NaT" ] }, 
                                                     ISODate(), 
                                                     { $toDate: "$$att.dtfineprevista" } 
                                                  ] 
                                           }
                     }
} } } },
] )

answered Nov 19, 2019 at 12:25

prasad_

14.4k2 gold badges27 silver badges44 bronze badges

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

1 Comment

Nicola Caravaggio Over a year ago

Thank you @prasad_ for your help!

Collectives™ on Stack Overflow

Convert string to date format within arrays in MongoDB with $dateFromString (presence of NaT)

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related