Group nested inner array in mongoose

Question

I have mongoDb content as below:

[{
"_id": {
    "$oid": "57c6699711bd6a0976cabe8a"
},
"ID": "1111",
"FullName": "AAA",
"Category": [
    {
        "CategoryId": {
            "$oid": "57c66ebedcba0f63c1ceea51"
        },
        "_id": {
            "$oid": "57e38a8ad190ea1100649798"
        },
        "Value": [
            {
                "Name": ""
            }
        ]
    },
    {
        "CategoryId": {
            "$oid": "57c3df061eb1e59d3959cc40"
        },
        "_id": {
            "$oid": "57e38a8ad190ea1100649797"
        },
        "Value": [
            [
                "111",
                "XXXX",
                "2005"
            ],
            [
                "1212",
                "YYYY",
                "2000"
            ],
            [
                "232323",
                "ZZZZZ",
                "1999"
            ]
        ]
    }
]
},{
    "_id": {
        "$oid": "57c6699711bd6a0976cabe8a"
    },
    "ID": "1111",
    "FullName": "BBB",
    "Category": [
        {
            "CategoryId": {
                "$oid": "57c66ebedcba0f63c1ceea51"
            },
            "_id": {
                "$oid": "57e38a8ad190ea1100649798"
            },
            "Value": [
                {
                    "Name": ""
                }
            ]
        },
        {
            "CategoryId": {
                "$oid": "57c3df061eb1e59d3959cc40"
            },
            "_id": {
                "$oid": "57e38a8ad190ea1100649797"
            },
            "Value": [
                [
                    "4444",
                    "XXXX",
                    "2005"
                ],
                [
                    "7777",
                    "GGGG",
                    "2000"
                ],
                [
                    "8888",
                    "ZZZZZ",
                    "1999"
                ]
            ]
        }
    ]
}]

Here i have an array named 'ResumeCategory' where it contains objects with different category id.

I need to 1. select category whose id is '57c3df061eb1e59d3959cc40' 2. The above selected category contains value as array 3. From the value array, I have to group element based on second value and need to get the user name list

Eg Output:

[{
    'CategoryName': 'XXXX',
    'Users': ['AAA', 'BBB']
},
{
    'CategoryName': 'YYYY',
    'Users': ['AAA']
},
{
    'CategoryName': 'ZZZZZ',
    'Users': ['AAA', 'BBB']
},
{
    'CategoryName': 'GGGG',
    'Users': ['BBB']
}]

I have tried to group using aggregate function as below:

resume.aggregate([
  {$match: {'Category.CategoryId': new ObjectId('57c3df191eb1e59d3959cc43')}},
  {$unwind: '$Category'},
  {$unwind: '$Category.Value'},
  {$match: {'Category.CategoryId': new ObjectId('57c3df191eb1e59d3959cc43')}},
  {$group: { _id: '$Category.Value', count: {$sum: 1}}},
  {$project: {'_id':0, TagValue: '$_id',count: '$count'}}
],function(err, resData){
        res.send(resData);
});

From above query, It is grouped based on Value array. It takes the array with three element to group. Can anyone help to group the value based on inner array value (single element ie. value[1]) and get the desired result.

Thanks in advance.

Answer 1

You can use $arrayElemAt (introduced in version 3.2) in the project stage to select the second element in the array.

db.resume.aggregate([
                        {"$unwind":"$Category"}, 
                        {"$match":{"Category.CategoryId" :ObjectId("57c3df061eb1e59d3959cc40")}}, 
                        {"$unwind":"$Category.Value"}, 
                        {"$project":{"FullName":1, "secondVal" : {"$arrayElemAt": ["$Category.Value",1]} }}, 
                        {"$group":{"_id":"$secondVal", "Users":{"$addToSet":"$FullName"}}}, 
                        {"$project":{"CategoryName":"$_id", "_id": 0, "Users":1}}
                    ])

Sample Output:

{ "Users" : [ "AAA", "BBB" ], "CategoryName" : "XXXX" }
{ "Users" : [ "AAA" ], "CategoryName" : "YYYY" }
{ "Users" : [ "BBB" ], "CategoryName" : "GGGG" }
{ "Users" : [ "AAA", "BBB" ], "CategoryName" : "ZZZZZ" }

Answer 2

You just need to $unwind "Category.Value" field twice since it has double nested arrays:

resume.aggregate([
    { "$match": { "Category.CategoryId": "57c3df061eb1e59d3959cc40" } },
    { "$unwind": "$Category" },
    { "$match": { "Category.CategoryId": "57c3df061eb1e59d3959cc40" } },    
    { "$unwind": "$Category.Value" },// <-- unwind twice
    { 
        "$unwind": {
            "path": "$Category.Value",
            "includeArrayIndex": "idx" 
        }
    },
    { "$match": { "idx": 1 } },
    {
        "$group": {
            "_id": "$Category.Value",
            "Users": { "$push": "$FullName" },
            "count": { "$sum": 1 }
        }
    },
    { "$project": { "_id": 0, "CategoryName": "$_id", "Users": 1, "count": 1 } } 
], function(err, resData){
    res.send(resData);
});