代码之家 › 专栏 › 技术社区 › Manolait

使用聚合检索最新MongoDB值

aggregation-framework mongoose mongodb

Manolait · 技术社区 · 7 年前

我需要恢复我的集合的最后一个值,但是我需要使用MongoDB聚合来完成它,因为我与另一个集合有一个联接。

这是我的查询代码:

我通过一个带传感器的阵列,我需要恢复。

function getDataSensorGreenhousetypeFechasJoin (req, res){
  var array = req.params.nombresensores;
  var arr = array.split(',');
  var id_greenhouse = req.params.id_greenhouse;

  Datagreenhouse.aggregate([
  {"$match":{"attrName":{"$in":arr}}},
  {"$sort":{"recvTime":-1}},
  {"$lookup":{
    "from":"sensors",
    "localField":"attrName", // local field in measurements collection
    "foreignField":"name", //foreign field from sensors collection
    "as":"sensor"
  }},
  {"$unwind":"$sensor"},
  {"$addFields":{
    "sensor.attrName":"$attrName",// Add attrName to the sensors
    "sensor.attrValue":"$attrValue",// Add attrValue to the sensors
    "sensor.recvTimeTs":"$recvTimeTs",
    "sensor.recvTime":"$recvTime"
  }},
  {"$group":{
    "_id":"$attrName", // Group by time
    "medidas":{"$push":"$sensor"}, // Collect measurements
    "count":{"$sum":1} // Count measurements
  }},
  {"$limit":1}
  ], (err, DatagreenhouseRecuperado) => {
    if (err) return res.status(500).send({message: 'Error al realizar la peticion' + err})
    if (!DatagreenhouseRecuperado) return res.status(404).send({message: 'Error el usuario no existe'})
    res.status(200).send({DatagreenhouseRecuperado})
    })
}

结果是:

{
        "DatagreenhouseRecuperado": [
            {
                "_id": "457",
                "medidas": [
                    {
                        "_id": "5bab51c69a3969055b6bf344",
                        "name": "42-457",
                        "type": "clima",
                        "place": "interior",
                        "img": "assets/img/contrast.png",
                        "name_comun": "RadiaciÃ³n solar",
                        "medida": "W/mÂ²",
                        "usuario": "5bab4aa69a3969055b6bf334",
                        "invernadero": "5bab4dbb9a3969055b6bf339",
                        "__v": 0,
                        "attrName": "42-457",
                        "attrValue": 5.215478125,
                        "recvTimeTs": 1538047310,
                        "recvTime": "2018-09-27T11:21:50.000Z"
                    },
                    {
                        "_id": "5bab4e4d9a3969055b6bf33a",
                        "name": "3-457",
                        "type": "clima",
                        "place": "interior",
                        "img": "assets/img/hum.png",
                        "name_comun": "Hum. Relativa",
                        "medida": "%",
                        "usuario": "5bab4aa69a3969055b6bf334",
                        "invernadero": "5bab4dbb9a3969055b6bf339",
                        "__v": 0,
                        "attrName": "3-457",
                        "attrValue": 57.6,
                        "recvTimeTs": 1538047193,
                        "recvTime": "2018-09-27T11:19:53.000Z"
                    },
                    {
                        "_id": "5bab511e9a3969055b6bf33f",
                        "name": "5-457",
                        "type": "clima",
                        "place": "interior",
                        "img": "assets/img/temp.png",
                        "name_comun": "Temp. Suelo",
                        "medida": "ÂºC",
                        "usuario": "5bab4aa69a3969055b6bf334",
                        "invernadero": "5bab4dbb9a3969055b6bf339",
                        "__v": 0,
                        "attrName": "5-457",
                        "attrValue": 29.222,
                        "recvTimeTs": 1537984323,
                        "recvTime": "2018-09-26T17:52:03.000Z"
                    },
                    {
                        "_id": "5bab51c69a3969055b6bf344",
                        "name": "42-457",
                        "type": "clima",
                        "place": "interior",
                        "img": "assets/img/contrast.png",
                        "name_comun": "RadiaciÃ³n solar",
                        "medida": "W/mÂ²",
                        "usuario": "5bab4aa69a3969055b6bf334",
                        "invernadero": "5bab4dbb9a3969055b6bf339",
                        "__v": 0,
                        "attrName": "42-457",
                        "attrValue": 137.137,
                        "recvTimeTs": 1537954419,
                        "recvTime": "2018-09-26T09:33:39.000Z"
                    }
                ],
                "count": 24
            }
        ]
    }

