How can I filter documents from a collection by a string type field named "fecha_proximo_mantenimiento" (next_date_maintenance)?

user_12144521 0
Viewed 61

I have a collection called "inventory_devices" and from this I need to get the documents that are close to maintenance (usually 30 days before the next maintenance date) and at the moment I am trying to filter the documents that are in the field "date_close_to_maintenance" 30 days before the date and I can't find the way to do it. "maintenance_next_date" is String type. I currently use the Mongoose library for MongoDB queries in NodeJS.

The variable/field "contract" is the common identifier that the documents have and it is the one that serves me to group them. Each X number of documents have a contract associated, something like a "Customer type" of a customer table.

The model has the following, I will show the data that are used only because the model could take 300 rows:

const mongoose = require('mongoose')
    
    const Schema = mongoose.Schema
    const InventarioDispositivosSchema = new mongoose.Schema({
        contrato: {
            type: Schema.Types.ObjectId,
                ref: 'Contrato'
            },
      informacion_equipo: {
            type: Object,
            require: ["sistema_operativo", "ip", "nombre_equipo", "usuario_red"],
            properties: {
      ususario_red: { //Este campo y el modelo en sí funciona como está
                    type: String,
                    required: false,
                    trim: true,
                },
        },
    },fecha_proximo_mantenimiento: {
        type: String,
        required: false,
        trim: true,
    }, { collection: 'inventario_dispositivos', timestamps: true })

const InventarioDispositivos = mongoose.model('InventarioDispositivos', InventarioDispositivosSchema)

module.exports = InventarioDispositivos

I have the following query:

// With "contrato" burned

let contrato = 'contrato1';

 Dispositivos.find(
  {
    //Filtros que se le hacen a la consulta 
    contrato,
    fecha_proximo_mantenimiento: {   
      $exists: true
    }
  },
  {
    //Datos que quiero que se muestren, solo el ID se oculta.
    _id: 0,
    'informacion_equipo.ususario_red': 1,
    fecha_proximo_mantenimiento: 1
  }
).limit(10).then((devices) => {
  console.log(devices)
}).catch(err => {
  console.log(err)
})

A sample of the model shown and what the above query returns:

 {
    informacion_equipo: { ususario_red: 'angel.goez' },
    fecha_proximo_mantenimiento: '2021-08-30'
  },
  {
    informacion_equipo: { ususario_red: 'angel.goez' },
    fecha_proximo_mantenimiento: '2021-08-25'
  }
mongoose node.js mongodb filter javascript
2 Answers

Hopefully I understood your question correctly.

The query below will filter documents that have fecha_proximo_mantenimiento with value older than 30 days.

[
  {
    "$addFields": {
      "fecha_proximo_mantenimiento_timestamp": {
        "$subtract": [
          "$$NOW",
          {
            "$toDate": "$fecha_proximo_mantenimiento"
          }
        ]
      }
    }
  },
  {
    "$match": {
      "fecha_proximo_mantenimiento_timestamp": {
        "$gte": 1000 * 60 * 60 * 24 * 30
      }
    }
  }
]

First stage $addFields adds new field (I called same as original with appended _timestamp and it subtracts field value (which is also converted to Date, since your schema defined that as String) from Date.

Second stage $match checks that subtracted result (in miliseconds) is greater than 30 days (you can also replace with 2592000000 if it's easier for you).

user_38848520 
const hasta = moment(new Date()).add(30, 'days').format('YYYY-MM-DD');

const desde = moment(new Date()).format('YYYY-MM-DD');

const contrato= 0;

Dispositivos.find(
  contrato,
  fecha_proximo_mantenimiento: { 
    $exists: true 
  }
},
  {
    _id: 0,
    'informacion_equipo.ususario_red': 1,
    fecha_proximo_mantenimiento: 1
  })
  .where('fecha_proximo_mantenimiento')
  .gte(desde)
  .lte(hasta)
  .exec(function (err, result) {
    if (err) {
      console.log(err)
    } else {
      for (var x in result) {
        console.log('numero de documento: ', x, result[x])
      }
    }
  });

It works too with find

user_121445210
Related