I am making a query in Grafana connected to a MongoDB database, but I have doubts about how to use the variables that are generated from a Mongo query. I have created a variable in Grafana from a MongoDB query, but I don’t know exactly what syntax I should use to reference that variable within my Grafana query. For example, if the variable is called $timeResolution, how should I write the condition in $match so that Grafana automatically replaces the value selected by the user? Should I use ‘$in’: [‘$timeResolution’] or some other special syntax? I would appreciate help in understanding how to integrate these variables correctly within my aggregation query in MongoDB.
Here is an example:
db.test_1.aggregate([
// Desenrollar el array de features para que cada feature se convierta en un documento separado
{ $unwind: “$PayloadGEOJSON.features” },
// Filtrar los documentos donde el nombre sea “G002”
{ $match: { “PayloadGEOJSON.features.properties.name”: “G002” } },
// Convertir las coordenadas a tipo double para cálculos precisos y aplicar timeResolution
{
$project: {
_id: 0,
cpLocationLatitude: { $toDouble: “$PayloadGEOJSON.features.properties.cpLocationLatitude” },
cpLocationLongitude: { $toDouble: “$PayloadGEOJSON.features.properties.cpLocationLongitude” },
time: {
$toDate: {
$multiply: [
{
$subtract: [
{
$toDouble: “$PayloadGEOJSON.features.properties.timeStamp”
},
{
$mod: [
{ $toDouble: “$PayloadGEOJSON.features.properties.timeStamp” },
{ $toDouble: “$__timeResolution” } // Aplicamos la resolución de tiempo
]
}
]
},
1000
]
}
}
}
},
// Agrupar para obtener el valor máximo y mínimo de las coordenadas
{
$group: {
_id: null,
maxLatitude: { $max: “$cpLocationLatitude” },
minLatitude: { $min: “$cpLocationLatitude” },
maxLongitude: { $max: “$cpLocationLongitude” },
minLongitude: { $min: “$cpLocationLongitude” }
}
},
// Proyectar los resultados en un formato adecuado
{
$project: {
_id: 0,
maxLatitude: 1,
minLatitude: 1,
maxLongitude: 1,
minLongitude: 1
}
}
])
I want to filter by the variable TimeResolution created as an Interval variable with these values “500ms,1s,15s,30s,1m,5m,30m,1h,12h”
How can I filter by this variable inside the query?