Merge arrays by matching similar values in mongodb

user_15392169 0
Viewed 34

This is an extension of the below question.

Filter arrays in mongodb

I have a collection where each document contains 2 arrays as below.

{
  users:[
    {
      id:1,
      name:"A"
    },
    {
      id:2,
      name:"B"
    },
    {
      id:3,
      name:"C"
    }
  ]
  priv_users:[
    {
      name:"X12/A",
      priv:"foobar"
    },
    {
      name:"Y34.B",
      priv:"foo"
    }
  ]
}

From the linked question, I learnt to use $map to merge 2 document arrays. But I can't figure out to match users.name to priv_users.name to get below output.

{
  users:[
    {
      id:1,
      name:"A",
      priv:"foobar"
    },
    {
      id:2,
      name:"B",
      priv:"foo"
    },
    {
      id:3,
      name:"C"
    }
  ]
}

users.name and priv_users.name don't have a consistent pattern, but users.name exists within priv_users.name. MongoDB version is 4.0

aggregation-framework mongodb-query mongodb
1 Answers

This may not be as generic but will push you in the right direction. Consider using the operators $mergeObjects to merge the filtered document from the priv_users array with the document in users. Filtering takes the $substr of the priv_users name field and compares it with the users name field. The resulting pipeline will be as follows

db.collection.aggregate([
    { '$addFields': { 
        'users': {
            '$map': {
                'input': '$users',
                'in': {
                    '$mergeObjects': [
                        {
                            '$arrayElemAt': [
                                {
                                    '$filter': {
                                        'input': '$priv_users',
                                        'as': 'usr',
                                        'cond': {
                                            '$eq': [
                                                '$$this.name',
                                                { '$substr': [ 
                                                  '$$usr.name', 4, -1 
                                                ] }
                                            ]
                                        }
                                    }
                                },
                                0
                            ]
                        },                        
                        '$$this'
                    ]
                }
            }
        }
    } }
])

If using MongoDB 4.2 and newer versions, consider using $regexMatch operator for matching the priv_users name field with the users name field as the regex pattern. Your $cond operator now becomes:

'cond': {
    '$regexMatch': { 
        'input': '$$usr.name', 
        'regex': '$$this.name', 
        'options': "i" 
    }
}
user_1220050
Related