MongoDB's $elemMatch does not find the proper result

Question

Below is a small MongoDB database:

> db.sss.find().pretty()
{
    "_id" : ObjectId("543f53b8711761110238be74"),
    "name" : "A",
    "pos" : 828288,
    "s_type" : 1,
    "sub_name" : "B01",
    "type" : "Test",
    "x_type" : 7,
    "chr" : [
        {
            "5" : "C"
        },
        {
            "6" : "T"
        }
    ]
}
{
    "_id" : ObjectId("543f53b8711761110238be75"),
    "name" : "A",
    "pos" : 171878,
    "s_type" : 3,
    "sub_name" : "B01",
    "type" : "Test",
    "x_type" : 8,
    "chr" : [
        {
            "5" : "C"
        },
        {
            "6" : "T"
        }
    ]
}
{
    "_id" : ObjectId("543f53b8711761110238be76"),
    "name" : "A",
    "pos" : 871963,
    "s_type" : 3,
    "sub_name" : "B01",
    "type" : "Test",
    "x_type" : 9,
    "chr" : [
        {
            "5" : "A"
        },
        {
            "6" : "G"
        }
    ]
}
{
    "_id" : ObjectId("543f53b8711761110238be77"),
    "name" : "A",
    "pos" : 1932523,
    "s_type" : 1,
    "sub_name" : "B01",
    "type" : "Test",
    "x_type" : 10,
    "chr" : [
        {
            "4" : "T"
        },
        {
            "5" : "A"
        },
        {
            "6" : "X"
        }
    ]
}
{
    "_id" : ObjectId("543f53b8711761110238be78"),
    "name" : "A",
    "pos" : 667214,
    "s_type" : 1,
    "sub_name" : "B01",
    "type" : "Test",
    "x_type" : 14,
    "chr" : [
        {
            "4" : "T"
        },
        {
            "5" : "G"
        },
        {
            "6" : "G"
        }
    ]
}

The above database has been created with below script:

from pymongo import MongoClient
from collections import defaultdict


db = MongoClient().test
sDB = db.sss

r = [["Test", "A", "B01", 828288,  1,    7, 'C', 5],
    ["Test", "A", "B01", 828288,  1,    7, 'T', 6],
    ["Test", "A", "B01", 171878,  3,    8, 'C', 5],
    ["Test", "A", "B01", 171878,  3,    8, 'T', 6],
    ["Test", "A", "B01", 871963,  3,    9, 'A', 5],
    ["Test", "A", "B01", 871963,  3,    9, 'G', 6],
    ["Test", "A", "B01", 1932523, 1,   10, 'T', 4],
    ["Test", "A", "B01", 1932523, 1,   10, 'A', 5],
    ["Test", "A", "B01", 1932523, 1,   10, 'X', 6],
    ["Test", "A", "B01", 667214,  1,   14, 'T', 4],
    ["Test", "A", "B01", 667214,  1,   14, 'G', 5],
    ["Test", "A", "B01", 667214,  1,   14, 'G', 6]]

for i in r:
    sDB.update({'type': i[0],
          'name': i[1],
          'sub_name': i[2],
          'pos': i[3],
          's_type': i[4],
          'x_type': i[5]},
          {"$push": {"chr":{str(i[7]): i[6]} }}, True)

I started to write a query for the following conditions:

• x_type:7 and x_type:9 are correct, because chr.5 and chr.6 have different characters and neither of them has a character X.
• x_type:8 would be correct, but is out of range (200000 to 2000000)
• x_type:10 is not valid, because chr.6 contain a X
• x_type:14 is not valid, because chr.5 and chr.6 have the same characters

However, I did not get what I wanted with the following draft query:

> db.snps.find({"pos": {$gte: 200000, $lt: 2000000}}, {"chr":{$elemMatch:{"6":{$ne: "X"}}}}).pretty()
{
    "_id" : ObjectId("543f53b8711761110238be74"),
    "chr" : [
        {
            "5" : "C"
        }
    ]
}
{
    "_id" : ObjectId("543f53b8711761110238be76"),
    "chr" : [
        {
            "5" : "A"
        }
    ]
}
{
    "_id" : ObjectId("543f53b8711761110238be77"),
    "chr" : [
        {
            "4" : "T"
        }
    ]
}
{
    "_id" : ObjectId("543f53b8711761110238be78"),
    "chr" : [
        {
            "4" : "T"
        }
    ]
}

What did I do wrong?

Answer 1

You seem to be using the wrong form of $elemMatch here as what you are using is the "projection" form and is used to select the first match in the array to the conditions that you supply. The only "query" condition you have specified here is that the "pos" value falls within the requested range.

If what you want is that both the conditions are present where the document has a "pos" value within the range and that there is no "X" value in "chr.6". The final and additional clause is a little tricky as you are comparing values of array elements. But this is possible with the JavaScript evaluation of $where and fairly simple as long as there are only and always two elements:

db.snps.find({
    "pos": { "$gte": 200000, "$lt": 2000000 },
    "chr.6": { "$ne": "X" },
    "$where": "this.chr[0]['5'] != this.chr[1]['6']"
})

A bit more tricky for other combinations or named elements but the same principles of JavaScript evaluation apply to that condition.

So you don't need the query form of $elemMatch here due to only testing one field of the array elements, and projection is therefore in the wrong place. What you want a "query" conditions that select the documents that meet them.