CODEWITHSUNDEEP
mongodbaggregation-framework
darkphoenix
darkphoenix

Reputation: 2167

Aggregate on an embedded document and unique value count

I have documents similar to these stored in my database:

[
  {
    "location_path": {
      "Country": "US",
      "State": "Illinois",
      "City": "Chicago"
    },
    "myvalue": 1
  },
  {
    "location_path": {
      "Country": "US",
      "State": "Houston",
      "City": "Texas"
    },
    "myvalue": 4
  },
  {
    "location_path": {
      "Country": "US",
      "State": "Illinois",
      "City": "Chicago"
    },
    "myvalue": 2
  }
]

I'm having some issues constructing the $group stage of an aggregation to group documents with the same location path and get a count in each group of each unique value of myvalue encountered. So, my expected output would be something like:

{
    "location_path": {
        "Country": "US",
        "State": "Illinois",
        "City": "Chicago"
    },
    "myvalue": {1: 1, 2: 1}
},
{
    "location_path": {
        "Country": "US",
        "State": "Houston",
        "City": "Texas"
    },
    "myvalue": {4: 1}
}

How can I achieve something similar to what I want? So far, I have this as my $group stage:

{
    '$group': {
        '_id': {
            'location_path': '$location_path',
            'option': '$myvalue'
        },
        'count': {'$sum': 1}
    }
}

It's okay if the result is somewhat different from what I expect, but location_path and the count per unique value of myvalue are important.

Upvotes: 0

Views: 43

Answers (1)

darkphoenix
darkphoenix

Reputation: 2167

{
    '$group': {
        '_id': { 'path': '$location_path', 'value': '$myvalue'},
        'count': {'$sum': 1}
    },
    '$project': {
        '_id': '$_id.path',
        'myvalue': '$_id.value',
        'count': '$count',
    },
    '$group': {
        '_id': '$_id',
        'values': {'$push': {'myvalue': '$myvalue', 'count': '$count'}}
    }
}

Upvotes: 1

Related Questions