MongoDB $in for multiple fields

Question

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.

$or is how you do this, so if the performance using that is bad, your compound index is likely not being used. Have you check it with explain()? — JohnnyHK
– JohnnyHK, Commented Sep 26, 2015 at 19:05
Yes, it uses the index. But for 10,000 field pairs MongoDB just crashes — Ori Popowski
– Ori Popowski, Commented Sep 26, 2015 at 19:09
10,000! Wow, that's definitely going to be slow with $or. What are you trying to do? Maybe there's another way to approach this. — JohnnyHK
– JohnnyHK, Commented Sep 26, 2015 at 19:11
Well, the solution would definitely be to use a single value index and then use the $in statement. But it's a legacy system and that pair of fields is the only unique identifier in the collection. I could use the _id field because it's indexed but it may introduce subtle imprecisions because it's not a business identifier. I just wanted to find out if there's such an $in statement that might solve my problem — Ori Popowski
– Ori Popowski, Commented Sep 26, 2015 at 19:17

JohnnyHK · Accepted Answer · 2015-09-26 21:53:20Z

3

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'})

edited Sep 26, 2015 at 21:53

answered Sep 26, 2015 at 21:07

JohnnyHK

313k70 gold badges632 silver badges479 bronze badges

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

Collectives™ on Stack Overflow

MongoDB $in for multiple fields

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related