$DatefromString MongoDB & get documents-CodePudding

I am trying to get the 5 documents with the minimum humidity values in "location_id: 1". But only from Monday to Friday and from 8 a.m. to 6 p.m. The collection, db.datos_sensores2, has documents within like:

{
    "_id" : ObjectId("609c2c2d420a73728827e87f"),
    "timestamp" : ISODate("2020-07-01T02:15:00Z"),
    "sensor_id" : 1,
    "location_id" : 1,
    "medidas" : [
        {
            "tipo_medida" : "Temperatura",
            "valor" : 14.03,
            "unidad" : "ºC"
        },
        {
            "tipo_medida" : "Humedad_relativa",
            "valor" : 84.32,
            "unidad" : "%"
        }
    ]
}

{
    "_id" : ObjectId("609c2c2d420a73728827e880"),
    "timestamp" : ISODate("2020-07-01T02:15:00Z"),
    "sensor_id" : 2,
    "location_id" : 1,
    "medidas" : [
        {
            "tipo_medida" : "Emision_CO2",
            "valor" : 1.67,
            "unidad" : "gCO2/m2"
        },
        {
            "tipo_medida" : "Consumo_electrico",
            "valor" : 0.00155,
            "unidad" : "kWh/m2"
        }
    ]
}

I wrote this:

db.datos_sensores2.aggregate([
    {
       $project:{
              timestamp:{$dateFromString:{dateString:'$timestamp'}},"_id":0, "medidas":{$slice:["$medidas",-1]},"location_id":1}
    },
    {
      $add-Fields:{dia_semana:{$dayOfWeek:"$timestamp"},Hora:{$hour:"$timestamp"}}
    },
    {
      $match:{'medidas.tipo_medida': "Humedad", "location_id":1}
    },
    {
      $match:{$and:[{'dia_semana':{$in:[2,3,4,5,6]},'Hora':{$gt:8, $lt:18}}]}
    },
    {$sort:{"medidas.valor":1}},{$limit:5}])

But nothing happen....

**The Date field is String, that's why I am using $DatefromString

CodePudding user response：

I change your query and fix some thing first remove DatefromString

db.collection.aggregate([
  {
    $project: {
      timestamp: 1,
      _id: 0,
      "medidas": {
        $slice: [
          "$medidas",
          -1
        ]
      },
      "location_id": 1
    }
  },
  {
    $addFields: {
      dia_semana: {
        $dayOfWeek: "$timestamp"
      },
      Hora: {
        $hour: "$timestamp"
      }
    }
  },
  {
    $match: {
      "medidas.tipo_medida": "Humedad",
      "location_id": 1
    }
  },
  {
    $match: {
      $and: [
        {
          "dia_semana": {
            $in: [
              2,
              3,
              4,
              5,
              6
            ]
          },
          "Hora": {
            $gt: 8,
            $lt: 18
          }
        }
      ]
    }
  },
  {
    $sort: {
      "medidas.valor": 1
    }
  },
  {
    $limit: 5
  }
])

after inspecting your query this aggregation will return this

 {
        $project: {
          timestamp: 1,
          _id: 0,
          "medidas": {
            $slice: [
              "$medidas",
              -1
            ]
          },
          "location_id": 1
        }
      },
      {
        $addFields: {
          dia_semana: {
            $dayOfWeek: "$timestamp"
          },
          Hora: {
            $hour: "$timestamp"
          }
        }
      },

result :

[
  {
    "Hora": 2,
    "dia_semana": 4,
    "location_id": 1,
    "medidas": [
      {
        "tipo_medida": "Humedad_relativa",
        "unidad": "%",
        "valor": 84.32
      }
    ],
    "timestamp": ISODate("2020-07-01T02:15:00Z")
  },
  {
    "Hora": 2,
    "dia_semana": 4,
    "location_id": 1,
    "medidas": [
      {
        "tipo_medida": "Consumo_electrico",
        "unidad": "kWh/m2",
        "valor": 0.00155
      }
    ],
    "timestamp": ISODate("2020-07-01T02:15:00Z")
  }
]

and in this result we didn't add matches stages to pipeline and if you see your match didn't select any result for many reason

1- $match:{'medidas.tipo_medida': "Humedad"}

2- 'Hora':{$gt:8, $lt:18}} in your result before matches stages in aggregation you don't any result to match with these query I highlited