Group By Nested Array Aggregation

Working with Data
,
3

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.

1 Like