How to remove fields in an aggregation?

Working with Data
, , ,
1

I’d like to do something like what .updateMany(query, { $unset: { field1: 1, field2: 1 } }) does

const u1 = new ObjectId(), u2 = new ObjectId(), u3 = new ObjectId();
await conn.collection('a').insertMany([
   { bid: u1, name: 'A', deleted: true },
   { bid: u2, name: 'B' },
   { bid: u3, name: 'C' },
])

await conn.collection('b').insertMany([
   { _id: u1, name: 'A2', deleted: true },
   { _id: u2, name: 'B2', deleted: true },
   { _id: u3, name: 'C2' },
])

await conn.collection('a').aggregate([
  { $replaceRoot: { newRoot: { _id: '$bid', name: '$name', deleted: '$$REMOVE' } } }, // tried this 
  { $unset: ['deleted'] }, // and tried this
  { $merge: { into: 'b', whenNotMatched: 'discard' } },
]).toArray();

console.log(await conn.collection('b').find().toArray());
/*
[
  { _id: new ObjectId('67c9a68418c34651a785b05e'), name: 'A', deleted: true},
  { _id: new ObjectId('67c9a68418c34651a785b05f'), name: 'B', deleted: true },
  { _id: new ObjectId('67c9a68418c34651a785b060'), name: 'C' }
]
*/

I’d like the field deleted to be removed in the output in collection b, what am I missing?

  • I think $$REMOVE is not supported there maybe? (docs says it’s supported only in $addFields and $project)
  • $unset should work?
2

Hi @Cyril_Auburtin1,

The deleted field is removed from collection a, but it already exists in collection b. When merging, the $merge stage will not automatically remove fields from the destination collection (b). To address this, you can use the whenMatched property in the $merge stage to handle the removal of the deleted field during the merge.

In this case, you don’t need the $replaceRoot or $unset stages. Instead, you can simplify the pipeline by starting with a $project stage to shape the documents from collection a, and then use $merge with whenMatched to update and remove the deleted field in collection b.

Here’s the refined solution:

await conn.collection('a').aggregate([
  { 
    "$project": { 
      "_id": "$bid", 
      "name": 1 
    }
  },
  {
    "$merge": {
      "into": "b",
      "whenMatched": [
        {
          "$set": {
            "name": "$name", 
            "deleted": "$$REMOVE" 
          }
        }
      ],
      "whenNotMatched": "discard" 
    }
  }
]).toArray();
2 Likes
3

Long time no see @turivishal, good to read you.

2 Likes