ElasticSearch aggregate documents by field into array

Question

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..

My end goal is to convert this query from native elasic query to java Querybuilder query. — JeyJ
– JeyJ, Commented Jul 30, 2020 at 8:12

Jozef - Spatialized.io · Accepted Answer · 2020-07-30 10:43:17Z

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.

JeyJ · Accepted Answer · 2020-07-30 17:14:54Z

0

@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);

answered Jul 30, 2020 at 17:14

JeyJ

4,1409 gold badges48 silver badges108 bronze badges

Collectives™ on Stack Overflow

ElasticSearch aggregate documents by field into array

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related