MongoDB aggregation - count distinct in sub array

Question

I'm trying to count distinct values in sub array per document. So far I'm getting totals. My collection:

[
  {
    "_id": "5e013ca474082f6852",
    "name": [
      {
        "value": "Oak",
        "date": "2020-01-01 00:10:57"
      }
    ],
    "colors": [
      {
        "nice": "bla bala",
        "color": "Blue"
      },
      {
        "nice": "bla bala",
        "color": "Red"
      },
      {
        "nice": "bla bala",
        "color": "Blue"
      },
      {
        "nice": "bla bala",
        "color": "Green"
      },
      {
        "nice": "bla bala",
        "color": "Green"
      }
    ]
  },
  {
    "_id": "57689a4740857bb2",
    "name": [
      {
        "value": "Cloud",
        "date": "2020-01-01 00:10:57"
      }
    ],
    "colors": [
      {
        "nice": "bla bala",
        "color": "Blue"
      },
      {
        "nice": "bla bala",
        "color": "Blue"
      },
      {
        "nice": "bla bala",
        "color": "Blue"
      },
      {
        "nice": "bla bala",
        "color": "Green"
      },
      {
        "nice": "bla bala",
        "color": "Green"
      }
    ]
  }
]

The result I'm searching for is:

[
  {
    "_id": "5e013ca474082f6852",
    "name": [
      {
        "value": "Oak",
        "date": "2020-01-01 00:10:57"
      }
    ],
    "colors": {
      "Blue": 2,
      "Red": 1,
      "Green": 2
    }
  },
  {
    "_id": "57689a4740857bb2",
    "name": [
      {
        "value": "Cloud",
        "date": "2020-01-01 00:10:57"
      }
    ],
    "colors": {
      "Blue": 3,
      "Green": 2
    }
  }
]

Thanks P.S. I did not put any of my tries with aggregation using $group, because so far what I got is useless :) P.P.S. I do not know what else to say about my issue, SO says it is too much code, not enough text.

Ashh · Accepted Answer · 2020-01-09 09:56:03Z

1

You can use below aggregation

db.collection.aggregate([
  { "$addFields": {
    "colors": {
      "$arrayToObject": {
        "$map": {
          "input": { "$setUnion": ["$colors.color"] },
          "as": "m",
          "in": {
            "k": "$$m",
            "v": {
              "$size": {
                "$filter": {
                  "input": "$colors",
                  "as": "d",
                  "cond": { "$eq": ["$$d.color", "$$m"] }
                }
              }
            }
          }
        }
      }
    }
  }}
])

MongoPlayground

answered Jan 9, 2020 at 9:56

Ashh

46.6k16 gold badges111 silver badges137 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MongoDB aggregation - count distinct in sub array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related