如何在MongoDB查询中将$ elemMatch与$ nin或$ not结合? [英] How to combine $elemMatch with $nin or $not in a MongoDB query?

问题描述

我想找到在fieldC对象中数组fieldB没有值(val1和val2)的所有结果.

I want to find all results where the array fieldB does not have values (val1 and val2) in fieldC objects.

我尝试过:

db.SalesToImport
  .find(
    {
      fieldA: {$exists:true},
      ,fieldB: {$not: {$elemMatch: {fieldC: 'val1'}}}
      ,fieldB: {$not: {$elemMatch: {fieldC: 'val2'}}}
    }
  );

我已经尝试过:

db.SalesToImport
  .find(
    {
      fieldA: {$exists:true},
      ,fieldB: {$not: {$elemMatch: {fieldC: 'val1', fieldC: 'val2'}}}
    }
  );

它们都产生相同的结果，并且在删除某些文档时，仍然存在文档中包含val1和val2的文档，这告诉我我进行了错误的查询.

They both yield the same results and while they remove some documents, there are still docs with val1 and val2 in them, which tells me that I made an incorrect query.

文档的结构包含如下字段:

A document's structure contains fields like so:

{
  fieldA: value,
  fieldB: [
    {fieldC:value1, fieldD:value3, fieldE:value5},
    {fieldC:value2, fieldD:value4, fieldE:value6}
  ]
}

我应该如何在MongoDB查询中将$ elemMatch与$ nin或$ not结合起来以达到预期的效果?

How should I combine $elemMatch with $nin or $not in a MongoDB query to get the desired effect?

根据建议，我运行了以下现实但经过消毒的查询:

Based on the suggestions I ran the following realistic but sanitized query:

db.SalesToImport
  .find(
    {
      calculatedTaxRate: {$exists:true}, unidentifiedProducts: {$exists:false}, unidentifiedCustomer: {$exists:false},
      register_sale_payments: {
        $elemMatch: {
          retailer_payment_type_id : {
            $nin: [
              '0af7b240-ab24-11e7-eddc-9fff6de134e9'
              ,'0af7b240-ab24-11e7-eddc-9fff69293826'
            ]
          }, 
        }
      }
    },
    {_id:0,status:0,customer_id:0,register_id:0}
  )
  .sort({"calculatedTaxRate":1});

但是响应在fieldB.fieldC或register_sale_payments.retailer_payment_type_id中仍然有不想要的结果，所以这不是我想要的，或者我在使用它时出错了.

But the response still had the unwanted results in fieldB.fieldC or register_sale_payments.retailer_payment_type_id, so this wasn't what I was looking for or I made a mistake in using it.

根据建议，我运行了以下现实但经过消毒的查询:

Based on the suggestions I ran the following realistic but sanitized query:

db.SalesToImport
  .find(
    {
      calculatedTaxRate: {$exists:true}, unidentifiedProducts: {$exists:false}, unidentifiedCustomer: {$exists:false},
      'register_sale_payments.retailer_payment_type_id': {
        $nin: [
          '0af7b240-ab24-11e7-eddc-9fff6de134e9'
          ,'0af7b240-ab24-11e7-eddc-9fff69293826'
        ]
      }
    },
    {_id:0,status:0,customer_id:0,register_id:0}
  )
  .sort({"calculatedTaxRate":1});

成功了！

推荐答案

虽然您只对fieldB的一个字段感兴趣，但是您无需使用$elemmatch.

While you are interested just one field of fieldB, you don't need to use $elemmatch.

db.SalesToImport.find(
{
  "fieldA": {$exists:true},
  "fieldB.fieldC": { $nin: ["val1", "va2"] }
}
);

例如，如果您在fieldB中有一个fieldD，并且您不希望fieldD也具有值val3，val4:

If you have a fieldD in fieldB and for example, you don't want fieldD have values val3, val4 too :

db.SalesToImport.find(
{
  "fieldA": { $exists: true },
  "fieldB.fieldC": { $nin: ["val1", "va2"] },
  "fieldB.fieldD": { $nin: ["val3", "va4"] }
}
);

上面的查询也排除了fieldC和fieldD对的文档，例如:(val5，val3)，因为我们分别查询fieldC和fieldD.

Above query exclude documents for fieldC, fieldD pairs like that too: (val5, val3), because we are querying fieldC and fieldD seperately.

如果要查询fieldB，则没有fieldC和fieldD的值对(val1，val3)，(val1，val4)，(val2，val3)，(val2，val4):

If you want to query for fieldBnot have value pairs for fieldC and fieldD (val1, val3), (val1, val4), (val2, val3), (val2, val4) :

db.SalesToImport.find(
{
  "fieldA": { $exists: true },
  "fieldB": { $elemmatch: {
    "fieldC" : { $nin: ["val1", "val2"] }, 
    "fieldD": { $nin: ["val3", "val4"] } } 
   }
}
);

以上查询不排除filedC和fieldD对，例如:(val5，val3)

Above query does not exclude filedC, fieldD pairs like: (val5, val3)

这篇关于如何在MongoDB查询中将$ elemMatch与$ nin或$ not结合?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！