Question 117925 in Mongodb Question posted in
The official documentation can be found here.

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

Tags:

2

Answers


  1. 0 
    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

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search