Good day. I have huge collection with next info (example):

{
    "_id" : ObjectId("1"),
    "symbol" : "AUDHKD",
    "digits" : 5,
    "times" : {
        "event_ts_utc" : ISODate("2021-10-17T23:59:59.405Z")
    },
    "prices" : {
        "bid" : 5.76648,
        "ask" : 5.76848
    }
}

{
    "_id" : ObjectId("2"),
    "symbol" : "AUDSGD",
    "digits" : 5,
    "times" : {
        "event_ts_utc" : ISODate("2021-10-17T23:59:59.406Z")
    },
    "prices" : {
        "bid" : 0.99932,
        "ask" : 1.00032
    }
}

{
    "_id" : ObjectId("3"),
    "symbol" : "AUDSGD",
    "digits" : 5,
    "times" : {
        "event_ts_utc" : ISODate("2021-10-17T23:59:59.618Z")
    },
    "prices" : {
        "bid" : 0.99932,
        "ask" : 1.00132
    }
}

{
    "_id" : ObjectId("4"),
    "symbol" : "AUDHKD",
    "digits" : 5,
    "times" : {
        "event_ts_utc" : ISODate("2021-10-17T23:59:59.720Z")
    },
    "prices" : {
        "bid" : 5.76648,
        "ask" : 5.76878
    }
}

I need to get all last records for yesterday for each symbol field. Means 1 record for each symbol that last for yesterday date. As result it must return next - most late record for date 2021-10-17 for all symbols:

{
    "_id" : ObjectId("3"),
    "symbol" : "AUDSGD",
    "digits" : 5,
    "times" : {
        "event_ts_utc" : ISODate("2021-10-17T23:59:59.618Z")
    },
    "prices" : {
        "bid" : 0.99932,
        "ask" : 1.00132
    }
}

{
    "_id" : ObjectId("4"),
    "symbol" : "AUDHKD",
    "digits" : 5,
    "times" : {
        "event_ts_utc" : ISODate("2021-10-17T23:59:59.720Z")
    },
    "prices" : {
        "bid" : 5.76648,
        "ask" : 5.76878
    }
}

Please, help to create correct query - idk how to put all requirements inside $aggregate.. Tried to solve this using next links https://docs.mongodb.com/v4.0/reference/method/db.collection.group/ https://docs.mongodb.com/manual/reference/operator/aggregation/group/ but do not understand how to merge group, sort and limit inside 1 query (

PS: indexes in collection are:

[
    {
        "v" : 2,
        "key" : {
            "_id" : 1
        },
        "name" : "_id_"
    },
    {
        "v" : 2,
        "key" : {
            "symbol" : 1.0
        },
        "name" : "idx_symbol",
        "background" : true
    },
    {
        "v" : 2,
        "key" : {
            "times.event_ts_utc" : 1.0
        },
        "name" : "idx_times.event_ts_utc",
        "background" : true
    }
]

Thank you.

CodePudding user response：

You can do the followings in the aggregation pipeline:

1. $match to your selected date range
2. $sort by "times.event_ts_utc": -1
3. $group by $symbol to get $first document, which is the latest record
4. wrangle back to expected form

db.collection.aggregate([
  {
    "$match": {
      "times.event_ts_utc": {
        $lt: ISODate("2021-10-18")
      }
    }
  },
  {
    $sort: {
      "times.event_ts_utc": -1
    }
  },
  {
    $group: {
      _id: "$symbol",
      latest: {
        $first: "$$ROOT"
      }
    }
  },
  {
    "$replaceRoot": {
      "newRoot": "$latest"
    }
  }
])

Here is the Mongo playground for your reference.