How to remove a field of an array's nested object that has an empty string value using mongodb aggregation?

Question

So far, after i tried, i came up with solution where i am able to remove the whole object inside of the array if that object has field with empty value. That does not work in my case. I only need to remove the field and keep rest of the object. In this case, "Comment" field is the one having empty values occasionally. Thanks in advance!

Structure:

someArray: [
    {
      field1:"value",
      field2:"value",
      Comment:"",
      Answer:"",
},
    {
      field1:"value",
      field2:"value",
      Comment:"",
      Answer:"",

}]

Code:

        $project: {
          someArray: {
            $filter: {
              input: "$someArray", as: "array",
              cond: { $ne: [ "$$array.Comment", ""]}}}}

Instead of $filter try using the $map operator. And, use $$REMOVE system variable to delete a field. — prasad_
– prasad_, Commented Jun 29, 2022 at 3:22
Do you want to update the document permanently or it is specific to a read operation? — Charchit Kapoor
– Charchit Kapoor, Commented Jun 29, 2022 at 3:37
I have tried, but it is removing the whole object if "Comment": "". Specific to read operation. Thank you! — Roman
– Roman, Commented Jun 29, 2022 at 3:44
if i use $map instead of $filter cond becomes unrecognized parameter — Roman
– Roman, Commented Jun 29, 2022 at 3:50
@prasad_ I think with $$REMOVE you an remove only entire fields but not single elements from an array. — Wernfried Domscheit
– Wernfried Domscheit, Commented Jun 29, 2022 at 4:55

Charchit Kapoor · Accepted Answer · 2022-06-29 03:56:01Z

1

Use $map to loop over the array elements.For each array element where comment is not an empty string, return whole element, otherwise return the document excluding comment field. Like this:

db.collection.aggregate([
  {
    "$project": {
      someArray: {
        $map: {
          input: "$someArray",
          as: "element",
          in: {
            $cond: {
              if: {
                $eq: [
                  "",
                  "$$element.Comment"
                ]
              },
              then: {
                field1: "$$element.field1",
                field2: "$$element.field2"
              },
              else: "$$element"
            }
          }
        }
      }
    }
  },
  
])

Here, is the working link.

edited Jun 29, 2022 at 3:56

answered Jun 29, 2022 at 3:30

Charchit Kapoor

9,3242 gold badges11 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

prasad_ · Accepted Answer · 2022-06-29 11:04:18Z

1

Here is a solution where an array's nested object can have multiple fields and these need not be referred in the aggregation. Removes the nested object's field with value as an empty string (""):

db.collection.aggregate([
{ 
  $set: { 
      someArray: { 
          $map: { 
              input: '$someArray', 
              as: 'e',
              in: {
                  $let: {
                      vars: {
                          temp_var: { 
                              $filter: { 
                                  input: { $objectToArray: '$$e' }, 
                                  cond: { $ne: [ '', '$$this.v' ] }, 
                              }
                           }
                      },
                      in: {
                          $arrayToObject: '$$temp_var'
                      }
                  }
              }
          }
      }
   }
},
])

answered Jun 29, 2022 at 11:04

prasad_

14.4k2 gold badges27 silver badges44 bronze badges

3 Comments

Wernfried Domscheit Over a year ago

Maybe better cond: {$not: {$and: [ {$eq: ["$$this.k", "Comment"]}, {$eq: ["$$this.v", ""]} ]}}

prasad_ Over a year ago

Maybe, if your data has other fields with empty values. Finally, its a matter of the requirement.

prasad_ Over a year ago

@WernfriedDomscheit To check for just one field only (in this case, Comment), use this for the cond:

$or: [ { $ne: [ 'Comment', '$$this.k' ] },  { $and: [ { $eq: [ 'Comment', '$$this.k' ] }, { $ne: [ '', '$$this.v' ] } ] } ]

Wernfried Domscheit · Accepted Answer · 2022-06-29 07:13:55Z

Solution from Charchit Kapoor works only if your array has exactly

{
  field1: ...
  field2: ...
  Comment:""
}

But it does not work for arbitrary fields. I was looking for more generic solution, my first idea was this:

db.collection.aggregate([
   {
      "$project": {
         someArray: {
            $map: {
               input: "$someArray",
               in: {
                  $cond: {
                     if: { $eq: ["$$this.Comment", ""] },
                     then: { $mergeObjects: ["$$this", { Comment: "$$REMOVE" }] },
                     else: "$$this"
                  }
               }
            }
         }
      }
   }
])

but it does not work.

I ended on this one:

db.collection.aggregate([
   {
      "$project": {
         someArray: {
            $map: {
               input: "$someArray",
               in: {
                  $cond: {
                     if: { $eq: ["", "$$this.Comment"] },
                     then: {
                        $arrayToObject: {
                           $filter: {
                              input: {
                                 $map: {
                                    input: { $objectToArray: "$$this" },
                                    as: "element",
                                    in: { $cond: [{ $eq: ["$$element.k", "Comment"] }, null, "$$element"] }
                                 }
                              },
                              as: "filter",
                              cond: "$$filter" // removes null's from array 
                           }
                        }
                     },
                     else: "$$this"
                  }
               }
            }
         }
      }
   }
])

Mongo Playground

Collectives™ on Stack Overflow

How to remove a field of an array's nested object that has an empty string value using mongodb aggregation?

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related