How can i aggregate filter nested documents and get value from other field

Question

I have a collection like this:

{
  '_id' : ObjectId('6251f8556e75125f9260f333'),
  'name': 'jojo',
  'profile': 'jojo profile',
  'date': ISODate("2022-04-09T21:18:40.473Z"),
  'look': [
    { 'art': 'group-id', 'data': 'alma', 'dt': '1'},
    { 'art': 'called', 'data': 'central', 'dt': '1'},
    { 'art': 'access-time', 'data': 108000, 'dt': '1'}
  ]
  'answer': [
    { 'art': 'rate-id', 'data': 'limit1', 'dt': '1'},
    { 'art': 'protocol', 'data': 'tcp', 'dt': '1'}
  ]
},
{
  '_id' : ObjectId('6251f8306e75125f9260f332'),
  'name': 'dodo',
  'profile': 'dodo profile',
  'date': ISODate("2022-04-09T15:20:58.562Z"),
  'look': [
    { 'art': 'group-id', 'data': 'alma', 'dt': '1'},
    { 'art': 'called', 'data': 'central', 'dt': '1'},
  ]
  'answer': [
    { 'art': 'rate-id', 'data': 'limit1', 'dt': '1'},
  ]
},
{
  '_id' : ObjectId('6251a5113700ba4a0a59c48f'),
  'name': 'kaka',
  'profile': 'kaka profile',
  'date': ISODate("2022-04-09T15:22:25.816Z"),
  'look': [
    { 'art': 'access-time', 'data': 50400, 'dt': '1'}
  ]
  'answer': [
    { 'art': 'protocol', 'data': 'tcp', 'dt': '1'}
  ]
}

and I was expecting an output like this:

{
  '_id' : ObjectId('6251f8556e75125f9260f333'),
  'name': 'jojo',
  'profile': 'jojo profile',
  'date': ISODate("2022-04-09T21:18:40.473Z"),
  'goup': 'alma',        // filter by 'group-id' and put value of data field
  'called': 'central',   // filter by 'called' and put value of data field
  'accessTime': 108000,  // filter by 'access-time' and put value of data field
  'rate': 'limi1',       // filter by 'rate-id' and put value of data field
  'protocol': 'tcp',     // filter by 'protocol' and put value of data field
},
{
  '_id' : ObjectId('6251f8306e75125f9260f332'),
  'name': 'dodo',
  'profile': 'dodo profile',
  'date': ISODate("2022-04-09T15:20:58.562Z"),
  'goup': 'alma',
  'called': 'central',
  'accessTime': '',     // set blank data if not exist
  'rate': 'limi1',
  'protocol': '',       // set blank data if not exist
},
{
  '_id' : ObjectId('6251a5113700ba4a0a59c48f'),
  'name': 'kaka',
  'profile': 'kaka profile',
  'date': ISODate("2022-04-09T15:22:25.816Z"),
  'goup': '',          // set blank data if not exist
  'called': '',        // set blank data if not exist
  'accessTime': 50400,
  'rate': '',          // set blank data if not exist
  'protocol': 'tcp',
}

I've searched here but couldn't find an answer that matches the problem I'm facing, probably because of the wrong keywords. Since I'm new to mongodb, I'm confused about how to solve the query I want. How can I achieve this? Please help me...

Answer 1

You would require an aggregate operation that has a pipeline with the following key operators and stages:

• $map: an operator to transform the look and answer arrays into documents with just mapped k and v fields, crucial for obtaining a hash map with the following operator
• $arrayToObject: this allows the above to be possible i.e. converting an array into a single document
• $mergeObjects: combine top level fields i.e. _id, date, name, profile together with the converted documents above
• $replaceWith: pipeline stage to replace the root document with the specified document from above

Overall, your pipeline should follow:

const first = { 
   $first: {
      $split: ['$$this.art', '-']
   }
};
const keyExpression = {
   $cond: [
     { $eq: [first, 'access'] },
     'accessTime',
     first
   ]
};

const pipeline = [
    { $replaceWith: {
        $mergeObjects: [
            {  
                _id: '$_id', 
                date: '$date', 
                name: '$name', 
                profile: '$profile',
                protocol: '',
                group: '',
                called: '',
                rate: '',
                accessTime: '',
            },
            { $arrayToObject: {
                $map: {
                  input: '$look',
                  in: { k: keyExpression, v: '$$this.data' } 
                }
            } },
            { $arrayToObject: {
                $map: {
                  input: '$answer',
                  in: { k: keyExpression, v: '$$this.data' } 
                }
            } }
        ]
    } } 
]

Mongo Playground