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
@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 :
Set up an elementary mapping:
Sync a few docs:
Filter in the query, then aggregate by the
request
field and use sortedtop_hits
:Translating this into the Java DSL shouldn’t be too difficult.