Loading...

XML

Word

Printable

JSON

Operating System:
ALL
Steps To Reproduce:
Hide

Create a new collection.

Create index :

db.debug.createIndex({ 'item': 1 })

Insert data :

db.debug.insert({ item: "card", qty: 15 }) db.debug.insert({ item: "table", qty: 15 }) db.debug.insert({ item: "piano", qty: 15 }) db.debug.insert({ item: "house", qty: 15 }) db.debug.insert({ item: "test", qty: 15 })

Query :

db.debug.explain().aggregate([{ "$match": {"$expr": {"$in": ["$item", ["card", "test"]]}} }])

Wining Plan

"winningPlan" : { "stage" : "COLLSCAN", "filter" : { "$expr" : { "$in" : [ "$item", { "$const" : [ "card", "test" ] } ] } }, "direction" : "forward" },
Show
Create a new collection. Create index : db.debug.createIndex({ 'item' : 1 }) Insert data : db.debug.insert({ item: "card" , qty: 15 }) db.debug.insert({ item: "table" , qty: 15 }) db.debug.insert({ item: "piano" , qty: 15 }) db.debug.insert({ item: "house" , qty: 15 }) db.debug.insert({ item: "test" , qty: 15 }) Query : db.debug.explain().aggregate([{ "$match" : { "$expr" : { "$in" : [ "$item" , [ "card" , "test" ]]}} }]) Wining Plan "winningPlan" : { "stage" : "COLLSCAN" , "filter" : { "$expr" : { "$in" : [ "$item" , { "$ const " : [ "card" , "test" ] } ] } }, "direction" : "forward" },

An $expr expression in a query will not use an index when used with $in.

The goal was to used it in a $lookup pipeline.

The problem seems to exists in 3.6.3 as well as 4.0.2

duplicates

SERVER-32549 allow $expr with $in expression use a non-multikey index