CODEWITHSUNDEEP
mongodb
Ori Popowski
Ori Popowski

Reputation: 10662

MongoDB $in for multiple fields

If I have a collection with the following schema:

{
    "author": …,
    "title": …,
    "pages": …,
    "publishDate": …,
}

where author and title are a part of a compound index.

I want to find all documents whose author and title might be one of the following: 

"Melville", "Moby Dick"
"Orwell", "1984"
"Tolkien", "The Hobbit"

How can I do it using the "$in" statement? I could do it with "$or" but the performance is disastrous.

Upvotes: 3

Views: 75

Answers (1)

JohnnyHK
JohnnyHK

Reputation: 312075

You can do this with $in if you alter the schema to put that pair of keys into a separate subdocument:

{key: {author: 'Melville', title: 'Moby Dick'}, ...},
{key: {author: 'Orwell', title: '1984'}, ...},
{key: {author: 'Tolkien', title: 'The Hobbit'}, ...}

That way you can perform an $in query on key:

db.test.find({key: {$in: [
    {author: 'Melville', title: 'Moby Dick'},
    {author: 'Orwell', title: '1984'},
    {author: 'Tolkien', title: 'The Hobbit'}
]}})

Note that the order of the author and title fields is significant, so be sure to use consistent field ordering.

To make it performant, you can add a unique index on key:

db.test.createIndex({key: 1}, {unique: true})

Or, if you don't need the index to enforce uniqueness, it may be more efficient to create a hashed index instead:

db.test.createIndex({key: 'hashed'})

Upvotes: 3

Related Questions