How to get the latest documents from a collection using date time?

I have searched in SO for this specific problem, but couldn't find an example that is similar to my data structure. I have this kind of data structure:

[
   {
      stationId: 'xxxxx',
      stationName:  'xxxx',
      state: 'xxxx',
      lat: 'xxxxx',
      long: 'xx.xxxxx',
      waterLevel: [
         {
              wlDateTime: '11/04/2022 11:30',
              wlSeverity: 'Danger',
              wlLevel: 7.5
         },
         {
              wlDateTime: '11/04/2022 09:00',
              wlSeverity: 'Danger',
              wlLevel: 7.3
         },
         {
              wlDateTime: '11/04/2022 03:00',
              wlSeverity: 'Normal',
              wlLevel: 5.2
         }
      ],
      rainfallData: [
         {
              rfDateTime: '11/04/2022 11:30',
              rfSeverity: 'Heavy',
              rfLevel: 21
         },
         {
              rfDateTime: '11/04/2022 10:30',
              rfSeverity: 'Heavy',
              rfLevel: 21
         },
         {
              rfDateTime: '11/04/2022 9:30',
              rfSeverity: 'Heavy',
              rfLevel: 21
         }
      ]
   }
]

The question is, how can I get documents that have wlDateTime equal today, with wlSeverity equal to Danger, but I just want the latest record from the waterLevel array. The same case with the rainfallDataarray i.e. to return with the latest reading for today.

Sample expected return will be like this:

[
   {
      stationId: 'xxxxx',
      stationName:  'xxxx',
      state: 'xxxx',
      lat: 'xxxxx',
      long: 'xx.xxxxx',
      waterLevelData: [
         {
              wlDateTime: '11/04/2022 11:30',  //latest data compared to the array
              wlSeverity: 'Danger',
              wlLevel: 7.5
         }
      ],
      rainfallData: [
         {
              rfDateTime: '11/04/2022 11:30', //latest data compared to the array
              rfSeverity: 'Heavy',
              rfLevel: 21
         }
      ]
   }
]

I've tried querying it like this:

    Meteor.publish('Alerts', function(){
      return AlertLatest.find({
        'waterLevelData.wlSeverity':'Danger',
      }, {
        fields : {
        'stationName'               : 1,
        'state'                     : 1,
        'lat'                       : 1,
        'long'                      : 1,
        'waterLevelData.wlDateTime' : 1,
        'waterLevelData.wlSeverity' : 1,
        'waterLevelData.wlLevel'    : 1,
        'rainfallData.rfSeverity'   : 1,      
      }},{sort: { 'waterLevelData.wlDateTime' : -1}});
    })

but the query returned data that isn't how I wanted. Any help will be much appreciated.

CodePudding user response：

I don't think you can sort by embedded document in an array field. It's not how mongodb works.

CodePudding user response：

1. change your date string to date
2. filter the array to find the max one

db.collection.aggregate([
  {
    $match: {}
  },
  {
    $set: {
      waterLevel: {
        $map: {
          input: "$waterLevel",
          as: "w",
          in: {
            $mergeObjects: [ "$$w", { wlDateTime: { $toDate: "$$w.wlDateTime" } } ]
          }
        }
      },
      rainfallData: {
        $map: {
          input: "$rainfallData",
          as: "r",
          in: {
            $mergeObjects: [ "$$r", { rfDateTime: { $toDate: "$$r.rfDateTime" } } ]
          }
        }
      }
    }
  },
  {
    $set: {
      waterLevel: {
        $filter: {
          input: "$waterLevel",
          as: "w",
          cond: {
            $and: [
              { $eq: [ "$$w.wlSeverity", "Danger" ] },
              { $eq: [ "$$w.wlDateTime", { $max: "$waterLevel.wlDateTime" } ] }
            ]
          }
        }
      },
      rainfallData: {
        $filter: {
          input: "$rainfallData",
          as: "r",
          cond: { $eq: [ "$$r.rfDateTime", { $max: "$rainfallData.rfDateTime" } ] }
        }
      }
    }
  }
])

mongoplayground

CodePudding user response：

but I just want the latest

I you are only interested in the latest docs you can omit the sort and instead use a natural negative cursor:

Meteor.publish('Alerts', function(){
  return AlertLatest.find({
    'waterLevelData.wlSeverity':'Danger',
  }, {
    fields : {
    'stationName'               : 1,
    'state'                     : 1,
    'lat'                       : 1,
    'long'                      : 1,
    'waterLevelData.wlDateTime' : 1,
    'waterLevelData.wlSeverity' : 1,
    'waterLevelData.wlLevel'    : 1,
    'rainfallData.rfSeverity'   : 1,      
  }},{ hint: { $natural: -1}});
})

It will start counting docs from the end, instead of the beginning.

https://docs.meteor.com/api/collections.html#Mongo-Collection-find