I have this collection:

[{
    "_id": "5c378eecd11e570240a9b0ac",
    "userID": "1",
    "isActive": "Active",
    "areaCode": "A-1",
    "__v": 0
},
{
    "_id": "5c378eecd11e570240a9b0bb",
    "userID": "1",
    "isActive": "Active",
    "areaCode": "A-2",
    "__v": 0
},
{
    "_id": "5c378eecd11e570240a9b0c5",
    "userID": "2",
    "isActive": "Active",
    "areaCode": "A-1",
    "__v": 0
}]

Need help in grouping the results by user ID then the area code using aggregation but I'm not getting the desired output. Here's what I've tried:

        AreaCodes.aggregate([
            {
                '$match': { '$and': [
                        { 'isActive': 'Active' },
                        { 'userID': { '$exists': true } }
                    ]
                }
            },
            {
                '$group': {
                    '_id': {
                        'userID': '$userID'
                    },
                    'entries': {
                        '$push': {
                            'areaCode': '$areaCode'
                        }
                    }
                }
            },
            {
                '$group': {
                    '_id': '$_id.userID',
                    'areaCodes': {
                        '$push': {
                            'areaCode': '$entries'
                        }
                    }
                }
            },
            {
                '$project': {
                    '_id': 0,
                    'userID': '$_id',
                    'areaCodes': '$areaCodes'
                }
            }
        ])

Which returns the following:

[
    {
        "userID": "1",
        "areaCodes": [
            {
                "areaCode": [
                    {
                        "areaCode": "A-1"
                    },
                    {
                        "areaCode": "A-2"
                    }
                ]
            }
        ]
    },
    {
        "userID": "2",
        "areaCodes": [
            {
                "areaCode": [
                    {
                        "areaCode": "A-1"
                    }
                ]
            }
        ]
    }
]

My desired output would be to remove the excess areaCode objects and group them inside an array for each user like:

[
    {
        "userID": "1",
        "areaCodes": ["A-1", "A-2"]
    },
    {
        "userID": "2",
        "areaCodes": ["A-1"]
    }
]

How to achieve this format? Thanks.

CodePudding user response：

How about:

db.collection.aggregate([
  {
    $match: {
      $and: [{ "isActive": "Active" }, {"userID": {"$exists": true}}]
    }
  },
  {
    $group: {
      _id: '$userID',
      areaCodes: {$addToSet: "$areaCode"}
    }
  },
  {
    $project: {
      _id: 0,
      userID: "$_id",
      areaCodes: 1
    }
  }
])

As you can see on this playgeound example.

If you just want the matching areaCodes, you can simply use $addToSet.