Set the a MongoDB collection field value based on another collection field value

user_2404470 0
Viewed 321

I have 2 collections like this:

//collection1
[{
    Name: 'abc'
},
{
    Name: 'def'
}]

// collection2
[{
    Name: 'abc'
    PresentInCollection1: '' // this field should be true since collection2.Name exists in collection1.Name
},
{
    Name: 'xyz'
    PresentInCollection1: '' // this field should be false since collection2.Name does not exists in collection1.Name
}]

I wanted to $set the value collection2.PresentInCollection1 to true when the collection2.Name matches with collection1.Name. Here is what I have tried:

db.stocks.update({}, [{
    $lookup: {
      from: 'collection1',
      localField: 'Name',
      foreignField: 'Name',
      as: 'Match',
    },
  },
  {
    $set: {
      PresentInCollection1: {
        $cond: [{
            $eq: ["Name", "$Match.Name"]
          },
          true,
          false
        ]
      },
    },
  },
]);

It throws the error:

"$lookup is not allowed to be used within an update"

Another approach is updating documents iteratively but I wanted to do this in one go. I'm thinking of MongoDB's $map - $reduce but unable to think of the query :)

mongodb
1 Answers

I would try a $addFields stage instead of the $set. You would basically perform the lookup into the other collection, then you would want to add a field that specifies the length of the 'Match' array. You would then do another $addFields stage and add the desired 'PresentInCollection1' field and set it to true if the size of the array is greater than 0. If the size of the array is less than or equal to 0 you would set it equal to false.

Something like this (sry if it is formatted wrong, I dont have access to a formatter or code editor):

db.stocks.aggregate([{
    $lookup: {
      from: 'collection1',
      localField: 'Name',
      foreignField: 'Name',
      as: 'Match',
    },
  },
  {'$addFields': {
      'matchLen': {'$size': '$Match'}
    }
  },
 {'$addFields': {
     PresentInCollection1 : {'$cond':{ 
       'if': {$gt: ['$matchLen', 0]},
       'then': true,
       'else': false
        }
      }
    }
  },
 {'$unset': 'matchLen'}
]);

If you want the documents to appear in a new collection you would also need to use the out stage last. (I haven't included it because I'm not sure if you need it or not)

Someone can probably find a better solution but this is just the one that popped in my mind right away.

user_64015120
Related