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.

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.

CodePudding user response：

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

CodePudding user response：

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.