CODEWITHSUNDEEP
sqlelasticsearch
user1028697
user1028697

Reputation: 135

Elasticsearchj implementation of Exists and Not Exists

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)

Equivalent query in elasticsearch ?

Upvotes: 1

Views: 93

Answers (1)

headspiderthingy
headspiderthingy

Reputation: 68

The Filter Aggregation may be the answer.

Something like this:

{
  "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"
          }
        }
      }
    }
  }
}

Upvotes: 2

Related Questions