Elasticsearch - Terms Aggregation nested field

Question

I have following problem. I have a nested field ("list") with 2 properties (fieldB & fieldC). This is how a document looks like:

"fieldA: "1",
"list": [
  {"fieldB": "ABC",
  "fieldC": "DEF"},
  {"fieldB": "ABC",
  "fieldC": "GHI"},
  {"fieldB": "UVW",
  "fieldC": "XYZ"},...]
                        },

I want to get a distinct list of all possible fieldC values for "ABC" (fieldB) over all documents. So far I've tried this in Java (Java REST Client):

 SearchRequest searchRequest = new SearchRequest("abc*");
 QueryBuilder matchQueryBuilder = QueryBuilders.boolQuery()
             .must(QueryBuilders.nestedQuery("aList", 
             QueryBuilders.matchQuery("list.fieldB.keyword", "ABC"), ScoreMode.None));
 SearchSourceBuilder sourceBuilder = new SearchSourceBuilder();
 sourceBuilder.query(matchQueryBuilder)
              .aggregation(AggregationBuilders.nested("listAgg","list")
              .subAggregation(AggregationBuilders.terms("fieldBAgg")
              .field("list.fieldB.keyword")));

    searchRequest.source(sourceBuilder);

    SearchResponse searchResponse = null;
    try {
        searchResponse = restHighLevelClient.search(searchRequest, RequestOptions.DEFAULT);
    } catch (IOException e) {
        e.printStackTrace();
    }

    Nested list = searchResponse.getAggregations().get("listAgg");
    Terms fieldBs = list.getAggregations().get("fieldBAgg");

With that query I get all documents which include "ABC" in fieldB and I get all fieldC values. But I just want the fieldC values where fieldB is "ABC".

So in that example I get DEF, GHI and XYZ. But i just want DEF and GHI. Does anybody have an idea how to solve this?

Val · Accepted Answer · 2020-05-11 14:43:38Z

0

The nested constraint in the query part will only select all documents that do have a nested field satisfying the constraint. You also need to add that same constraint in the aggregation part, otherwise you're going to aggregate all nested fields of all the selected documents, which is what you're seeing. Proceed like this instead:

// 1. terms aggregation on the desired nested field
nestedField = AggregationBuilders.terms("fieldBAgg").field("list.fieldC.keyword");

// 2. filter aggregation on the desired nested field value
onlyBQuery = QueryBuilders.termQuery("list.fieldB.keyword", "ABC");
onlyBFilter = AggregationBuilders.filter("onlyFieldB", onlyBQuery).subAggregation(nestedField);

// 3. parent nested aggregation
nested = AggregationBuilders.nested("listAgg", "list").subAggregation(onlyBFilter);

// 4. main query/aggregation
sourceBuilder.query(matchQueryBuilder).aggregation(nested);

edited May 11, 2020 at 14:43

answered May 11, 2020 at 11:50

Val

218k14 gold badges377 silver badges384 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Val Over a year ago

Sorry there was a typo in my code, I was aggregating fieldB instead of fieldC. Please try it again

Collectives™ on Stack Overflow

Elasticsearch - Terms Aggregation nested field

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related