Why does $ne comparison against null not work in a $filter condition?

I have a User collection with the following structure.

{
    _id:5aeb04db3a336840e2a7fde8
    name:"John Doe"
    email:"john@gmail.com",
    login_activity: [
        {
            platform: "Windows,
            browser: "Chrome",
            browser_version: "95.0.232"
            time: 2021-05-22T00:00:00.000+00:00
        },
        {
            time: 2016-06-23T00:00:00.000+00:00
        },
        {
            platform: "Linux,
            browser: "Firefox",
            browser_version: "94.0.232"
            time: 2021-06-24T00:00:00.000+00:00
        },
        {
            platform: "Linux,
            browser: "Firefox",
            browser_version: "94.0.232"
            time: 2021-06-25T00:00:00.000+00:00
        },
    ]
}

I am trying to get a break up of the platform i.e Windows: 1, Linux: 2 etc. For that, I first need to filter out entries that do not have a platform attribute. I have the following $filter stage which does not seem to be working as expected.

{
    $set: {
        filtered_login_activities: {
            $filter: {
                input: "$login_activity",
                as: "login_session",
                cond: {
                    $ne: ["$$login_session.platform", null]
                }
            }
       }
   }
}

But for some reason, it is not filtering out the documents with the platform attribute not present. What am I doing wrong?

I noticed that if I use $eq: ["$$login_session.platform", "Windows], the filtering works as expected. It is just the $ne that is not working. I am running this query on Mongo 4.4 series.

First, see https://docs.mongodb.com/manual/tutorial/query-for-null-fields/. In your case, it looks like you want to test for existence rather than null value.

However $exists, as far as I know, exists only for MQL. I read somewhere (I could not trace back where), that $exists is looking at the type to be equal or not to “missing”. This means that if you replace

with

$ne: [ { "$type" : "$$login_session.platform" } , "missing" ]

then you should get the intended filtering.

And for the next time you publish documents make sure we can insert them directly into our installation. I had to add double quotes for the platform field values and add commas after the browser_version values. It is little details but it is appreciated when we do not have to editing work before starting to investigate.

2 Likes

Thank you very much for figuring out the solution.

And for the next time you publish documents make sure we can insert them directly into our installation. I had to add double quotes for the platform field values and add commas after the browser_version values. It is little details but it is appreciated when we do not have to editing work before starting to investigate.

Absolutely. Sorry for the inconvenience. I did not notice it until you pointed it out. Won’t happen next time.

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.