How to remove field conditionally mongoodb-CodePudding

I have a collection and its documents look like:

{
  _id: ObjectId('111111111122222222223333'),
  my_array: [
    {
      id: ObjectId('777777777788888888889999')
      name: 'foo'
    },
    {
      id: ObjectId('77777777778888888888555')
      name: 'foo2'
    }
    //...
  ]
  //more attributes
}

However, some documents have my_array: [{}] (with one element which is an empty array).

How can I add conditionally a projection or remove it?

I have to add it to a mongo pipeline at the end of the query, and I want to get my_array only when it has at least one element which is not an empty object. If there's an empty object remove it.

I tried with $cond and $eq in a projection stage but it is not supported. Any suggestion to solve this?

CodePudding user response：

You can't do that in a query, however in an aggregations you can add $filter to you pipeline, like so:

db.collection.aggregate([
  {
    $project: {
      my_array: {
        $filter: {
          input: "$my_array",
          as: "elem",
          cond: {
            $ne: [
              {},
              "$$elem"
            ]
          }
        }
      }
    }
  }
])

Mongo Playground

However unless this is "correct" behavior I suggest you clean up your database, it's much simpler to maintain "proper" structure than to update all your queries everywhere.

You can use this update to remove these objects:

db.collection.update({
  "myarray": {}
},
[
  {
    "$set": {
      "my_array": {
        $filter: {
          input: "$my_array",
          as: "elem",
          cond: {
            $ne: [
              {},
              "$$elem"
            ]
          }
        }
      }
    }
  },
  
],
{
  "multi": false,
  "upsert": false
})

Mongo Playground

CodePudding user response：

Suppose you have documents like this with my_array field:

{ "my_array" : [ ] }
{ "my_array" : [ { "a" : 1 } ] }    // #(1)
{ "my_array" : null }
{ "some_fld" : "some value" }
{ "my_array" : [ {  } ] }
{ "my_array" : [ { "a" : 2 }, { "a" : 3 } ] }    // #(2)

And, the following aggregation will filter and the result will have the two documents (1) and (2):

db.collection.aggregate([
{
  $match: { 
      $expr: {
          $and: [ 
              { $eq: [ { $type: "$my_array" }, "array" ] },
              { $gt: [ { $size: "$my_array" }, 0 ] }, 
              { $ne: [ [{}], "$my_array" ] }
          ]
      }
  } 
}
])

This also works with a find method:

db.collection.find({
    $expr: {
          $and: [ 
              { $eq: [ { $type: "$my_array" }, "array" ] },
              { $gt: [ { $size: "$my_array" }, 0 ] }, 
              { $ne: [ [{}], "$my_array" ] }
          ]
      }
})

To remove the my_array field, from a document when its empty, then you try this aggregation:

db.collection.aggregate([
{
  $addFields: { 
      my_array: {
          $cond: [
              {$and: [ 
                  { $eq: [ { $type: "$my_array" }, "array" ] },
                  { $gt: [ { $size: "$my_array" }, 0 ] }, 
                  { $ne: [ [{}], "$my_array" ] }
              ]}, 
              "$my_array", 
              "$$REMOVE"
          ]
      }
  } 
}
])

The result:

{  }
{ "my_array" : [ { "a" : 1 } ] }
{  }
{ "a" : 1 }
{  }
{ "my_array" : [ { "a" : 2 }, { "a" : 3 } ] }