CODEWITHSUNDEEP
mongodb
dez
dez

Reputation: 23

summing count result in two group

My collection's data are something like this :

[
  {
    ANumberAreaCode: "+98",
    BNumberAreaCode: "+1",
    AccountingTime: 1629754886,
    Length: 123
  },
  {
    ANumberAreaCode: "+44",
    BNumberAreaCode: "+98",
    AccountingTime: 1629754786,
    Length: 123
  },
  {
    ANumberAreaCode: "+98",
    BNumberAreaCode: "+96",
    AccountingTime: 1629754886,
    Length: 998
  }
]

I'm going to group on countries codes and count result (summing country codes in ANumberAreaCode and BNumberAreaCode ) .

This is my group sample :


{ "$group": {
        "_id": {
            "ANumberAreaCode": "$ANumberAreaCode",
        },
        "count": { "$sum": 1 }
    }},



{ "$group": {
        "_id": {
            "BNumberAreaCode": "$BNumberAreaCode",
        },
        "count": { "$sum": 1 }
    }},

now , how can i summing count result of two above queries for common countries ?

I'm looking for a query that give me this result :

+98 : 3
+44 : 1
+1 :1
+96 :1

Upvotes: 0

Views: 28

Answers (1)

J.F.
J.F.

Reputation: 15187

You can use this aggregation pipeline:

  • $facet to get both group, by A and B. This creates two objects: groupA and groupB.
  • Then using $concatArrays into $project stage it will concat two ouputs.
  • Deconstructs the array using $unwind
  • And $group again by values using $sum to get the total.
db.collection.aggregate([
  {
    "$facet": {
      "groupA": [
        {
          "$group": {
            "_id": "$ANumberAreaCode",
            "total": {
              "$sum": 1
            }
          }
        }
      ],
      "groupB": [
        {
          "$group": {
            "_id": "$BNumberAreaCode",
            "total": {
              "$sum": 1
            }
          }
        }
      ]
    }
  },
  {
    "$project": {
      "result": {
        "$concatArrays": [
          "$groupA",
          "$groupB"
        ]
      }
    }
  },
  {
    "$unwind": "$result"
  },
  {
    "$group": {
      "_id": "$result._id",
      "total": {
        "$sum": "$result.total"
      }
    }
  }
])

Example here

Upvotes: 2

Related Questions