Replacing field while taking data

[Nick_Elovsky]

Hi, community,

I struggle with replacing values while getting data.
Let’s imagine we have two collections:

mappedUsers = [
  {
    groupName: 'Group 1',
    users: [
      'Nick',
      'John',
    ]
  },
  {
    groupName: 'Group 2',
    users: [
      'Mark',
      'Samuel',
    ]
  }
]

data = [
  {
    date: '22/13/07',
    results: [
      {
        user: 'Nick',
        result: 5
      },
      {
        user: 'Mark',
        result: 7
      }
    ]
  },
  {
    date: '22/13/06',
    results: [
      {
        user: 'John',
        result: 6
      },
      {
        user: 'Helga',
        result: 9
      }
    ]
  },
]

I want to replace users with their groups. If a user doesn’t belong to any group we get his name. Same table with just mapped users:

results = [
  {
    date: '22/13/07',
    results: [
      {
        user: 'Group 1',  <-- replaced
        result: 5
      },
      {
        **user: 'Group 2',  <-- replaced
        result: 7
      }
    ]
  },
  {
    date: '22/13/06',
    results: [
      {
        **user: 'Group 1',  <-- replaced
        result: 6
      },
      {
        **user: 'Helga',  <-- NOT replaced
        result: 9
      }
    ]
  },
]

How can I replace those values?

[Aasawari (Aasawari Sahasrabuddhe)]

Hi @Nick_Elovsky and welcome to the community!!

Could you advise further context or the use case details for the desired results you have specified? Additionally, have you considered perhaps doing the replacements on the application end?

Also, if you still wish to achieve using aggregation, here is the step by step aggregation stages to achieve the above to an extent.

db.data.aggregate( [ { '$unwind': '$results' }, 
{
  '$lookup': {
    from: 'mappedUsers',
    localField: 'results.user',
    foreignField: 'users',
    as: 'relationship'
  }
}, 
{
  '$project': {
    'results.user': {
      '$cond': {
        if: { '$gte': [ { '$size': '$relationship' }, 1 ] },
        then: '$relationship.groupName',
        else: '$results.user'
      }
    },
    date: 1,
    'results.result': 1
  }
}, 
{$group:
     {_id:'$date',
       results:
              {$push:'$results'}}
}
]
)

The above aggregation includes four stages and the output response would look like:

[
  {
    _id: ObjectId("62df743966c81fed8894c2e2"),
    date: '22/13/07',
    results: { result: 5, user: [ 'Group 1' ] }
  },
  {
    _id: ObjectId("62df743966c81fed8894c2e2"),
    date: '22/13/07',
    results: { result: 7, user: [ 'Group 2' ] }
  },
  {
    _id: ObjectId("62df743966c81fed8894c2e3"),
    date: '22/13/06',
    results: { result: 6, user: [ 'Group 1' ] }
  },
  {
    _id: ObjectId("62df743966c81fed8894c2e3"),
    date: '22/13/06',
    results: { result: 9, user: 'Helga' }
  }
]

Please perform thorough testing to verify this suits your use case(s) or requirements. Additionally, you may want to view the $merge documentation which can allow you to write the results of the aggregation pipeline to a specified collection.

Let us know if you have any further questions.

Thanks
Aasawari