'MongoDB Aggregate Query to find the documents with missing values

I am having a huge collection of objects where the data is stored for different employees.

{
  "employee": "Joe",
  "areAllAttributesMatched": false,
  "characteristics": [
    {
      "step": "A",
      "name": "house",
      "score": "1"
    },
    {
      "step": "B",
      "name": "car"
    },
    {
      "step": "C",
      "name": "job",
      "score": "3"
    }
  ]
}

There are cases where the score for an object is completely missing and I want to find out all these details from the database. In order to do this, I have written the following query, but seems I am going wrong somewhere due to which it is not displaying the output.

enter image description here

I want the data in the following format for this query, so that it is easy to find out which employee is missing the score for which step and which name.

enter image description here

mongodbmongodb-queryaggregation-framework


Solution 1:[1]

db.collection.aggregate([
  {
    "$unwind": "$characteristics"
  },
  {
    "$match": {
      "characteristics.score": {
        "$exists": false
      }
    }
  },
  {
    "$project": {
      "employee": 1,
      "name": "$characteristics.name",
      "step": "$characteristics.step",
      _id: 0
    }
  }
])

You need to use $exists to check the existence

playground

Solution 2:[2]

You can use $ifNull to handle both cases of 1. the score field is missing 2. score is null.

db.collection.aggregate([
  {
    "$unwind": "$characteristics"
  },
  {
    "$match": {
      $expr: {
        $eq: [
          {
            "$ifNull": [
              "$characteristics.score",
              null
            ]
          },
          null
        ]
      }
    }
  },
  {
    "$group": {
      _id: null,
      documents: {
        $push: {
          "employee": "$employee",
          "name": "$characteristics.name",
          "step": "$characteristics.step",
          
        }
      }
    }
  },
  {
    $project: {
      _id: false
    }
  }
])

Here is the Mongo playground for your reference.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Gibbs
Solution 2 ray

Related Questions