Question 118383 in Mongodb Question posted in
The official documentation can be found here.

Mongodb – Meteor Collection Find Documents Based on Latest Date Time in an Array

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.

UPDATE

I’ve tried the solution provided by @YuTing, which is using aggregate to customise the publication query. I went ahead and read a bit about Mongodb Aggregation, and found a Meteorjs community package (tunguska:reactive-aggregate) which simplifies the process.

This is the sample of a working aggregation so far:

Meteor.publish('PIBDataAlerts', function(){
      const start = dayjs().startOf('day'); // set to 12:00 am today
      const end = dayjs().endOf('day'); // set to 23:59 pm today
      ReactiveAggregate(this, PIBLatest, [
        {
          $match: {   
            'stationStatus' : 'ON',
            'waterLevelData': {        //trying to get only today's docs
                "$elemMatch" : {
                    "wlDateTime" : {
                        $gte: start.format()  , $lt: end.format()  
                    }
                }
            }
          }
        },
        {
          $set: {
            waterLevelHFZ: {
              $filter: {
                input: "$waterLevelData",
                as: "w",
                cond: {
                  $and: [
                    { $or : [
                      { $eq: [ "$$w.wlSeverity", "Alert" ] },
                      { $eq: [ "$$w.wlSeverity", "Warning" ] },
                      { $eq: [ "$$w.wlSeverity", "Danger" ] },
                    ]},
                    { $eq: [ "$$w.wlDateTime", { $max: "$waterLevelData.wlDateTime" } ] }
                  ],
                }
              }
            },
            rainfallDataHFZ: {
              $filter: {
                input: "$rainfallData",
                as: "r",
                cond: { $eq: [ "$$r.rfDateTime", { $max: "$rainfallData.rfDateTime" } ] }
              }
            }
          }
        },
        {
          $project : {
            "stationId": 1,
            "stationName" :1,
            "state": 1,
            "waterLevelHFZ": 1,
            "rainfallDataHFZ": 1
          }
        }
      ]);
    })

I’m struggling to get documents that only have the wlDateTime that equals today. I’ve tried a query in the $match but it returned empty array. If the $match is set to {}, it’ll return all 1548 records even though the wlDateTime is not equals to today.

Tags:

3

Answers


  1. 0

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

    Login or Signup to reply.
  2. 0
    1. change your date string to date
    2. filter the array to find the max one
    db.collection.aggregate([
  {
    $match: {
      $expr: {
        $or: [
          {
            $ne: [
              {
                $filter: {
                  input: "$waterLevel",
                  as: "w",
                  cond: {
                    $eq: [
                      {
                        $dateTrunc: {
                          date: {
                            $dateFromString: {
                              dateString: "$$w.wlDateTime",
                              format: "%d/%m/%Y %H:%M"
                            }
                          },
                          unit: "day"
                        }
                      },
                      {
                        $dateTrunc: {
                          date: "$$NOW",
                          unit: "day"
                        }
                      }
                    ]
                  }
                }
              },
              []
            ]
          },
          {
            $ne: [
              {
                $filter: {
                  input: "$rainfallData",
                  as: "r",
                  cond: {
                    $eq: [
                      {
                        $dateTrunc: {
                          date: {
                            $dateFromString: {
                              dateString: "$$r.rfDateTime",
                              format: "%d/%m/%Y %H:%M"
                            }
                          },
                          unit: "day"
                        }
                      },
                      {
                        $dateTrunc: {
                          date: "$$NOW",
                          unit: "day"
                        }
                      }
                    ]
                  }
                }
              },
              []
            ]
          }
        ]
      }
    }
  },
  {
    $set: {
      waterLevel: {
        $map: {
          input: "$waterLevel",
          as: "w",
          in: {
            $mergeObjects: [
              "$$w",
              {
                wlDateTime: {
                  $dateFromString: {
                    dateString: "$$w.wlDateTime",
                    format: "%d/%m/%Y %H:%M"
                  }
                }
              }
            ]
          }
        }
      },
      rainfallData: {
        $map: {
          input: "$rainfallData",
          as: "r",
          in: {
            $mergeObjects: [
              "$$r",
              {
                rfDateTime: {
                  $dateFromString: {
                    dateString: "$$r.rfDateTime",
                    format: "%d/%m/%Y %H:%M"
                  }
                }
              }
            ]
          }
        }
      }
    }
  },
  {
    $set: {
      waterLevel: {
        $filter: {
          input: "$waterLevel",
          as: "w",
          cond: {
            $and: [
              {
                $in: [
                  "$$w.wlSeverity",
                  [
                    "Alert",
                    "Warning",
                    "Danger"
                  ]
                ]
              },
              {
                $eq: [
                  "$$w.wlDateTime",
                  {
                    $max: "$waterLevel.wlDateTime"
                  }
                ]
              },
              {
                $eq: [
                  {
                    $dateTrunc: {
                      date: "$$w.wlDateTime",
                      unit: "day"
                    }
                  },
                  {
                    $dateTrunc: {
                      date: "$$NOW",
                      unit: "day"
                    }
                  }
                ]
              }
            ]
          }
        }
      },
      rainfallData: {
        $filter: {
          input: "$rainfallData",
          as: "r",
          cond: {
            $and: [
              {
                $eq: [
                  "$$r.rfDateTime",
                  {
                    $max: "$rainfallData.rfDateTime"
                  }
                ]
              },
              {
                $eq: [
                  {
                    $dateTrunc: {
                      date: "$$r.rfDateTime",
                      unit: "day"
                    }
                  },
                  {
                    $dateTrunc: {
                      date: "$$NOW",
                      unit: "day"
                    }
                  }
                ]
              }
            ]
          }
        }
      }
    }
  }
])

    mongoplayground

    Login or Signup to reply.
  3. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search