So I'm attempting to find all records who have a field set and isn't null.
I try using $exists
, however according to the MongoDB documentation, this query will return fields who equal null.
$exists does match documents that contain the field that stores the null value.
So I'm now assuming I'll have to do something like this:
db.collection.find({ "fieldToCheck" : { $exists : true, $not : null } })
Whenever I try this however, I get the error [invalid use of $not]
Anyone have an idea of how to query for this?
Use $ne
(for "not equal")
db.collection.find({ "fieldToCheck": { $ne: null } })
Suppose we have a collection like below:
{
"_id":"1234"
"open":"Yes"
"things":{
"paper":1234
"bottle":"Available"
"bottle_count":40
}
}
We want to know if the bottle field is present or not?
Ans:
db.products.find({"things.bottle":{"$exists":true}})
When <boolean> is true, $exists matches the documents that contain the field, including documents where the field value is null.
From the docs.
i find that this works for me
db.getCollection('collectionName').findOne({"fieldName" : {$ne: null}})
db.<COLLECTION NAME>.find({ "<FIELD NAME>": { $exists: true, $ne: null } })
This comment is written in 2021 and applies for MongoDB 5.X and earlier versions.
If you value query performance never use $exists (or use it only when you have a sparse index over the field that is queried. the sparse index should match the criteria of the query, meaning, if searching for $exists:true, the sparse index should be over field:{$exist:true} , if you are querying where $exists:true the sparse index should be over field:{$exist:false}
Instead use :
db.collection.find({ "fieldToCheck": { $ne: null } })
or
db.collection.find({ "fieldToCheck": { $eq: null } })
this will require that you include the fieldToCheck in every document of the collection, however - the performance will be vastly improved.
$exists
with full index and the reason.
I Tried to convert it into boolean condition , where if document with table name already exist , then it will append in the same document , otherwise it will create one .
table_name is the variable using which i am trying to find the document
query = { table_name : {"$exists": "True"}}
result = collection.find(query)
flag = 0
for doc in result:
collection.update_one({}, { "$push" : { table_name : {'name':'hello'} } } )
flag = 1
if (flag == 0):
collection.insert_one({ table_name : {'roll no' : '20'}})
aggregate example
https://mongoplayground.net/p/edbKil4Zvwc
db.collection.aggregate([
{
"$match": {
"finishedAt": {
"$exists": true
}
}
},
{
"$unwind": "$tags"
},
{
"$match": {
"$or": [
{
"tags.name": "Singapore"
},
{
"tags.name": "ABC"
}
]
}
},
{
"$group": {
"_id": null,
"count": {
"$sum": 1
}
}
}
])
Success story sharing
find
always returns: a collection of records, matching the criteria.{$exists: true}
is redundant, just{$ne: null}
is enough.