Group By Nested Array Aggregation

[Fhilip_Jhune_Fernandez]

How do I group nested arrays like this.

[
    {
        "_id": "Id",
        "name": "Product Name",
        "Departments": [
            {
                "_id": "63c0faf4752a6a7cfd169602",
                "name": "Audio",
                "parentId": null
            },
            {
                "_id": "63c0faf4752a6a7cfd16960c",
                "name": "Home Audio & Speakers",
                "parentId": "63c0faf4752a6a7cfd169602"
            },
            {
                "_id": "63c0faf4752a6a7cfd16960d",
                "name": "Speakers",
                "parentId": "63c0faf4752a6a7cfd16960c"
            }
        ],
    },
    {
        "_id": "Id",
        "name": "Product Name",
        "Departments": [
            {
                "_id": "63c0faf4752a6a7cfd169957",
                "name": "Computers & Accessories",
                "parentId": null
            },
            {
                "_id": "63c0faf4752a6a7cfd16996c",
                "name": "Data Storage",
                "parentId": "63c0faf4752a6a7cfd169957"
            },
            {
                "_id": "63c0faf4752a6a7cfd16996e",
                "name": "SSD",
                "parentId": "63c0faf4752a6a7cfd16996c"
            }
        ],
    },
    {
        "_id": "Id",
        "name": "Product Name",
        "Departments": [
            {
                "_id": "63c0faf4752a6a7cfd169602",
                "name": "Audio",
                "parentId": null
            },
            {
                "_id": "63c0faf4752a6a7cfd16960c",
                "name": "Home Audio & Speakers",
                "parentId": "63c0faf4752a6a7cfd169602"
            },
            {
                "_id": "63c0faf4752a6a7cfd16960d",
                "name": "AV Receivers",
                "parentId": "63c0faf4752a6a7cfd16960c"
            }
        ],
]

Into this. I want them to be grouped by their department names and getting only the 2nd item of the array. I tried doing different approaches and I still can’t manage to do it. Can anyone help me with this.

{
	ShopDepartments: [
		{
			"_id": "63c0faf4752a6a7cfd16960c",
			"name": "Home Audio & Speakers",
			"parentId": "63c0faf4752a6a7cfd169602"
        },
		{
			"_id": "63c0faf4752a6a7cfd16996c",
			"name": "Data Storage",
			"parentId": "63c0faf4752a6a7cfd169957"
		},
	]

}

[Ian (Ian Y)]

You will need to use a MongoDB Search aggregation pipeline and use facets inside the $searchMeta pipeline stage to group your results.

Have a look at this page on facets and in particular the examples which help to demonstrate. There is also a helpful tutorial to walk you through an example.

[steevej (Steeve Juneau)]

@Fhilip_Jhune_Fernandez

I want them to be grouped by their department names and getting only the 2nd item of the array

With the example result and sample documents, what I understand you want is that for each input documents, you want to find the unique list of the 2nd elements of the Departments array.

You extract the 2nd element with a $set stage like:

set_second_element = { $set : {
    second_element : { $arrayElemAt : [ "$Departments" , 1 ] }
} }

Then you $group by using second_element as the group _id like:

group_second_element = { $group : {
    _id : "$second_element.name" ,
    department : { $first : "$second_element" }
} }

The aggregation pipeline [ set_second_element , group_second_element ] will produce

{ _id: 'Home Audio & Speakers',
  department: 
   { _id: '63c0faf4752a6a7cfd16960c',
     name: 'Home Audio & Speakers',
     parentId: '63c0faf4752a6a7cfd169602' } }
{ _id: 'Data Storage',
  department: 
   { _id: '63c0faf4752a6a7cfd16996c',
     name: 'Data Storage',
     parentId: '63c0faf4752a6a7cfd169957' } }

Which matches the data of the expected result. I leave as an exercise to the reader a final cosmetic $project to get the data into the exact expected format.

When you redact documents for publishing please ensure we can cut-n-paste them and insert them into our system without having us to edit them. The last sample document was not terminated correctly. The "_"id:“Id” produce duplicate errors.