MongoDB中的多个$ where查询

问题描述

我尝试将以下查询放在一起，但不起作用:

I tried to put following query together, but it is not working:

db.sss.find({
    "pos": { "$gte": 200000, "$lt": 2000000 },
    "$where": "(this.chr.letter != "X" && this.chr.no == 5) && (this.chr.letter != "X" && this.chr.no == 6) && (this.chr.letter != this.chr.letter)"
})

上面的条件我试图在下面解释:

The above condition above I tried to explain below:

• chr.no = 5和chr.no = 6
chr中两个对象/字典之间的
• chr.letter不相同，并且没有 X ，并且
• 文档必须在200000-2000000范围内

• chr.no = 5 and chr.no = 6
• chr.letter between two objects/dicts in chr are not the same and no X, and
• the document must be in range 200000 - 2000000

示例输出如下:

• {"x_type":"7"，"sub_name":"B01"，"name":"A"，"pos":828288，"s_type":1}
• {"x_type":"9"，"sub_name":"B01"，"name":"A"，"pos":871963，"s_type":3}

文档"x_type":"8"不满足范围条件.文档"x_type":"10"无效，因为chr.no = 6的chr.letter =X.并且文档"x_type":"14"无效，因为chr.no = 5和chr.no = 6都具有相同的chr.letter = G

Document "x_type":"8" meet not the range condition. Document "x_type":"10" is not valid, because chr.no = 6 has chr.letter = X.And document "x_type":"14" is not valid, because chr.no = 5 and chr.no = 6 both have the same chr.letter = G

数据库包含以下文档:

{
  "_id":ObjectId("5441b57bb6d08aa98ee8d34f"),
  "name":"A",
  "pos":828288,
  "s_type":1,
  "sub_name":"B01",
  "type":"Test",
  "x_type":7,
  "chr":[
      {
        "letter":"C",
        "no":4
      },
      {
        "letter":"C",
        "no":5
      },
      {
        "letter":"T",
        "no":6
      }
  ]
}{
  "_id":ObjectId("5441b57cb6d08aa98ee8d350"),
  "name":"A",
  "pos":171878,
  "s_type":3,
  "sub_name":"B01",
  "type":"Test",
  "x_type":8,
  "chr":[
      {
        "letter":"C",
        "no":5
      },
      {
        "letter":"T",
        "no":6
      }
  ]
}{
  "_id":ObjectId("5441b57cb6d08aa98ee8d351"),
  "name":"A",
  "pos":871963,
  "s_type":3,
  "sub_name":"B01",
  "type":"Test",
  "x_type":9,
  "chr":[
      {
        "letter":"A",
        "no":5
      },
      {
        "letter":"G",
        "no":6
      }
  ]
}{
  "_id":ObjectId("5441b57cb6d08aa98ee8d352"),
  "name":"A",
  "pos":1932523,
  "s_type":1,
  "sub_name":"B01",
  "type":"Test",
  "x_type":10,
  "chr":[
      {
        "letter":"T",
        "no":4
      },
      {
        "letter":"A",
        "no":5
      },
      {
        "letter":"X",
        "no":6
      }
  ]
}{
  "_id":ObjectId("5441b57cb6d08aa98ee8d353"),
  "name":"A",
  "pos":667214,
  "s_type":1,
  "sub_name":"B01",
  "type":"Test",
  "x_type":14,
  "chr":[
      {
        "letter":"T",
        "no":4
      },
      {
        "letter":"G",
        "no":5
      },
      {
        "letter":"G",
        "no":6
      }
  ]
}

我使用以下脚本创建了上述数据库:

I created the above database with the below script:

from pymongo import MongoClient
from collections import defaultdict


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

r = [["Test", "A", "B01", 828288,  1,    7, 'C', 4],
    ["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":{
                  "letter":i[6],
                  "no": i[7]} }},
          True)

如何解决以上查询?

推荐答案

我想您在JavaScript和MongoDB之间混合了语法.
$ where 由服务器端的JavaScript引擎执行，因此您的 $ where 语句必须符合JavaScipt的语法.例如，this.chr.no == 5实际上是您所期望的this.chr[index].no == 5；并且变量不可能同时等于两个不同的值.以下代码供您参考:

I guess somewhat you mixed the syntax between JavaScript and MongoDB.
$where executed by JavaScript engine on server side, so your $where statementsmust conform the syntax of JavaScipt. For example, this.chr.no == 5 is actually this.chr[index].no == 5 as you expected; and a variable is impossibly equal to two different values at the same time. The following codes for your reference:

var judge = function () {
    var unexpect = "X";
    var letter1 = unexpect, letter2 = unexpect;
    for (var i in this.chr) {
        var chr = this.chr[i];
        if (chr.no == 5) {
            letter1 = chr.letter;
        } else if (chr.no == 6) {
            letter2 = chr.letter;
        }
    }
    if (letter1 != letter2 && letter1 != unexpect && letter2 != unexpect) {
        return true;
    }
    return false;
};

db.sss.find({
    "pos": { "$gte": 200000, "$lt": 2000000 },
    "$and" : [{"chr.no" : 5}, {"chr.no" : 6}],    // narrow range further
    "$where": judge
}, {"x_type":1, "sub_name":1, "name":1, "pos":1, "s_type":1, _id:0});

这篇关于MongoDB中的多个$ where查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！