Use distinct and where clause in Elasticsearch Query

Question

I want to get result from Elasticsearch as Sql Query

select distinct(id) from table where E_id in (5,6) and P_id=54

I manged to build block of code to get distinct values using aggregates in elasticsearch as follows

GET /Index/Type/_search?search_type=count
{
  "aggs": {
    "my_fields": {
      "terms": {
        "field": "ID",
        "size": 0
      }
    }
  }
}

and I have another block of code which does where clause job of SQL query

GET /index/type/_search
{
    "query": {
        "bool": {            
            "must": [
               {
                  "terms": {
                    "ID": [ "5","6" ]          
                  }
                },
                {
                  "terms": {
                    "ProjectID": [ "54"]
                  }
                }
            ]
        }
    }
}

How can I integrate these two blocks and get distinct result with where clause in elasticsearch.

Answer 1

You are very close. Just combine both query and aggregation.

{
  "query": {
    "bool": {
      "must": [
        {
          "terms": {
            "ID": [
              "5",
              "6"
            ]
          }
        },
        {
          "terms": {
            "ProjectID": [
              "54"
            ]
          }
        }
      ]
    }
  },
  "aggs": {
    "my_fields": {
      "terms": {
        "field": "ID",
        "size": 0
      },
    "aggs":{
      "top_hits_log"   :{
       "top_hits"   :{
           "size" :1
       }
      }
     }
    }
  }
}

Study about top_hits here