[
  {
    "_id": "",
    "at": IsoDate(2022-11-19 10:00:00),
    "areaId": 3,
    "data": [
      {
        "name": "a",
        "sec": 34,
        "x": 10.3,
        "y": 23.3
      },
      {
        "name": "a",
        "sec": 36,
        "x": 10.3,
        "y": 23.3
      },
      {
        "name": "b",
        "sec": 37,
        "x": 10.3,
        "y": 23.3
      }
      ]
  },
  {
    "_id": "",
    "at": IsoDate(2022-11-19 10:00:00),
    "areaId": 3,
    "data": [
      {
        "name": "a",
        "sec": 10,
        "x": 10.3,
        "y": 23.3
      },
      {
        "name": "b",
        "sec": 12,
        "x": 10.3,
        "y": 23.3
      }
      ]
  }
  ]

I have a table that indicates in what seconds people are in which area in packets of minutes. My goal here is to find the date the person with the specified name was last in the field with the most performance.

Can you help me for this?

Example output: Last date 'a' was found in polygon=3

2022-11-19 10:01:10 (with sec)

CodePudding user response：

One option is:

1. Use the first 3 steps to find the matching document
2. The 4th step is to add the seconds to to the at date

db.collection.aggregate([
  {$match: {data: {$elemMatch: {name: inputName}}}},
  {$sort: {at: -1}},
  {$limit: 1},
  {$project: {
      res: {
        $dateAdd: {
          startDate: "$at",
          unit: "second",
          amount: {$reduce: {
              input: "$data",
              initialValue: 0,
              in: {$cond: [
                  {$eq: ["$$this.name", inputName]},
                  {$max: ["$$this.sec", "$$value"]},
                  "$$value"
              ]}
          }}
        }
      }
  }}
])

See how it works on the playground example