CODEWITHSUNDEEP
mongodb
dabal
dabal

Reputation: 420

mongo - count return no docoument found instead of 0

In SQL query

select count(*) from table where id=1

would return 0 as result where there isn't any record with such id.

I would like to get exactly the same behavior but in mongo. Unfortunately I can only use aggregate function.

I was trying something like this

db.collection.aggregate([
  {
    "$match": {
      "key": 1
    }
  },
  {
    $count: "s"
  }
])

It works but only with records with key:1 but when this key does not exist there is "no document found"

Upvotes: 2

Views: 2076

Answers (3)

user20578603
user20578603

Reputation: 11

I did it by using $facet,$project and when there were no documents to project it was showing undefined, so I used $ifNull expression. I've kept zero value for replacement expression value (see the $ifNull docs).

db.collection.aggregate([
  {
    "$facet": {
      "keyFound": [
        {
          "$match": {
            "key": 1
          }
        },
        {
          "$count": "count"
        }
      ]
    }
  },
  {
    "$project": {
      "keyFoundCount": {
        "$ifNull": [
          {
            "$arrayElemAt": [
              "$keyFound.count",
              0
            ]
          },
          0
        ]
      }
    }
  }
])

testCodeHere

Upvotes: 1

Takis
Takis

Reputation: 8695

I think the right way to do it is with the driver code, if you get empty results you make that document {"count" : 0} you dont need i think to do anything in the database.

Another solution can be this (replace the 5 with the key value you want)

Test code here

  • creates 2 groups the matched(count>0) and the not matched(count=0)
  • sort by {"count" : -1}
  • take the first, if there was a match count will be the one matched, else it will be 0
aggregate(
[ {
  "$group" : {
    "_id" : {
      "$cond" : [ {"$eq" : [ "$key", 5 ]}, "$key", "not_match" ]
    },
    "count" : {
      "$sum" : {"$cond" : [ {"$eq" : [ "$key", 5 ]}, 1, 0 ]}
    }
  }
 }, 
 {"$sort" : {"count" : -1}},
 {
  "$group" : {
    "_id" : null,
    "count" : {"$first" : "$count"}
  }
 },
 {"$project" : {"_id" : 0}}
])

Upvotes: 0

J.F.
J.F.

Reputation: 15177

You can use this aggregation query using $facet to create two possible ways: If document exists or if document does not exists.

  • First $facet to create the two ways
  • Into notFound way the result will always be {count: 0} ; into found way there is the match
  • Then $replaceRoot merging results to get desired value.
db.collection.aggregate([
  {
    "$facet": {
      "notFound": [
        {
          "$project": {
            "_id": 0,
            "count": {
              "$const": 0
            }
          }
        },
        {
          "$limit": 1
        }
      ],
      "found": [
        {
          "$match": {
            "key": 1
          }
        },
        {
          "$count": "count"
        }
      ]
    }
  },
  {
    "$replaceRoot": {
      "newRoot": {
        "$mergeObjects": [
          {
            "$arrayElemAt": [
              "$notFound",
              0
            ]
          },
          {
            "$arrayElemAt": [
              "$found",
              0
            ]
          }
        ]
      }
    }
  }
])

Example here where key exists and here where key doesn't exists.

Also I've tested with this using $ifNull instead of $mergeObjects and seem works ok too.

Upvotes: 1

Related Questions