CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
Aks
Aks

Reputation: 69

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

Upvotes: 1

Views: 1313

Answers (2)

ray
ray

Reputation: 15217

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.

Upvotes: 1

Gibbs
Gibbs

Reputation: 22956

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

Upvotes: 1

Related Questions