skip to Main Content

I’m trying to write the following logic as a query in java to elasticsearch :

ES contains following documents :

{"request" : 1, "store":"ebay", "status" : "retrieved" , "lastdate": "2012/12/20 17:00", "retrieved_by" : "John"}
{"request" : 1, "store":"ebay", "status" : "stored" , "lastdate": "2012/12/20 18:00", "stored_by" : "Alex"}
{"request" : 1, "store":"ebay", "status" : "bought" , "lastdate": "2012/12/20 19:00", "bought_by" : "Arik"}
{"request" : 2, "store":"aliexpress", "status" : "retrieved" , "lastdate": "2012/12/20 17:00"}
{"request" : 2, "store":"aliexpress","status" : "stored" , "lastdate": "2012/12/20 18:00"}
{"request" : 2, "store":"aliexpress","status" : "bought" , "lastdate": "2012/12/20 19:00"}

I’m trying to write a query that will get as an input the store name and return the requests of that store aggregated into an array by their request_id.

In other words I’m trying :

1.Filter by term on specific field("store").

2.Aggregate the results based on specific field("request") to an array

For example for input "ebay" :

{
"1" : [
{"request" : 1, "store":"ebay", "status" : "retrieved" , "lastdate": "2012/12/20 17:00", "retrieved_by" : "John"}
{"request" : 1, "store":"ebay", "status" : "stored" , "lastdate": "2012/12/20 18:00", "stored_by" : "Alex"}
{"request" : 1, "store":"ebay", "status" : "bought" , "lastdate": "2012/12/20 19:00", "bought_by" : "Arik"}
],

".." : [...]

}

It isnt so important the the key in the result will be the request (I will buy any key). The important part is that I aggregate all the records by the request field into an array and sorted them in the array by lastdate.

My end goal is to create this query with java QueryBuilder. Therefore, I’m first trying to use elastic native query language in order to understand what QueryBuilder to use..

2

Answers


  1. Chosen as BEST ANSWER

    @joe posted the right answer in ES DSL(Thanks again !).

    My goal was to use the query in java. In case someone will also need the JAVA DSL code I'm adding it here :

    QueryBuilder storeQuery = QueryBuilders.boolQuery().filter(QueryBuilders.termsQuery("store", "ebay"))
    AggregationBuilder subAgg= AggregationBuilders.topHits("hits").sort("lastdate, SortOrder.ASC);
     AggregationBuilder mainAgg= AggregationBuilders.terms("by_req").field("request").subAggregation(subAgg);
    

  2. Set up an elementary mapping:

    PUT stores
    {
      "mappings": {
        "properties": {
          "lastdate": {
            "type": "date",
            "format": "yyyy/MM/dd HH:mm"
          }
        }
      }
    }
    

    Sync a few docs:

    POST _bulk
    {"index":{"_index":"stores","_type":"_doc"}}
    {"request":1,"store":"ebay","status":"retrieved","lastdate":"2012/12/20 17:00","retrieved_by":"John"}
    {"index":{"_index":"stores","_type":"_doc"}}
    {"request":1,"store":"ebay","status":"stored","lastdate":"2012/12/20 18:00","stored_by":"Alex"}
    {"index":{"_index":"stores","_type":"_doc"}}
    {"request":1,"store":"ebay","status":"bought","lastdate":"2012/12/20 19:00","bought_by":"Arik"}
    {"index":{"_index":"stores","_type":"_doc"}}
    {"request":2,"store":"aliexpress","status":"retrieved","lastdate":"2012/12/20 17:00"}
    {"index":{"_index":"stores","_type":"_doc"}}
    {"request":2,"store":"aliexpress","status":"stored","lastdate":"2012/12/20 18:00"}
    {"index":{"_index":"stores","_type":"_doc"}}
    {"request":2,"store":"aliexpress","status":"bought","lastdate":"2012/12/20 19:00"}
    

    Filter in the query, then aggregate by the request field and use sorted top_hits:

    GET stores/_search
    {
      "size": 0, 
      "query": {
        "term": {
          "store": {
            "value": "ebay"
          }
        }
      },
      "aggs": {
        "by_req": {
          "terms": {
            "field": "request"
          },
          "aggs": {
            "hits": {
              "top_hits": {
                "sort": [
                  {
                    "lastdate": {
                      "order": "desc"
                    }
                  }
                ]
              }
            }
          }
        }
      }
    }
    

    Translating this into the Java DSL shouldn’t be too difficult.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search