CODEWITHSUNDEEP
mongodb
Sagar Gupta
Sagar Gupta

Reputation: 1450

Omit empty fields from MongoDB query result

Is there a way to omit empty fields (eg empty string, or an empty array) from MongoDB query results' documents (find or aggregate).

Document in DB:

{
    "_id" : ObjectId("5dc3fcb388c1c7c5620ed496"),
    "name": "Bill",
    "emptyString" : "",
    "emptyArray" : []
}

Output:

{
    "_id" : ObjectId("5dc3fcb388c1c7c5620ed496"),
    "name": "Bill"
}

Similar question for Elasticsearch: Omit null fields from elasticsearch results

Upvotes: 4

Views: 3786

Answers (3)

Karim Ayachi
Karim Ayachi

Reputation: 717

I think the easiest way to remove all empty string- and empty array-fields from the output is to add the aggregation stage below. (And yes, "easy" is relative, when you have to create these levels of logic to accomplish such a trivial task...)

$replaceRoot: {
  newRoot: {
    $arrayToObject: {
      $filter: {
        input: {
          $objectToArray: '$$ROOT'
        },
        as: 'item',
        cond: {
          $and: [
            { $ne: [ '$$item.v', [] ] },
            { $ne: [ '$$item.v', '' ] }
          ]
        }
      }
    }
  }
}

Just modify the cond-clause to filter out other types of fields (e.g. null).

btw: I haven't tested the performance of this, but at least it's generic and somewhat readable.

Edit: IMPORTANT! The $replaceRoot-stage does prevent MongoDB from optimizing the pipeline, so if you use it in a View that you run .find() on, it will append a $match-stage to the end of the View's pipeline, in stead of prepending an indexed search at the start of the pipeline. This will have significant impact on the performance. You can safely use it in a custom pipeline though, as long as you have the $match-stage before it. (At least as far as my limited MongoDB knowledge tells me). And if anyone knows how to prépend a $match-stage to a View when querying, then please leave a comment :-)

Upvotes: 0

ambianBeing
ambianBeing

Reputation: 3529

One way this could be done is using cursor.map() which is available on find() and aggregation([]) both.

The idea is to have list of the fields that are present/could be in the documents and filter out by using delete operator to remove the fields (which are empty strings or empty array, both have length property) from returning document.

Mongo Shell:

var fieldsList = ["name", "emptyString", "emptyArray"];

db.collection.find().map(function(d) {
  fieldsList.forEach(function(k) {
    if (
      k in d &&
      (Array.isArray(d[k]) ||
        (typeof d[k] === "string" || d[k] instanceof String)) &&
      d[k].length === 0
    ) {
      delete d[k];
    }
  });
  return d;
});

Test documents:

{
  "_id" : ObjectId("5dc426d1f667120607ac5006"),
  "name" : "Bill",
  "emptyString" : "",
  "emptyArray" : [ ]
}
{
  "_id" : ObjectId("5dc426d1f667120607ac5007"),
  "name" : "Foo",
  "emptyString" : "foo",
  "emptyArray" : [ ]
}
{
  "_id" : ObjectId("5dc426d1f667120607ac5008"),
  "name" : "Bar",
  "emptyString" : "",
  "emptyArray" : [
          "foo",
          "bar"
  ]
}
{
  "_id" : ObjectId("5dc426d1f667120607ac5009"),
  "name" : "May",
  "emptyString" : "foobar",
  "emptyArray" : [
          "foo",
          "bar"
  ]
}

O/P

[
        {
                "_id" : ObjectId("5dc426d1f667120607ac5006"),
                "name" : "Bill"
        },
        {
                "_id" : ObjectId("5dc426d1f667120607ac5007"),
                "name" : "Foo",
                "emptyString" : "foo"
        },
        {
                "_id" : ObjectId("5dc426d1f667120607ac5008"),
                "name" : "Bar",
                "emptyArray" : [
                        "foo",
                        "bar"
                ]
        },
        {
                "_id" : ObjectId("5dc426d1f667120607ac5009"),
                "name" : "May",
                "emptyString" : "foobar",
                "emptyArray" : [
                        "foo",
                        "bar"
                ]
        }
]

Note: if the number of fields are very large in the documents this may not be very optimal solution since the comparisons are going to happen with all fields in document. You might want to chunk the fieldsList with properties that are suspected to be empty array or string.

Upvotes: 0

Mahesh Bhatnagar
Mahesh Bhatnagar

Reputation: 1080

Please use aggregate function. If you want to remove key. you use $cond by using $project. 

db.Speed.aggregate( [
   {
      $project: {
         name: 1,
         "_id": 1,
         "emptyString": {
            $cond: {
               if: { $eq: [ "", "$emptyString" ] },
               then: "$$REMOVE",
               else: "$emptyString"
            }
         },
         "emptyArray": {
            $cond: {
               if: { $eq: [ [], "$emptyArray" ] },
               then: "$$REMOVE",
               else: "$emptyArray"
            }
         }
      }
   }
] )

Upvotes: 6

Related Questions