Database looks like this:

[
    {
        store: "s1",
        prod: "a"
    },
    {
        store: "s2",
        prod: "b"
    },
    {
        store: "s3",
        prod: "a"
    },
    {
        store: "s2",
        prod: "c"
    },
    {
        store: "s5",
        prod: "a"
    },
    {
        store: "s3",
        prod: "b"
    },
    {
        store: "s5",
        prod: "a"
    },
    {
        store: "s1",
        prod: "c"
    }
]

Aggregation result I would like:

[
    {
        store: "s1",
        a: 1,
        b: 0,
        c: 1,
    },
    {
        store: "s2",
        a: 0,
        b: 1,
        c: 1,
    }
]

What I have so far:

[
    { $match: { store: { $in: ["s1", "s2"] } } },
    { $group: { "_id": null, "store": "$store", "a": { $sum: 1 } } },
]

This is obviously incorrect but I know if I figure out how to sum up the "a" field, I will be on the right track. What is the right way to achieve this?

CodePudding user response：

A simple solution would be $sum up with $cond

db.collection.aggregate([
  {
    $group: {
      _id: "$store",
      a: {
        "$sum": {
          "$cond": {
            "if": {
              $eq: [
                "$prod",
                "a"
              ]
            },
            "then": 1,
            "else": 0
          }
        }
      },
      b: {
        "$sum": {
          "$cond": {
            "if": {
              $eq: [
                "$prod",
                "b"
              ]
            },
            "then": 1,
            "else": 0
          }
        }
      },
      c: {
        "$sum": {
          "$cond": {
            "if": {
              $eq: [
                "$prod",
                "c"
              ]
            },
            "then": 1,
            "else": 0
          }
        }
      }
    }
  }
])

Here is the Mongo Playground for your reference.