How i can do mongodb group by query with like sql over by PARTITION ?
{
"id": "...",
"section": "x"
"items": [
{
"id": 1,
"count": 10,
}, {
"id": 2,
"count": 20,
}
]
},
{
"id": "...",
"section": "x"
"items": [
{
"id": 1,
"count": 100,
}, {
"id": 2,
"count": 200,
}
]
}
db.c.aggregate([
{ $unwind: "$items"},
{ $group :
{
_id : "$items.id",
SumCount: { $sum: "$items.count" },
}
}
]);
Now result:
1 | 110
2 | 220
But need add column count by field ‘section’ like in sql :
COUNT(section) OVER (PARTITION BY section) AS [COUNT_Section]
Need result:
1 | 110 | 2 -- two document with section:'x'
2 | 220 | 2
any news , idea, variants ?
mb question is wrong or something is not clear?
slava
(Slava)
July 20, 2020, 8:03am
4
Hello, @alexov_inbox !
You just need to sum up the documents in your $group stage. Like this:
db.c.aggregate([
{
$unwind: '$items',
},
{
$group: {
_id: '$items.id',
sumCount: {
$sum: '$items.count',
},
totalDocs: {
$sum: 1,
}
}
}
]);
The output will be:
[
{ "_id" : 2, "sumCount" : 220, "totalDocs" : 2 },
{ "_id" : 1, "sumCount" : 110, "totalDocs" : 2 }
]
its ‘live hack’ not real over by partition
because if nested array contain two items with equal keys , will return “totalDocs” : 3 , but documents with section ‘x’ = 2
{
"id": "...",
"section": "x"
"items": [
{
"id": 1,
"count": 10,
},
{
"id": 1,
"count": 10,
}, {
"id": 2,
"count": 20,
}
]
},
slava
(Slava)
July 20, 2020, 4:34pm
6
alexov_inbox:
{
"id": "...",
"section": "x"
"items": [
{
"id": 1,
"count": 10,
},
{
"id": 1,
"count": 10,
}, {
"id": 2,
"count": 20,
}
]
},
For this dataset the above aggregation returns the following result:
[
{ "_id" : 2, "sumCount" : 20, "totalDocs" : 1 },
{ "_id" : 1, "sumCount" : 20, "totalDocs" : 2 }
]
Isn’t this what you expect to achieve?
yes , its wrong, i have only 2 document with section: ‘x’. SQL COUNT(section) OVER (PARTITION BY section) AS [COUNT_Section] return 2 for any result rows
{
"id": "...",
"section": "x"
"items": [
{
"id": 1,
"count": 10,
},
{
"id": 1,
"count": 10,
}, {
"id": 2,
"count": 20,
}
]
},
{
"id": "...",
"section": "x"
"items": [
{
"id": 1,
"count": 100,
}, {
"id": 2,
"count": 200,
}
]
}
should return: (and better rename field totalDocs to totalDocsPerFieldSection)
[
{ "_id" : 2, "sumCount" : 220, "totalDocsPerFieldSection" : 2 },
{ "_id" : 1, "sumCount" : 120, "totalDocsPerFieldSection" : 2 }
]
slava
(Slava)
July 22, 2020, 11:22am
8
Try this:
db.test1.aggregate([
{
$unwind: '$items',
},
{
$group: {
_id: '$items.id',
sumCount: {
$sum: '$items.count',
},
docsInvolved: {
$addToSet: '$_id',
},
},
},
{
$project: {
sumCount: true,
totalSectionsThatContainThisItem: {
$size: '$docsInvolved',
},
},
},
]).pretty();
It returns:
[
{ "_id" : 2, "sumCount" : 220, "totalSectionsThatContainThisItem" : 2 },
{ "_id" : 1, "sumCount" : 120, "totalSectionsThatContainThisItem" : 2 }
]