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'
}
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).
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