node.js - MongoDB。通过一次查询获取每种产品不同时间段的价格

Question

我在正确查询 MongoDB 时遇到一些问题。我有很多关于每种产品不同时间的价格的文件。更新来自第 3 方服务，每个产品几乎每秒都会实时更新。有时，我在一秒钟内更新 2-3 次价格，或者对于某些产品，每 5-10 秒更新 1 次。

{ "_id": ObjectID("5a4f51ec4f1bdb02f6c82833"), "name": orange", "price": 1.16, "date": ISODate("2018-01-05T09:22:37.983Z"), "unix_date": 1515147756642}
{ "_id": ObjectID("5a4f51eb4f1bdb02f6c82828"), "name": orange", "price": 1.17, "date": ISODate("2018-01-05T09:22:37.530Z"), "unix_date": 1515147755738}
{ "_id": ObjectID("5a4f51ea4f1bdb02f6c8281a"), "name": orange", "price": 1.16, "date": ISODate("2018-01-05T09:22:36.123Z"), "unix_date": 1515147754489}
{ "_id": ObjectID("5a4f51e84f1bdb02f6c82805"), "name": orange", "price": 1.15, "date": ISODate("2018-01-05T09:22:33.937Z"), "unix_date": 1515147752334}
{ "_id": ObjectID("5a4f51e34f1bdb02f6c827d2"), "name": orange", "price": 1.15, "date": ISODate("2018-01-05T09:22:28.890Z"), "unix_date": 1515147747411}

{ "_id": ObjectID("5a4f51ea4f1bdb02f6c82818"), "name": apricot", "price": 2.73, "date": ISODate("2018-01-05T09:22:36.640Z"), "unix_date": 1515147754487}
{ "_id": ObjectID("5a4f51e84f1bdb02f6c82806"), "name": apricot", "price": 2.66, "date": ISODate("2018-01-05T09:22:33.623Z"), "unix_date": 1515147752335}
{ "_id": ObjectID("5a4f51e74f1bdb02f6c827fc"), "name": apricot", "price": 2.59, "date": ISODate("2018-01-05T09:22:33.577Z"), "unix_date": 1515147751196}
{ "_id": ObjectID("5a4f51e34f1bdb02f6c827d7"), "name": apricot", "price": 2.46, "date": ISODate("2018-01-05T09:22:28.967Z"), "unix_date": 1515147747444}
{ "_id": ObjectID("5a4f51dc4f1bdb02f6c8278d"), "name": apricot", "price": 2.51, "date": ISODate("2018-01-05T09:22:22.530Z"), "unix_date": 1515147740183}

{ "_id": ObjectID("5a4f51de4f1bdb02f6c827aa"), "name": melon", "price": 10.1, "date": ISODate("2018-01-05T09:22:22.380Z"), "unix_date": 1515147742511}
{ "_id": ObjectID("5a4f51dd4f1bdb02f6c8279e"), "name": melon", "price": 10.1, "date": ISODate("2018-01-05T09:22:20.830Z"), "unix_date": 1515147741391}
{ "_id": ObjectID("5a4f51dc4f1bdb02f6c82793"), "name": melon", "price": 10.2, "date": ISODate("2018-01-05T09:22:20.113Z"), "unix_date": 1515147740250}
{ "_id": ObjectID("5a4f51db4f1bdb02f6c82782"), "name": melon", "price": 10.1, "date": ISODate("2018-01-05T09:22:18.597Z"), "unix_date": 1515147739137}
{ "_id": ObjectID("5a4f51d64f1bdb02f6c8274c"), "name": melon", "price": 10.0, "date": ISODate("2018-01-05T09:22:12.983Z"), "unix_date": 1515147734131}

{ "_id": ObjectID("5a4f51d34f1bdb02f6c82729"), "name": cherry", "price": 6.21, "date": ISODate("2018-01-05T09:22:12.810Z"), "unix_date": 1515147731148}
{ "_id": ObjectID("5a4f51c94f1bdb02f6c826b5"), "name": cherry", "price": 6.23, "date": ISODate("2018-01-05T09:22:03.733Z"), "unix_date": 1515147721637}
{ "_id": ObjectID("5a4f51c84f1bdb02f6c826a3"), "name": cherry", "price": 6.24, "date": ISODate("2018-01-05T09:22:02.263Z"), "unix_date": 1515147720394}
{ "_id": ObjectID("5a4f51c74f1bdb02f6c82695"), "name": cherry", "price": 6.24, "date": ISODate("2018-01-05T09:22:00.763Z"), "unix_date": 1515147719464}
{ "_id": ObjectID("5a4f51c14f1bdb02f6c82661"), "name": cherry", "price": 6.27, "date": ISODate("2018-01-05T09:21:55.233Z"), "unix_date": 1515147713322}

{ "_id": ObjectID("5a4f51c44f1bdb02f6c8267c"), "name": apple", "price": 1.72, "date": ISODate("2018-01-05T09:21:56.343Z"), "unix_date": 1515147716082}
{ "_id": ObjectID("5a4f51b94f1bdb02f6c8261e"), "name": apple", "price": 1.71, "date": ISODate("2018-01-05T09:21:46.687Z"), "unix_date": 1515147705949}
{ "_id": ObjectID("5a4f51b94f1bdb02f6c8260e"), "name": apple", "price": 1.75, "date": ISODate("2018-01-05T09:21:45.030Z"), "unix_date": 1515147705022}
{ "_id": ObjectID("5a4f51b54f1bdb02f6c825f8"), "name": apple", "price": 1.76, "date": ISODate("2018-01-05T09:21:42.110Z"), "unix_date": 1515147701643}
{ "_id": ObjectID("5a4f51b34f1bdb02f6c825e0"), "name": apple", "price": 1.77, "date": ISODate("2018-01-05T09:21:40.483Z"), "unix_date": 1515147699844}

主要思想是每 5-10 秒向 MongoDB 进行一次查询，以获取实际价格以及此请求之前的价格(10 秒前、30 秒前、100 万年前、500 万年前、1500 万年前)。假设我在 10:00:00 发出请求，我想获取该产品的最后价格、10 秒前的最后价格(09:59:50)、30 秒前的最后价格(09:59:30)等等。

{ "_id" : "orange", "price" : 1.16, "price_10s" : 1.15, "price_30s" : 1.15, "price_1m" : 1.13, "price_5m" : 1.10, "price_15m" : 1.07 }
{ "_id" : "apricot", "price" : 2.73, "price_10s" : 2.51, "price_30s" : 2.48, "price_1m" : 2.40, "price_5m" : 2.41, "price_15m" : 2.35 }
{ "_id" : "melon", "price" : 10.1, "price_10s" : 10.0, "price_30s" : 10.2, "price_1m" : 10.4, "price_5m" : 10.3, "price_15m" : 10.3 }
{ "_id" : "cheryy", "price" : 6.21, "price_10s" : 6.25, "price_30s" : 6.24, "price_1m" : 6.22, "price_5m" : 6.01, "price_15m" : 5.99 }
{ "_id" : "apple", "price" : 1.72, "price_10s" : 1.75, "price_30s" : 1.75, "price_1m" : 1.74, "price_5m" : 1.70, "price_15m" : 1.67 }

通过此查询，我只能获取每种产品的最后价格:

food.aggregate([
    {
        $group: {
            _id: "$name",
            price: {$last:"$price"},
            date: {$last:"$date"}
        }
    }
]}

我还可以使用 $match 提出请求，并获取最后的实际价格，即 10 分钟前(或其他时间间隔)。

var today = new Date();
var shift_10m = new Date(today.getTime() - 10*60*1000);

food.aggregate([
    {
        $match: {
            date: { "$lte": shift_10m }
        }
    }, {
        $group: {
            _id: "$name",
            price: {$last:"$price"},
            date: {$last:"$date"}
        }
    }
]);

但我想在一个查询中获得每种食物的结果以及不同时期的价格。可能吗？

我的MongoDB版本是v3.6.0。我在 Node.js 应用程序中使用 mongoose。

Answer 1

使用$push

food.aggregate([
    {
        $group: {
            _id: "$name",
            items: { $push:  { price: "$price", date: "$date" } }
        }
    }
]}

有关 docs 中的 push 的更多信息