它不正确,因为它显示的所有内容和值都具有相同的名称和不同的日期。

我需要每个传感器的最后一个值。

如何完成查询优化?

谢谢您。

>编辑01

我试过几次了,

有了这个代码,如果我得到了我需要的,但是消除了连接的一部分,并且需要有其他字段,那么我的问题是,如何用这个代码完成连接的一部分呢?

{"$match":{"attrName":{"$in":arr}}},
  {"$sort":{"recvTime":-1}},
  {"$lookup":{
    "from":"sensors",
    "localField":"attrName", // local field in measurements collection
    "foreignField":"name", //foreign field from sensors collection
    "as":"sensor"
  }},
  {"$unwind":"$sensor"},
  {"$addFields":{
    "sensor.attrName":"$attrName",// Add attrName to the sensors
    "sensor.attrValue":"$attrValue",// Add attrValue to the sensors
    "sensor.recvTimeTs":"$recvTimeTs",
    "sensor.recvTime":"$recvTime"
  }},
  {"$group":{
    "_id":"$attrName", // Group by time
    "attrName":{$last:"$attrName"},
    "attrValue":{$last:"$attrValue"},
    "recvTime":{$last:"$recvTime"},
    "medidas":{"$push":"$sensor",}, // Collect measurements
    "count":{"$sum":1} // Count measurements
  }},

我的推力和传感器有问题

编辑02

嗨,我的答案是这样的:

我只需要你按日期显示传感器列表的最后一个值。

我不知道这些信息是否足够。在按日期进行聚合之前,问题是在不同的时间保存的某些数据不会出现,因此,我需要显示传感器最后保存的数据。

