I need to make a query in Redash that generates a Ranking that is usually simple I do an unwind of what I want and use a sort to leave in the descending order, however now I need to make a query that has several "matches" within the matchs check the strings and depending on the string add +1 in a certain variable that will appear in the Ranking:
{
"collection": "data",
"aggregate": [
{
"$unwind": "$data.produtos"
},
{
"$project": {
"_id": "$data.produtos.nome"
}
},
{
"$match": {
"_id": {
"$in": [
"biscoito",
"brinquedo",
"picape",
"moto",
"piscina"
]
}
}
},
{
"$group": {
"_id": "$_id",
"count": {
"$sum": 1
}
}
},
{
"$sort": [
{
"name": "count",
"direction": -1
}
]
}
]
}
In this example I basically need to do the following if the match is for the motorcycle or car, I put the variable that does not exist AUTOMOVEIS in the ranking and I'm adding in the counter