MongoDB group by with over by partition as SQL

Working with Data
#1

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
#2

any news , idea, variants ?

#3

mb question is wrong or something is not clear?

#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 }
]
#5

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,
        }
    ]
},
#6

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?

#7

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 }
]
#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 }
]