MongoDB not querying `null` within a array at a given posision correctly

Question

I'm having a hard time understanding why the following happens within mongodb when dealing with nulls and array indexes within queries.

Say we have the following:

> db.test.find()
{ "_id" : ObjectId("5852da24507d8c27f4e3c357"), "item" : null }
{ "_id" : ObjectId("5852da2d507d8c27f4e3c358"), "item" : { "something" : true } }
{ "_id" : ObjectId("5852da33507d8c27f4e3c359") }

When I try to find items that are null I get ObjectId("5852da24507d8c27f4e3c357") and ObjectId("5852da33507d8c27f4e3c359") which is as expected based on https://docs.mongodb.com/v3.2/tutorial/query-for-null-fields/

> db.test.find({"item" : null})
{ "_id" : ObjectId("5852da24507d8c27f4e3c357"), "item" : null }
{ "_id" : ObjectId("5852da33507d8c27f4e3c359") }

But when I try to do the same but within a array with a given index, It's completely different set of results:

Say we have the following:

> db.test.find()
{ "_id" : ObjectId("5852dbe1507d8c27f4e3c35c"), "a" : [ { "something" : true }, { "something" : true }, null ] }
{ "_id" : ObjectId("5852dbf4507d8c27f4e3c35d"), "a" : [ { "something" : true }, { "something" : true }, { "something" : true } ] }
{ "_id" : ObjectId("5852dbfb507d8c27f4e3c35e"), "a" : [ { "something" : true }, { "something" : true } ] }

But If I do the query based on the index of 2 is null, we still get returned all the documents:

> db.test.find({"a.2": null})
{ "_id" : ObjectId("5852dbe1507d8c27f4e3c35c"), "a" : [ { "something" : true }, { "something" : true }, null ] }
{ "_id" : ObjectId("5852dbf4507d8c27f4e3c35d"), "a" : [ { "something" : true }, { "something" : true }, { "something" : true } ] }
{ "_id" : ObjectId("5852dbfb507d8c27f4e3c35e"), "a" : [ { "something" : true }, { "something" : true } ] }

This to me doesn't seem like its working as expected? But we can however see that the index position works correctly as we can do the following query:

> db.test.find({"a.2": {something:true}})
{ "_id" : ObjectId("5852dbf4507d8c27f4e3c35d"), "a" : [ { "something" : true }, { "something" : true }, { "something" : true } ] }

Is this a bug within mongodb and dealing with arrays and nulls?

I am not sure if this is the expected behavior, perhaps a bug so I created JIRA SERVER-27442 — Sede
– Sede, Commented Dec 17, 2016 at 10:55

Asya Kamsky · Accepted Answer · 2019-05-21 17:49:46Z

1

The problem is even though you mean for db.test.find({"a.2": null}) to be query based on the index of 2 is null that's not what it actually means. What it means is either index of 2 is null or there is a field name 2 of the object which is null. In array querying we reach into arrays (so saying a:2 should match value 2 but also any array that has an element 2. Here the matcher is falling into the "I'm matching for subfield 2 but this is an array and therefore I should check every element of the array to see if it's field name 2, value null.

This is an unfortunate ambiguity in the query language that makes the response not really incorrect in certain scenarios, and until the language is enhanced with syntax that allows you to unambiguously specify you are querying only for array position 2 or only for field name 2 querying a.2 combined with null semantics will give the result you see.

answered May 21, 2019 at 17:49

Asya Kamsky

42.4k5 gold badges113 silver badges133 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Kevin Smith Over a year ago

Totally makes sense, and just tried it out too... didn't even realize I could have a document with a field on a number 🤯. Thanks for explaining what the query engine is doing behind the scenes! 👍

Asya Kamsky Over a year ago

we will need to add syntax to query by position and syntax to query by name (that happens to be numeric) but in a way that's not backwards breaking - that's why it's not a simple or short term fix :(

Collectives™ on Stack Overflow

MongoDB not querying `null` within a array at a given posision correctly

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related