合并两个$ or语句 [英] Combine two $or statements

问题描述

我正在尝试执行由两个$or组成的查询:

I am trying to perform a query which is composed of two $or's:

|--------------------
| Date1  |  Date2   |
|--------------------
| NULL   |  NULL    | *
| NULL   |  TODAY   | *
| NULL   |  TOMRW   | 
| TODAY  |  TODAY   | *
| TODAY  |  NULL    | *
| TOMRW  |  NULL    | 
|--------------------

(我已经标记了与星号匹配的行)

(I've marked the rows that would match with an asterisk)

(Date1 == null || Date1 <= today) && (Date2 == null || Date2 <= today)

我不确定如何在MongoDB中表达此查询.

I am not sure how to express this query in MongoDB.

它可以分为两个单独的查询，它们分别执行应做的事情:

It can be broken down into two individual queries that do exactly what they should:

{
    "$or": [{
        "Date1": {
            "$exists": false
        }
    },
    {
        "Date1": {
            "$exists": true,
            "$lte": new Date("2012-01-07T04:45:52.057Z")
        }
    }]
}

和

{
    "$or": [{
        "Date2": {
            "$exists": false
        }
    },
    {
        "Date2": {
            "$exists": true,
            "$lte": new Date("2012-01-07T04:45:52.057Z")
        }
    }]
}

这两个都选择正确的文档集-我只是不知道如何将它们作为单个查询执行.

Both of these select the correct set of documents - I just dont know how to execute them as a single query.

我最初的想法是进行这样的查询:

My initial thought was to do a query like this:

{
    $and: [orQuery1, orQuery2]
}

使用$and查询将返回0个结果.解释了为什么在此线程中进行操作: $并且查询不返回结果

Using an $and query returns 0 results. It was explained why here in this thread: $and query returns no result

也在该线程中，有人建议执行这样的查询:

Also in that thread, a suggestion was made to do a query like this:

{
    Key: {valToMatch1: 1, valToMatch2: 2}
}

但是我认为$or不能以这种方式执行.

But I dont think an $or can be executed this way.

因此，问题是:如何构造查询，以便可以将两个$ or组合成一个查询?

So, the question is: How do I construct my query such that I can combine the two $or's into a single query?

(时间已经很晚了，所以我希望这个问题有意义.)

(Its getting very late so I hope this question makes sense.)

推荐答案

use test
db.test.insert({a:1})
db.test.insert({a:2, Date2:new Date("01/07/2012")})
db.test.insert({a:3, Date2:new Date("01/08/2012")})
db.test.insert({a:4, Date1:new Date("01/07/2012"), Date2:new Date("01/07/2012")})
db.test.insert({a:5, Date1:new Date("01/07/2012")})
db.test.insert({a:6, Date1:new Date("01/08/2012")})

第一个子查询 db.test.distinct('a'，{...});

first subquery db.test.distinct('a', {...});

[1、2、3]

[1, 2, 3]

第二个子查询 db.test.distinct('a'，{...});

second subquery db.test.distinct('a', {...});

[1，5，6]

[ 1, 5, 6 ]

(Date1 == null || Date1 <= today) && (Date2 == null || Date2 <= today)

放松

Date1 == null && Date2 == null ||
Date1 == null && Date2 <= today ||
Date1 <= today && Date2 == null ||
Date1 <= today && Date2 <= today ||

查询

db.test.find(
{
  $or : 
  [
    {$and: [
        {"Date1": {"$exists": false}},
        {"Date2": {"$exists": false}}
      ]},
    {$and: [
        {"Date1": {"$exists": false}},
        {"Date2": {
            "$exists": true,
            "$lte": new Date("2012-01-07T04:45:52.057Z")}
        }
      ]},
    {$and: [
        {"Date2": {"$exists": false}},
        {"Date1": {
            "$exists": true,
            "$lte": new Date("2012-01-07T04:45:52.057Z")}
        }
      ]},
    {$and: [
        {"Date2": {
            "$exists": true,
            "$lte": new Date("2012-01-07T04:45:52.057Z")}
        },
        {"Date1": {
            "$exists": true,
            "$lte": new Date("2012-01-07T04:45:52.057Z")}
        }
      ]}
  ]
})
>[ 1 ]

这也应该起作用(假设不存在"和空"相同)

this should work too (assume that 'not exist' and 'null' is the same)

db.test.find(
{
  $and : 
  [
    {$or: [
        {"Date1": null},
        {"Date1": { "$lte": new Date("2012-01-07T04:45:52.057Z")} }
      ]},
    {$or: [
        {"Date2": null},
        {"Date2": { "$lte": new Date("2012-01-07T04:45:52.057Z")} }
      ]}
  ]
}
)

这篇关于合并两个$ or语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！