Incorrect null equality check for fields of objects inside arrays

Working with Data
1

The check whether a field is null, evaluates always to true when the field is inside an object inside an array.

So given the following data:

[
  {
    "xs": [
      {
        "name": "a"
      }
    ]
  },
  {
    "xs": [
      {
        "name": null
      }
    ]
  },
  {
    "xs": [
      {}
    ]
  }
]

when performing the query:

db.collection.find({
  "xs.0.name": {
    $eq: null
  }
})

one would expect only the 2nd and 3rd document to be returned, but all of the documents are returned.

The following works as expected

db.collection.find({
  "$or": [
    {
      "xs.0.name": {
        $exists: false
      }
    },
    {
      "xs.0.name": {
        $type: "null"
      }
    }
  ]
})

Am I missing something?

2

Hello @Apostolos_Livanios ,
yes, your query is right but you only expect one object inside the array a name field inside the object only once so you can follow the query which puts checks on the name field inside any index.

{
  "$or": [
    {
      "xs.name": {
        $exists: false
      }
    },
    {
      "xs.name": {
        $type: "null"
      }
    }
  ]
}
3

Hey @SAMRAT_N_A ,

thanks for the response. I’m aware of that, but I think my example was too simple so my intention wasn’t clear.

I don’t want to check any index, I want to check for a specific index. So a more accurate example would be this:

Data:

[
  {
    "xs": [
      {
        "name": null
      },
      {
        "name": "a"
      }
    ]
  },
  {
    "xs": [
      {
        "name": "b"
      },
      {
        "name": null
      }
    ]
  },
  {
    "xs": [
      {},
      {}
    ]
  }
]

Query:

db.collection.find({
  "xs.1.name": {
    $eq: null
  }
})

The expected result would be to get only the 2nd and 3rd document, but all the documents are returned.

Workaround Query:

db.collection.find({
  "$or": [
    {
      "xs.1.name": {
        $exists: false
      }
    },
    {
      "xs.1.name": {
        $type: "null"
      }
    }
  ]
})

This returns the expected result.

So now in this example this query is not a valid approach since we don’t specify the index.

db.collection.find({
  "$or": [
    {
      "xs.name": {
        $exists: false
      }
    },
    {
      "xs.name": {
        $type: "null"
      }
    }
  ]
})

and the question remains, why doesn’t the following return the expected results?

db.collection.find({
  "xs.1.name": {
    $eq: null
  }
})
4

There’s been a bug open for this issue https://jira.mongodb.org/browse/SERVER-27442 , so I’ll just add a short description of what’s happening based on that bug for anyone else that comes across this.

Making the 1st example smaller by focusing only the 1st document in the data (which is the one that is included even thought it shouldn’t):
Data:

[
  {
    "xs": [
      {
        "name": "a"
      }
    ]
  }
]

Query:

db.collection.find({
  "xs.0.name": {
    $eq: null
  }
})

In this case what will happen is we’re going to check 2 possibilities and if any of them match, then the condition will be true:

  1. We get the value of xs → out of that we get the 0th element of the array → out of that we get the name field. The value of this is “a” which is not null, so this is false
  2. In this case we do something like “xs.*.‘0’.name”, meaning we get the value of xs → we get the values of all the elements in the array → for each object in the array we get the value for the key “0” if it exists → we get the value for the key “name”. In this case this would result to null, so the condition is true

So the whole condition evaluates to true and that’s why the document will be returned.

This can be validated by trying the query with the modified example bellow

[
  {
    "xs": [
      {
        "name": "a",
        "0": {
          "name": "b"
        }
      }
    ]
  }
]

In this case the document won’t be matched, because neither of the 2 cases will evaluate to true.

1 Like
5

@Apostolos_Livanios, I appreciate your help. This will help me understand how MongoDB works with array indexes.