Formatting dates with $dateToString in nested arrays

Question

I have a collection of documents where each document represents a location of charging stations. I'm trying to use $map and $dateToString to transform each last_updated field into a string and drop the milliseconds portion of the datetime. There can be up to 3 last_updated fields on a location. A top level last_updated, a last_updated for each evse within an evses array, and a last_updated field within each connector of each evse. It is also possible for the evses array to not be present.

Here is an example of 2 locations:

[
    {
        "id" : "A",
        "name" : "Meades Park",
        "evses" : [
            {
                "uid" : "B",
                "connectors" : [
                    {
                        "id" : "C",
                        "standard" : "IEC_62196_T1",
                        "last_updated" : ISODate("2021-02-18T23:54:56.000Z")
                    }
                ],
                "last_updated" : ISODate("2021-02-18T23:54:56.000Z")
            },
            {
                "uid" : "D",
                "connectors" : [
                    {
                        "id" : "E",
                        "standard" : "IEC_62196_T1",
                        "last_updated" : ISODate("2021-02-18T23:54:56.000Z")
                    }
                ],
                "last_updated" : ISODate("2021-02-18T23:54:56.000Z")
            }
        ],
        "last_updated" : ISODate("2021-12-14T23:42:06.000Z")
    },
    {
        "id" : "F",
        "name" : "5th Avenue",
        "last_updated" : ISODate("2022-01-12T13:12:01.000Z")
    }
]

I have my query working for the top level last_updated field and the evses.last_updated field, but I am having trouble modifying the evses.connectors.last_updated field. This is my query:

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

Is it possible to do another $map within the $map.in object? I was able to get this working by turning in into an array, but this changed the way the data was returned.

Here is an example of my query using $map.in as an array:

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

In this case the returned documents look like this:

[
    {
        "id" : "A",
        "name" : "CHARGEPOINT WVCC 1684DELL 1",
        "evses" : [
            [
                {
                    "uid" : "B",
                    "connectors" : [
                        {
                            "id" : "C",
                            "standard" : "IEC_62196_T1",
                            "last_updated" : ISODate("2021-02-18T23:54:56.000+0000")
                        }
                    ],
                    "last_updated" : "2021-02-18T23:54:56Z"
                },
                [
                    {
                        "id" : "C",
                        "standard" : "IEC_62196_T1",
                        "last_updated" : "2021-02-18T23:54:56Z"
                    }
                ]
            ],
            [
                {
                    "uid" : "D",
                    "connectors" : [
                        {
                            "id" : "E",
                            "standard" : "IEC_62196_T1",
                            "last_updated" : ISODate("2021-02-19T22:15:43.000+0000")
                        }
                    ],
                    "last_updated" : "2021-02-19T22:15:43Z"
                },
                [
                    {
                        "id" : "E",
                        "standard" : "IEC_62196_T1",
                        "last_updated" : "2021-02-19T22:15:43Z"
                    }
                ]
            ]
        ],
        "last_updated" : "2021-12-14T23:42:06Z"
    },
    {
        "id" : "F",
        "name" : "5th Avenue",
        "last_updated" : "2022-01-12T13:12:01Z",
        evses: null
    }
]

Is it possible to perform a $map within another $map without duplicating the evses.connectors array and nesting them in another array?

Yong Shun · Accepted Answer · 2022-08-09 13:11:44Z

Your query should be as below:

Remove the [] from in for the $map.
Merge objects for $$evse, last_updated document and connectors document.

db.collection.aggregate([
  {
    $addFields: {
      last_updated: {
        $dateToString: {
          format: "%Y-%m-%dT%H:%M:%SZ",
          date: "$last_updated"
        }
      },
      evses: {
        $map: {
          input: "$evses",
          as: "evse",
          in: {
            $mergeObjects: [
              "$$evse",
              {
                last_updated: {
                  $dateToString: {
                    format: "%Y-%m-%dT%H:%M:%SZ",
                    date: "$$evse.last_updated"
                  }
                }
              },
              {
                connectors: {
                  $map: {
                    input: "$$evse.connectors",
                    as: "connector",
                    in: {
                      $mergeObjects: [
                        "$$connector",
                        {
                          last_updated: {
                            $dateToString: {
                              format: "%Y-%m-%dT%H:%M:%SZ",
                              date: "$$connector.last_updated"
                            }
                          }
                        }
                      ]
                    }
                  }
                }
              }
            ]
          }
        }
      }
    }
  }
])

Sample Mongo Playground

Collectives™ on Stack Overflow

Formatting dates with $dateToString in nested arrays

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related