feat: add null value filter

[sadakchap]

Feature request https://github.com/parse-community/parse-dashboard/issues/1658

Added new filter to filter records if field contains null value.

[mtrezza]

Thanks for this PR.

In MongoDB, the value null is sometimes treated the same as if the field does not exist at all. In other words, these two documents are sometimes treated as identical:

{
  a: 1,
  b: null
}

{
  a: 1
}

That means from a MongoDB perspective there may not be a difference between the Parse Dashboard filters "does not exist" and "is null". To unambiguously query for objects with a field of content null we would have to query for the BSON Type 10.

The distinction in MongoDB is:

• a) field : { $type: 10 }: returns documents where the field has value null.
• b) field : { $exists: false }: returns documents where the field does not exist.
• c) field : null: returns documents where the field either has value null or does not exist.

Your video shows a filer for null values, but the filer description isNull is ambiguous - which of the filters above are applied? We would have to look behind the scenes into Parse Server MongoDB adapter to see how the query is ultimately constructed. I suspect it is c), which would in that case overlap with the "does not exist" filter.

Another aspect to consider is how that plays with the Postgres adapter, or whether that is something unique to MongoDB.

[sadakchap]

In MongoDB, the value null is sometimes treated the same as if the field does not exist at all. In other words, these two documents are sometimes treated as identical:

True. That's why we are filtering for both exists & equal to null value. If filtering field is age then we are sending a query like this.

where: {
  $and: [
      age: { $exists: true },
      age: null
   ]
}

Your video shows a filer for null values, but the filer description isNull is ambiguous - which of the filters above are applied?

Could you please elaborate? I don't think I understand the question properly.

[mtrezza]

That's why we are filtering for both exists & equal to null value.

Do you mean that is how you personally use the filter to query for a)?

It may return the same results, but the correct - because more efficient - query for this is:

where: {
    age: { $type: 10 }
}

[sadakchap]

Do you mean that is how you personally use the filter to query for a)?

yes.

It may return the same results, but the correct - because more efficient - query for this is:

where: {
    age: { $type: 10 }
}

Yes, you are correct! But, How do I send a query like this, I searched in the docs but couldn't find anything that would query on type of the field.

[mtrezza]

Not sure whether Parse Server supports this yet, you would have to look into the code, it may exist but not be in the docs. However, both filters (a) and (c) can be useful, so we may well offer both filters in the dashboard at some point in the future.

Therefore I suggest this naming to make them distinguishable:

a) field : { $type: 10 } filter name: is null c) field: null filter name: does not exist or is null

If later someone adds filter (a), they can name the filter is null and it won't conflict with the filter name in this PR. I don't know what this means for Postgres or other DBs, but it would be accurate for MongoDB.

[sadakchap]

Not sure whether Parse Server supports this yet, you would have to look into the code, it may exist but not be in the docs.

I look into this file, but I couldn't find any thing that would query on type.

Therefore I suggest this naming to make them distinguishable:

a) field : { $type: 10 } filter name: is null c) field: null filter name: does not exist or is null

If later someone adds filter (a), they can name the filter is null and it won't conflict with the filter name in this PR.

Sounds good :+1:, so should I change the current query as well for does not exist or is null option from

query.exists(field);
let nullQuery = new Parse.Query(className);
nullQuery.equalTo(field, null); 
query = Parse.Query.and(query, nullQuery);

to

query.equalTo(field, null)

as the current query fetches records only with null value.

[mtrezza]

should I change the current query as well for does not exist or is null option

Yes I think so. Can one still query only for null values by adding the field exists filter additionally for the same field in the dashboard?

[sadakchap]

Can one still query only for null values by adding the field exists filter additionally for the same field in the dashboard?

I think only one filter is allowed on a field at a time. No, I don't think it is possible.

[mtrezza]

I think only one filter is allowed on a field at a time. No, I don't think it is possible.

You could additionally add the is null filter if we find out how to do where { age: { $type: 10 } }. I think it should be already possible, maybe with a REST request, if the JS SDK doesn't support this yet.

If it really isn't possible, then you could add this:

query.exists(field);
let nullQuery = new Parse.Query(className);
nullQuery.equalTo(field, null); 
query = Parse.Query.and(query, nullQuery);

with a comment that this is a workaround because where { age: { $type: 10 } } is not possible with the JS SDK.

Then we could have both filters (is null; does not exist or is null) in the dashboard with this PR.

[mtrezza]

@sadakchap Do you think we can resolve any open questions here? I think this PR is almost ready for merge, it would be great to have it in the upcoming release of Parse Dashboard.

[sadakchap]

@sadakchap Do you think we can resolve any open questions here? I think this PR is almost ready for merge, it would be great to have it in the upcoming release of Parse Dashboard.

This pr was originally to add a feature through which users can filter records with only null value.

But, we decided to add a filter dneOrNull, on which user a add one more filter exists => then user should be getting records with only null value. But, that is not the case, $exists and $eq query seems to overriding each other. Please see https://github.com/parse-community/parse-dashboard/pull/1721#discussion_r646628396 .

I also raised an issue in Parse-SDK-JS. But, I think I am really not the right person to solve this issue.

For this feature, maybe we can try coupling $exists & $eq with $and. Something like this https://github.com/parse-community/parse-dashboard/pull/1721/commits/38d2f16da23ebbfa6ce3d13e554c2f31896e2310 , maybe ?

[mtrezza]

@sadakchap So maybe we can finalize this PR like this:

• Add a filter condition "does not exist or is null" for (c) with this PR
• Add a filter condition "is null" for (b) in a later PR once the Parse JS SDK issue is fixed and supports this properly

What do you think?

[sadakchap]

Thanks @mtrezza .

I was working on this issue's PR. I was able to to clear all unit test cases. Right now, I think only LiveQuery Integration test cases are failing.

I know that, this is open from long time, but If we can wait just 1 more week ( so that I can try to fix those failing integration test cases). It would be really nice. Or else we can do as you have suggested(incase it takes more than a week).

[mtrezza]

Sure. Let's wait as you suggest.

[cbaker6]

Another aspect to consider is how that plays with the Postgres adapter, or whether that is something unique to MongoDB.

For Postgres, the server treats doesNotExist and null the same, so I believe the filters here will produce the same results, which should be fine. I discuss more about this here: https://community.parseplatform.org/t/query-where-field-nil/2317/16?u=cbaker6