1

I have a bunch of documents that consists of fields "Data" and "Text". Some docs have either one, while some have neither of these. How would I query through pyMongo to get documents that HAVE the field "Data" and DO NOT HAVE the field "Text"?

I've tried the below queries but mongo doesn't return any files.

METHOD 1:

files = collection.find({"Data": {"$exists": "true"}, {"Text": {"$exists": "false"}})

for file in files:
    print(file)

METHOD 2:

files = collection.find({"$and": [{"Data": {"$exists": "true"}}, {"Text": {"$exists": "false"}}]})

for file in files:
    print(file)

NOTE: I'm currently trying the query on a database where no collections have the "Text" field (yet), but the query should still work w.r.t the logic. It being:

Return docs with "Data" AND not having "Text"

Aakash Dusane
  • 388
  • 4
  • 17
  • 1
    Try [`"$exists": true`](https://docs.mongodb.com/manual/reference/operator/query/exists/) instead of `"$exists": "true"` – Sergio Tulentsev Dec 27 '18 at 11:03
  • hey, i'm using pyMongo which is a native python driver for MongoDB and it accepts commands only in string. The command **files = collection.find({"Data": {"$exists": "true"})** works perfectly fine. – Aakash Dusane Dec 27 '18 at 12:12

3 Answers3

2

I tried using None instead of False as Anushka pointed out and it worked.

files = coll.find({"Data": {"$exists": "true"}, "Text": None})

If anyone knows why my initial attempt as stated below did not work, please comment on this answer. Thanks. 

files = collection.find({"Data": {"$exists": "true"}, {"Text": {"$exists": "false"}})
Aakash Dusane
  • 388
  • 4
  • 17
  • 1
    Ah, just noticed, that attempt didn't work about you wrapped the `Text` part into a hash/object of its own (and you're missing a closing curly brace somewhere there) – Sergio Tulentsev Dec 27 '18 at 16:47
  • You're right! I didn't notice that there. But I substituting None/Null with "false" in the correct version of the code (see accepted answer) but still doesn't work. Strange. – Aakash Dusane Dec 28 '18 at 13:30
1

You can refer this link : https://docs.mongodb.com/manual/tutorial/query-for-null-fields/

Try this

  collection.find({"Data": {"$exists": "true"}, {"Text": null })

query matches documents that either contain the Text field whose value is null or that do not contain the Text field.

If you could not resolve this then please post your data store in collections for reference and your schema structure.this will help us if you have problem with it.

Sergio Tulentsev
  • 226,338
  • 43
  • 373
  • 367
Anushka Ahir
  • 136
  • 7
-1

in using $exists:true any type of index usage goes out of equation. You can modify the query as files = collection.find({"Data":{$ne:null}, Text:null})

Rajat Rastogi
  • 17
  • 5
  • `$ne: null` is not any easier to be backed by index than `$exists: true/false`, it seems. (in fact, it used to _not_ use index until a few versions ago). Do you have sources to back this claim? – Sergio Tulentsev Dec 27 '18 at 11:19
  • I dont have any sources ,but in general when I run the query in prod $exists runs slower than e:{$nin:[null,""]} when e is indexed . Secondly null values are not indexed hence all the values present in index make the cut. – Rajat Rastogi Dec 27 '18 at 16:35
  • "Secondly null values are not indexed" - they are, [even in a sparse index](https://docs.mongodb.com/manual/core/index-sparse/) – Sergio Tulentsev Dec 27 '18 at 16:40
  • Tested on my machine and, indeed, `$exists` query uses the corresponding index. – Sergio Tulentsev Dec 27 '18 at 16:44
  • Please check :https://stackoverflow.com/questions/8176310/ , Please can you confirm your claim – Rajat Rastogi Dec 27 '18 at 17:09
  • I trust mongo's explain plan over 7 year old answer. And so should you. – Sergio Tulentsev Dec 27 '18 at 18:44