sql - Elasticsearchj存在和不存在的实现

Question

Select COUNT(distinct name) 
From index1 
Where date between X and y 
And name in (Select name 
             From index1  
             Where date between p and s) 

Elasticsearch中的等效查询？

Answer 1

The Filter Aggregation may be the answer.

像这样:

{
  "size" : 0,
  "query" : {
    "filtered" : {
      "query" : {
        "match_all" : { }
      },
      "filter" : {
        "range" : {
          "date" : {
            "from" : "2015-03-10T21:51:47.703-04:00",
            "to" : "2015-03-20T21:51:47.727-04:00",
            "include_lower" : true,
            "include_upper" : true
          }
        }
      }
    }
  },
  "aggregations" : {
    "names1" : {
      "filter" : {
        "range" : {
          "date" : {
            "from" : "2015-02-28T21:51:47.733-05:00",
            "to" : "2015-03-20T21:51:47.734-04:00",
            "include_lower" : true,
            "include_upper" : true
          }
        }
      },
      "aggregations" : {
        "names2" : {
          "terms" : {
            "field" : "name"
          }
        }
      }
    }
  }
}