{
        "DatagreenhouseRecuperado": [
            {
                "_id": "457",
                "medidas": [
                    {
                        "_id": "5bab51c69a3969055b6bf344",
                        "name": "42-457",
                        "type": "clima",
                        "place": "interior",
                        "img": "assets/img/contrast.png",
                        "name_comun": "RadiaciÃ³n solar",
                        "medida": "W/mÂ²",
                        "usuario": "5bab4aa69a3969055b6bf334",
                        "invernadero": "5bab4dbb9a3969055b6bf339",
                        "__v": 0,
                        "attrName": "42-457",
                        "attrValue": 5.215478125,
                        "recvTimeTs": 1538047310,
                        "recvTime": "2018-09-27T11:21:50.000Z"
                    },
                    {
                        "_id": "5bab51529a3969055b6bf340",
                        "name": "17-457",
                        "type": "clima",
                        "place": "interior",
                        "img": "assets/img/contrast.png",
                        "name_comun": "Rad. Solar PAR",
                        "medida": "W/mÂ²",
                        "usuario": "5bab4aa69a3969055b6bf334",
                        "invernadero": "5bab4dbb9a3969055b6bf339",
                        "__v": 0,
                        "attrName": "17-457",
                        "attrValue": 112.165625,
                        "recvTimeTs": 1538047193,
                        "recvTime": "2018-09-27T11:19:53.000Z"
                    },
                    {
                        "_id": "5bab519d9a3969055b6bf342",
                        "name": "21-457",
                        "type": "clima",
                        "place": "interior",
                        "img": "assets/img/co264.png",
                        "name_comun": "CO2",
                        "medida": "ppm",
                        "usuario": "5bab4aa69a3969055b6bf334",
                        "invernadero": "5bab4dbb9a3969055b6bf339",
                        "__v": 0,
                        "attrName": "21-457",
                        "attrValue": 827.6,
                        "recvTimeTs": 1538047193,
                        "recvTime": "2018-09-27T11:19:53.000Z"
                    },
                    {
                        "_id": "5bab511e9a3969055b6bf33f",
                        "name": "5-457",
                        "type": "clima",
                        "place": "interior",
                        "img": "assets/img/temp.png",
                        "name_comun": "Temp. Suelo",
                        "medida": "ÂºC",
                        "usuario": "5bab4aa69a3969055b6bf334",
                        "invernadero": "5bab4dbb9a3969055b6bf339",
                        "__v": 0,
                        "attrName": "5-457",
                        "attrValue": 27.844,
                        "recvTimeTs": 1538047193,
                        "recvTime": "2018-09-27T11:19:53.000Z"
                    },
                    {
                        "_id": "5bab4e4d9a3969055b6bf33a",
                        "name": "3-457",
                        "type": "clima",
                        "place": "interior",
                        "img": "assets/img/hum.png",
                        "name_comun": "Hum. Relativa",
                        "medida": "%",
                        "usuario": "5bab4aa69a3969055b6bf334",
                        "invernadero": "5bab4dbb9a3969055b6bf339",
                        "__v": 0,
                        "attrName": "3-457",
                        "attrValue": 57.6,
                        "recvTimeTs": 1538047193,
                        "recvTime": "2018-09-27T11:19:53.000Z"
                    },
                    {
                        "_id": "5bab4f009a3969055b6bf33b",
                        "name": "2-457",
                        "type": "clima",
                        "place": "interior",
                        "img": "assets/img/temp.png",
                        "name_comun": "Temp. Ambiente",
                        "medida": "ÂºC",
                        "usuario": "5bab4aa69a3969055b6bf334",
                        "invernadero": "5bab4dbb9a3969055b6bf339",
                        "__v": 0,
                        "attrName": "2-457",
                        "attrValue": 32.2,
                        "recvTimeTs": 1538047193,
                        "recvTime": "2018-09-27T11:19:53.000Z"
                    }
                ],
                "count": 24
            }
        ]
    }

我基于这个例子,因为它是相同的,只是它没有连接

Ejemplo

编辑03

它只显示最后一个值,工作正常,但我只需要在开始处显示一个ID,以及上面结构中对象中的度量值。

这是你告诉我的我添加的代码的一部分

{"$group":{
    "_id":"$id_greenhouse",
    "latest":{"$first":"$$ROOT"},
  }},
 {"$project":{
    "_id":0,
    "id_greenhouse":"$latest.id_greenhouse",
    "attrName":"$latest.attrName",
    "attrValue":"$latest.attrValue",
    "recvTimeTs":"$latest.recvTimeTs",
    "recvTime":"$latest.recvTime"
  }},

结果

    {
    "DatagreenhouseRecuperado": [
        {
            "_id": "457",
            "medidas": [
                {
                    "_id": "5bab50f09a3969055b6bf33e",
                    "name": "6-457",
                    "type": "fertigation",
                    "place": "interior",
                    "img": "assets/img/hum.png",
                    "name_comun": "Humedad del Suelo",
                    "medida": "%",
                    "usuario": "5bab4aa69a3969055b6bf334",
                    "invernadero": "5bab4dbb9a3969055b6bf339",
                    "__v": 0,
                    "attrName": "6-457",
                    "attrValue": 39.4,
                    "recvTimeTs": 1537954419,
                    "recvTime": "2018-09-28T09:33:39.000Z",
                    "id_greenhouse": "457"
                }
            ],
            "count": 1
        }
    ]
}

1 回复 | 直到 7 年前

s7vr 7 年前

添加一个 $group + $project 之后的阶段 $sort 以及之前 $lookup 阶段。

有点像

 {"$group":{
    "_id":"$attrName",
    "latest":{"$first":"$$ROOT"},
  }},
 {"$project":{
    "_id":0,
    "id_greenhouse":"$latest._id",
    "attrName":"$latest.attrName",
    "attrValue":"$latest.attrValue",
    "recvTimeTs":"$latest.recvTimeTs",
    "recvTime":"$latest.recvTime"
  }}