MongoDB $elemMatch $in

Question

So, I have a database with a load of arrays in documents in it. I want to find entire documents where my queries are an exact match for one or more array elements using $in.

So, document structure:

{
  "_id": "76561198045636214",
  "timecreated": 1311148549,
  "unusual": [
    {
      "id": 1960169991,
      "original_id": 698672623,
      "defindex": 313,
      "_particleEffect": 19
    },
    {
      "id": 965349033,
      "original_id": 931933064,
      "defindex": 363,
      "_particleEffect": 6
    }
  ]
}

I have a lot of documents like this, I want to find where a document has an array containing both a defindex 313 and a _particleEffect 19 in one array entry which means I'd have to use $elemMatch.

I also want to be able to search for many different array combinations at once so for example an array with a defindex of 363 and a _particleEffect of either 19 or 6 which means I have to use $in.

However, when I try to put $elemMatch and $in in a query, elemMatch will have nothing to do with it since it won't work on an array. I haven't been able to do it.

My attempts so far:

{unusual:{$all:[{"defindex":{"$in":[361,378]}},{"_particleEffect":{"$in":[30,0]}}]}}

(my latest attempt, simply does not work.)

{"$and":[{"unusual.defindex":{"$in":[361,378]}},{"unusual._particleEffect":{"$in":[[30,36]]}}]}

and many more where I tried loads of combinations with $elemmatch and $and.

(finds items in the unusual array but ignores array delimitation IE it will return a document where multiple items will be used to satisfy the condition (so at least one item with a defindex that matches and one item that has the effect.))

I've spend a day and a half on this and have come really far, even finding a question which was almost the same as mine but was missing any mention of an $in part. -> MongoDB: Match multiple array elements

tl;dr: is there a way to effectively do $in + $elemMatch?

Thanks for reading and being able to read my somewhat badly formatted post, thanks.

Answer 1

You can use different syntax than the one you're trying that achieves the same result but doesn't run into the limitation in SERVER-3544.

Use this syntax:

db.collection.find({ "unusual": {"$elemMatch":{"defindex":363,"_particleEffect":{"$in":[6,19]}  }} })

This will match any document which has an array element with both 313 and either 6 or 19.

It also works with {$in:[]} for both defindex and _particleEffect, as long as you intend to match any combination of the two lists.

db.collection.find({ "unusual": {"$elemMatch":{"defindex":{"$in":[313,363]},"_particleEffect":{"$in":[6,19]}  }} })

Answer 2

https://jira.mongodb.org/browse/SERVER-3544

Welp, did a LOT of digging and it looks like that answers my question. You cannot do $in $elemmatch at this time.