Mongo array querying with an index

Question

Can someone explain this behavior?

db.inventory.insertMany( [
   { item: "journal", instock: [ { warehouse: "A", qty: null }, { warehouse: "C", qty: 15 } ] },
   { item: "notebook", instock: [ { warehouse: "C", qty: 5 } ] },
   { item: "paper", instock: [ { warehouse: "A", qty: 60 }, { warehouse: "B", qty: 15 } ] },
   { item: "planner", instock: [ { warehouse: "A", qty: 40 }, { warehouse: "B", qty: 5 } ] },
   { item: "postcard", instock: [ { warehouse: "B", qty: 15 }, { warehouse: "C", qty: 35 } ] }
]);

Copied from https://docs.mongodb.com/v4.2/tutorial/query-array-of-documents/

but the first item, instock.0.qty is replaced with null.

Consequently, this query produces strange results:

> db.inventory.find({"instock.0.qty": null})
{ "_id" : ObjectId("6194329212c7421dbaaeac81"), "item" : "journal", "instock" : [ { "warehouse" : "A", "qty" : null }, { "warehouse" : "C", "qty" : 15 } ] }
{ "_id" : ObjectId("6194329212c7421dbaaeac82"), "item" : "notebook", "instock" : [ { "warehouse" : "C", "qty" : 5 } ] }
{ "_id" : ObjectId("6194329212c7421dbaaeac83"), "item" : "paper", "instock" : [ { "warehouse" : "A", "qty" : 60 }, { "warehouse" : "B", "qty" : 15 } ] }
{ "_id" : ObjectId("6194329212c7421dbaaeac84"), "item" : "planner", "instock" : [ { "warehouse" : "A", "qty" : 40 }, { "warehouse" : "B", "qty" : 5 } ] }
{ "_id" : ObjectId("6194329212c7421dbaaeac85"), "item" : "postcard", "instock" : [ { "warehouse" : "B", "qty" : 15 }, { "warehouse" : "C", "qty" : 35 } ] }

EDIT: I'm expecting only one result, yet they all come back.

Yea, that was intentional. I'm only expecting one result in the query, yet all the documents are returned. — Feliks Yaretskiy
– Feliks Yaretskiy, Commented Nov 16, 2021 at 23:00

Yong Shun · Accepted Answer · 2021-11-17 02:24:06Z

1

A similar issue was reported in this Jira ticket: Incorrect filter result when using a null match inside an indexed array field.

The proposed suggestion was using $type to check the value which is null.

db.collection.find({
  "instock.0.qty": {
    $type: "null"
  }
})

Sample Mongo Playground

answered Nov 17, 2021 at 2:24

Yong Shun

54.3k6 gold badges37 silver badges65 bronze badges

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

Collectives™ on Stack Overflow

Mongo array querying with an index

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related