Format Date with $dateToString only if date exists

Working with Data
, ,
1

I’m currently using the $dateToString operator to drop milliseconds from datetime fields (the last_updated fields to be specific.) and I’ve run into an issue.

I have a collection of documents formatted like so:

[
    {
        "id": "A",
        "name": "Meades Park",
        "last_updated": ISODate("2022-01-01T00:00:00.000Z"),
        "evses": [
            {
                "id": "AB",
                "status": "AVAILABLE",
                "last_updated": ISODate("2022-01-01T00:00:00.000Z")
                "connectors": [
                    {
                        "id": "AAB",
                        "power_type": "DC",
                        "last_updated": ISODate("2022-01-01T00:00:00.000Z")
                    }
                ]
            }
        ]
    },
    {
        "id": "A",
        "name": "Medes Park",
        "last_updated": ISODate("2022-01-01T00:00:00.000Z")
    }
]

I’m able to format the date fields with the following query:

db.collection.aggregate([
    {
        $addFields: {
            'last_updated': { 
                $dateToString: { 
                    format: '%Y-%m-%dT%H:%M:%SZ', 
                    date: '$last_updated'
                } 
            }
            'evses.last_updated': { 
                $dateToString: { 
                    format: '%Y-%m-%dT%H:%M:%SZ', 
                    date: '$last_updated'
                } 
            }
            'evses.connectors.last_updated': { 
                $dateToString: { 
                    format: '%Y-%m-%dT%H:%M:%SZ', 
                    date: '$last_updated'
                } 
            }
        }
    }
])

While this overwrites the evses.last_updated & evses.connectors.last_updated fields with the top level last_updated field, it works well for my use case for the first document.

My issue is that the second document, where the evses array is absent, this query will create an evses & connectors object and populate it with the last_updated field. Is it possible to only convert evses.last_updated and evses.connectors.last_updated if the evses and connectors arrays exist? I’m aware of the onNull operator that can be used on $dateToString, but I’ve not found a way to conditionally stringify a field using it, it seems like it is more for default fields.

3

Hi @Greg_Fitzpatrick-Bel and welcome to the community!!

This would be possible using the operator $isArray which would check if the $evses field is present as an array then update the required records.

The following aggregation query using the $addFields stage would check first if the array(s) exists else would return the “$$REMOVE” variable:

const condBody1 = {
    $cond: {
     if: { $isArray: "$evses" },
      then: {
         $dateToString: {
          format: "%Y-%m-%dT%H:%M:%SZ",
           date: "$last_updated"
        }
       },
       else: "$$REMOVE"
     }
   };

const condBody2 = {
    $cond: {
     if: { $isArray: "$evses.connectors" },
      then: {
         $dateToString: {
          format: "%Y-%m-%dT%H:%M:%SZ",
           date: "$last_updated"
        }
       },
       else: "$$REMOVE"
     }
   };

db.collection.aggregate([{
  '$addFields': {
    last_updated: {
      '$dateToString': { format: '%Y-%m-%dT%H:%M:%SZ', date: '$last_updated' }
    },
    'evses.last_updated': {
      '$cond': {
        if: { '$isArray': '$evses' },
        then: {
          '$dateToString': { format: '%Y-%m-%dT%H:%M:%SZ', date: '$last_updated' }
        },
        else: '$$REMOVE'
      }
    },
    'evses.connectors.last_updated': {
      '$cond': {
        if: { '$isArray': '$evses.connectors' },
        then: {
          '$dateToString': { format: '%Y-%m-%dT%H:%M:%SZ', date: '$last_updated' }
        },
        else: '$$REMOVE'
      }
    }
  }
}])

The $cond would check for the evses and evses.connector fields and update the required field.
However, if the array does not exist, it would simple use the $$REMOVE

The output response would like the following:

[
  {
    _id: ObjectId("62fdc365b5f8518fe18d213e"),
    id: 'A',
    name: 'Meades Park',
    last_updated: '2022-01-01T00:00:00Z',
    evses: [
      {
        id: 'AB',
        status: 'AVAILABLE',
        last_updated: '2022-01-01T00:00:00Z',
        connectors: [
          {
            id: 'AAB',
            power_type: 'DC',
            last_updated: '2022-01-01T00:00:00Z'
          }
        ]
      }
    ]
  },
  {
    _id: ObjectId("62fdca59b5f8518fe18d213f"),
    id: 'A',
    name: 'Medes Park',
    last_updated: '2022-01-01T00:00:00Z',
    evses: { connectors: {} }
  }
]

You could change the evses: { connectors: {} } in the output by adding an extra stage to the pipeline if needed.

Also, please note that the above query is tested on the sample document provided. Please make sure you do a thorough testing and ensure it meets all the use cases and requirements.

Let us know if you have any further questions.

Thanks
Aasawari

5 Likes
5

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.