Query where "field" == nil

Is it possible to query where a field is == to nil?

I have a column named “displayUntil” where it takes a Date.

Screen Shot 2021-12-24 at 1.56.18 PM

I get a “Generic parameter ‘T’ could not be inferred” error when I try to find empty field by == nil.

What is the correct way of constraining to these fields?

I can’t seem to find any example in ParseSwift Playgrounds

Instead of leaving field as undefined, You can set a default value for that field with either using beforeSave trigger or editing the schema. And when you wanna query you can use:

query.equalTo("field", defaultValue);

thought about that. But the problem is that field holds a “valid until” date. so any date I place there will be when it is “valid until”. some posts don’t have a validity or expiry date. So I need it nil.

Maybe set a date 1000 years from now? Posts still will be valid and if your app/website survives 1000 years, somebody in the future will take care of it.

You should use doesNotExist:


Perhaps to extend the answer I have been looking to the docs on indexing and query performance here and if I understand it right, the value nil is not using indexing and therefore doesNotExist would perform slower on a large number of objects?

I am asking, because I would like to implement “cleaning” cloud job where the objects are older than XY and have a certain field nil. I believe that for the better performance, the version with default value as @uzaysan proposed is more suitable.

Please correct me if I am wrong.

1 Like

Yes. You are right. İ believe parse server uses $exists keyword for querying the missing fields. According to the MongoDB docs (https://docs.mongodb.com/manual/reference/operator/query/exists/) this keyword cannot use default indexes and as a result of query all documents will be scanned. İn order to use indexes you should create sparse index.

Only some of the Parse provided fields have index’s created by Parse. Most fields created by developers, like displayUntil won’t have an index unless the developer adds it manually. It’s important to note there are pros/cons to index’s and they aren’t always needed. In addition, most of the Parse docs for performance considerations are written for mongo, so if you are using mongo they may still apply. Postgres uses should look into what makes since.

@lsmilek1 your comment mentions “cloud job” which means it’s using the JS SDK. I’m sure there are different ways to accomplish the query using the JS SDK. The original question in this post is how to do this using the Swift SDK.

Lastly, there is usually multiple ways to construct a query, if you are trying to optimize performance, you should use explain to see what works best for you.

Also, using equatTo with a Date will probably start to yield problems at some point as it’s hard to have an “exact” date (seconds, milliseconds, etc). It’s best to use <, <=, >, >= with a Date type.

1 Like

forgive the noob in me, but what is this “explain” you mentioned and where can I find information about it?

I am trying to see if the query I run is optimized.

Cheers and Happy New Year!

The API documentation for explain is here. Mongos documentation does a decent job of describing explain:

You can see the playgrounds for how to use it on a query:

You will probably want to add the AnyCodable package to your app to decode the results from an explain.

Note that there’s currently a bug on the server that prevents the Swift SDK from explaining a mongo query. So if you are using Mongo, you can’t use the Swift SDK (unless you are using it through ParseCloud). The other SDKs conform to the bug on the server, so they work with Mongo. If you are using Postgres, there’s no problem. More info about the bug here:

and here:

Update: The Swift SDK 3.1.0 can now explain Mongo queries. See the PR for details:

Happy New Year!

1 Like

Thank you for additional clarification. The explain as well the importance of the individual query constrains is clear and nicely documented. I often use index on the field based on what a equalTo returns a rough but reasonably low number of documents and the fine filtering is then on not indexed fields. That way indexes do take less space than indexing multiple fields, but still perform well because the fine filtering scans relatively low number of results (even the filtering does through all results).

I would like to follow on this though:

I have looked into the documentation about the sparse indexes and I am a little confused about what you mean by creating them in this case. In principle I use them as they skip the documents where the field is undefined and therefore take less space. What i am not sure about now… when the field is undefined then no matter if the field is indexed completely or with sparse index (or in future perhaps partial index) it does not give any advantage for the "field" == nil scenario, right? To grab 50 in million documents once per day that has "field" == nil is still better to use default value, for example "field" == "tbd" and this would get indexed with both complete and sparse indexes.

I therefore beliveve that “In order to use indexes you should create sparse index.” was rather a general advice not related to "field" == nil scenario?

I was useing the explain here and here while deciding if I would need to implement ElasticSearch for a very complex query (think of multiple Tinder queries with different schemes combined) or the MongoDB performance would be sufficient.

It helped me to understand how the query performance works (how MongoDB grabs certain indexes). I just had to generate many artificial documents as I do not have my app ready yet.

All the best for 2022 to you all! :champagne:

In that regard you may want to consider MongoDB Atlas Search, which is built on Apache Lucene and provides advances search capabilities. Apache Lucene is also the basis for ElasticSearch.

1 Like

Thank you, Manuel, I completely missed the MongoDB Atlas Search when I was looking how to solve the architecture. I will definitely look into it and compare if it is better solution.

@jayson there’s a PR that will be released in the Swift SDK 3.0.0 that may better address your needs. The details are in the link:

Note that for QueryConstraint there are two added counterparts for exist and doesNotExist, isNotNull, and isNull. For Mongo users these constraints yield different results as isNull in your original question will get you your expected results, displayUtil == undefined || displayUtil == null. Using doesNotExist will only get you displayUtil == undefined. This becomes important based on if you set values to undefined or null on your server. If you are using Postgres, this doesn’t matter is much as $exist is treated as NOT NULL and there’s no difference between null or undefined.

I suspect this should also help address any performance issues @lsmilek1 brought up with performance and indexes mentioned in Query where "field" == nil - #6 by lsmilek1 as isNull and isNotNull should allow you take advantage of indexing on MongoDB (I’m guessing). I’m not a Mongo expert, so @Manuel feel free to chime in if my comments about Mongo performance is true or not. I don’t know how the other SDKs handle querying for null (or if they have the feature at all), so I’m not sure how you take advantage of this in Cloud Code, though looking at the JS query constraints may provide some insight:

The Swift SDK will have this available on the main branch shortly and then in 3.0.0 sometime this month. You can point your SPM to the main branch in the mean time.

1 Like

It’s awesome you guys got a fix for this. The discussion here was going over my head a bit. :smiley: