I am trying to translate a query from SQL Sever into a query for MongoDB. The table and the collection have the same data and structure at this time.
The final query in SQL Server looks like this. This query has subqueries and due to this and the use of ROW_NUMBER () to get the expected result I have not been able to replicate the same result in MongoDb.
FULL SQL Server Query
SELECT CodigoNodo, NivelTension, cantidad
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY CodigoNodo ORDER BY cantidad DESC) AS Top_niveltesion
FROM (
SELECT CodigoNodo, NivelTension,COUNT(NivelTension) as cantidad
FROM QEnergia.FronteraInstalaciones
WHERE IdentificadorEmpresa=1
GROUP BY CodigoNodo,NivelTension
) SubconsultaInterna
) Subconsulta
WHERE Top_niveltesion = 1
ORDER BY CodigoNodo;
To understand the result easier, here I show the result of this segment of the query.
SELECT *,
ROW_NUMBER() OVER (PARTITION BY CodigoNodo ORDER BY cantidad DESC) AS Top_niveltesion
FROM (
SELECT CodigoNodo, NivelTension,COUNT(NivelTension) as cantidad
FROM QEnergia.FronteraInstalaciones
WHERE IdentificadorEmpresa=1
GROUP BY CodigoNodo,NivelTension
) SubconsultaInterna
Result
+------------+--------------+----------+-----------------+
| CodigoNodo | NivelTension | cantidad | Top_niveltesion |
+------------+--------------+----------+-----------------+
| 100001 | 2 | 15 | 1 |
| 100001 | 4 | 13 | 2 |
| 100001 | 3 | 13 | 3 |
| 100001 | 1 | 7 | 4 |
| 100002 | 2 | 20 | 1 |
| 100002 | 1 | 14 | 2 |
| 100002 | 3 | 12 | 3 |
| 100002 | 4 | 9 | 4 |
| 100003 | 3 | 10 | 1 |
| 100003 | 2 | 9 | 2 |
| 100003 | 1 | 5 | 3 |
| 100003 | 4 | 4 | 4 |
| 100004 | 4 | 1 | 1 |
| 100005 | 4 | 7 | 1 |
| 100005 | 2 | 7 | 2 |
| 100005 | 3 | 4 | 3 |
| 100005 | 1 | 2 | 4 |
| 100006 | 3 | 9 | 1 |
| 100006 | 2 | 7 | 2 |
| 100006 | 4 | 1 | 3 |
+------------+--------------+----------+-----------------+
And this is the final result of the full query
+------------+--------------+----------+
| CodigoNodo | NivelTension | cantidad |
+------------+--------------+----------+
| 100001 | 2 | 15 |
| 100002 | 2 | 20 |
| 100003 | 3 | 10 |
| 100004 | 4 | 1 |
| 100005 | 2 | 7 |
| 100006 | 3 | 9 |
| 100007 | 2 | 13 |
| 100008 | 2 | 20 |
| 100009 | 2 | 19 |
| 10001 | 3 | 15 |
| 100010 | 3 | 41 |
| 100011 | 3 | 22 |
| 100012 | 2 | 11 |
| 100013 | 2 | 20 |
| 100014 | 3 | 28 |
| 100015 | 2 | 15 |
| 100016 | 3 | 24 |
| 100017 | 2 | 37 |
| 100018 | 2 | 30 |
| 100019 | 3 | 22 |
+------------+--------------+----------+
In this way we see that the final result obtains the record of the maximum value for the calculated field "cantidad" grouped just by CodigoNodo.
The help that I would like to obtain is how I can put in the final result the value of the field "NivelTension" since it is not in the grouping and I have not been able to replicate it in MongoDB.
Currently this is the query that I have developed so far.
db.getCollection("FronteraInstalaciones").aggregate(
[
{
"$match" : { "IdentificadorEmpresa":1 }
},
{
"$group" : {
"_id" : {
"CodigoNodo" : "$CodigoNodo",
"NivelTension" : "$NivelTension"
},
"cantidad" : {
$sum : 1
}
}
},
{
"$group" : {
"_id" : {
"CodigoNodo" : "$_id.CodigoNodo"
},
"maximo" : { $max : "$cantidad" }
}
},
{
"$project" : {
"_id" : false,
"CodigoNodo" : "$_id.CodigoNodo",
"Maximo" : "$maximo"
}
},
{
"$sort" : {
"CodigoNodo" : 1
}
}
],
{
"allowDiskUse" : true
}
);
I have obtained this result which is correct but I cannot show the field "NivelTension" related to the "Maximo" field ($max: "$cantidad") as if it happens in the SQL query.
{
"CodigoNodo" : "100001",
"Maximo" : 15.0
}
{
"CodigoNodo" : "100002",
"Maximo" : 20.0
}
{
"CodigoNodo" : "100003",
"Maximo" : 10.0
}
{
"CodigoNodo" : "100004",
"Maximo" : 1.0
}
{
"CodigoNodo" : "100005",
"Maximo" : 7.0
}
{
"CodigoNodo" : "100006",
"Maximo" : 9.0
}
{
"CodigoNodo" : "100007",
"Maximo" : 13.0
}
{
"CodigoNodo" : "100008",
"Maximo" : 20.0
}
{
"CodigoNodo" : "100009",
"Maximo" : 19.0
}
{
"CodigoNodo" : "10001",
"Maximo" : 15.0
}
{
"CodigoNodo" : "100010",
"Maximo" : 41.0
}
{
"CodigoNodo" : "100011",
"Maximo" : 22.0
}
{
"CodigoNodo" : "100012",
"Maximo" : 11.0
}
{
"CodigoNodo" : "100013",
"Maximo" : 20.0
}
{
"CodigoNodo" : "100014",
"Maximo" : 28.0
}
{
"CodigoNodo" : "100015",
"Maximo" : 15.0
}
{
"CodigoNodo" : "100016",
"Maximo" : 24.0
}
{
"CodigoNodo" : "100017",
"Maximo" : 37.0
}
{
"CodigoNodo" : "100018",
"Maximo" : 30.0
}
{
"CodigoNodo" : "100019",
"Maximo" : 22.0
}
Thx guys for your help and sorry for the long post.
You need to add one $sort stage between your two $group stages as follows. Second $group uses $first accumulator.
db.getCollection("FronteraInstalaciones").aggregate(
[
................
{
"$group" : {
"_id" : {
"CodigoNodo" : "$CodigoNodo",
"NivelTension" : "$NivelTension"
},
"cantidad" : {
$sum : 1
}
}
},
{
"$sort":{
"_id.CodigoNodo": 1,
"cantidad": -1
}
},
{
"$group" : {
"_id" : {
"CodigoNodo" : "$_id.CodigoNodo"
},
"NivelTension":{$first:"$_id.NivelTension"}
"maximo" : { $max : "$cantidad" },
}
},
..............
]
)