我需要恢复我的集合的最后一个值,但是我需要使用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
}
]
}
