# “distinct” query in elasticsearch java api

**URL:** <https://discuss.elastic.co/t/distinct-query-in-elasticsearch-java-api/319349>\
**Category:** Elasticsearch\
**Tags:** language-clients\
**Created:** [November 19, 2022, 9:50pm UTC](https://discuss.elastic.co/t/distinct-query-in-elasticsearch-java-api/319349 "2022-11-19T21:50:20Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![hmkhitaryan](https://avatars.discourse-cdn.com/v4/letter/h/cdc98d/32.png) [@hmkhitaryan](https://discuss.elastic.co/u/hmkhitaryan)\
**Post date:** [November 19, 2022, 9:50pm UTC](https://discuss.elastic.co/t/distinct-query-in-elasticsearch-java-api/319349/1 "2022-11-19T21:50:20Z")

</div>

How to create “distinct” query in elasticsearch java api, like we do in sql. here is my query, which kibana generated, so I should do the same with java, and I'm trying many ways, but again getting with duplicates.

```auto
"aggs": {
    "3": {
      "terms": {
        "field": "policy.weight",
        "order": {
          "1": "asc"
        },
        "size": 5
      },
      "aggs": {
        "1": {
          "cardinality": {
            "field": "item.item_doc_id.keyword"
          }
        },
        "2": {
          "terms": {
            "field": "policy.name.keyword",
            "order": {
              "1": "desc"
            },
            "size": 100
          },
          "aggs": {
            "1": {
              "cardinality": {
                "field": "item.item_doc_id.keyword"
              }
            },
            "4": {
              "terms": {
                "field": "policy.description.keyword",
                "order": {
                  "1": "desc"
                },
                "size": 100
              },
              "aggs": {
                "1": {
                  "cardinality": {
                    "field": "item.item_doc_id.keyword"
                  }
                }
              }
            }
          }
        }
      }
    }
  }

```

and this is the code in java, what I'm trying to do, but get with duplicates.

```auto
final SearchRequest searchRequest = new SearchRequest(bucketListInfo.getIndexName());
        final String field1 = "policy.name.keyword";
        final String field2 = "policy.description.keyword";
// final String field3 = "policy.weight";
        final String field3 = "item.item_doc_id.keyword";
        final SearchSourceBuilder searchSourceBuilder = new SearchSourceBuilder()
                .query(getRangeQueryBuilderWithOptionalFilter(bucketListInfo.getTimestampFieldName(), bucketListInfo.getFilterFieldName(), bucketListInfo.getFilterFieldValue(),
                        bucketListInfo.getFrom(), bucketListInfo.getTo())).size(100);

        TermsAggregationBuilder termsAggregationBuilder;
        AggregationBuilder cardinalityAggregationBuilder = AggregationBuilders.cardinality(field3).field(field3);
        termsAggregationBuilder = AggregationBuilders.terms(field1).field(field1).size(100);
        searchSourceBuilder.aggregation(termsAggregationBuilder);

        termsAggregationBuilder = AggregationBuilders.terms(field2).field(field2).size(100);
        searchSourceBuilder.aggregation(termsAggregationBuilder);

        searchSourceBuilder.aggregation(cardinalityAggregationBuilder);
        searchRequest.source(searchSourceBuilder);
        try {
            final SearchResponse response = restHighLevelClient.search(searchRequest, RequestOptions.DEFAULT);
            BucketList bucketList = new BucketList();
            final Terms terms1 = response.getAggregations().get(field1);
            final Terms terms2 = response.getAggregations().get(field2);
            Cardinality terms3 = response.getAggregations().get(field3);
            bucketList.getBuckets().add(terms1.getBuckets());
            bucketList.getBuckets().add(terms2.getBuckets());
// bucketList.getBuckets().add(terms3.getBuckets());
            return bucketList;
        } catch (Exception e) {
            log.error(e.getMessage(), e);
            return null;
        }

```

So tell me please, what is the correct way of writing the java query of that kibana generated query.

---

<div class="post-metadata">

**Author:** ![RabBit\_BR](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rabbit_br/32/82261_2.png) [@RabBit\_BR](https://discuss.elastic.co/u/RabBit_BR)\
**Post date:** [November 20, 2022, 1:05am UTC](https://discuss.elastic.co/t/distinct-query-in-elasticsearch-java-api/319349/2 "2022-11-20T01:05:55Z")

</div>

Hi @hmkhitaryan

This a code adapt your query.

```auto
 SearchRequest searchRequest = new SearchRequest("idx_name");

    TermsAggregationBuilder aggsTermPolicyWeight = AggregationBuilders.terms("3")
        .field("policy.weight").size(5);

    CardinalityAggregationBuilder aggsCardinalityDocId = AggregationBuilders.cardinality("1").field("item.item_doc_id.keyword");

    aggsTermPolicyWeight.subAggregation(aggsCardinalityDocId);

    TermsAggregationBuilder aggsTermPolicyName = AggregationBuilders.terms("2")
        .field("policy.name.keyword").size(100);

    TermsAggregationBuilder aggsTermPolicyDescription = AggregationBuilders.terms("4")
        .field("policy.description.keyword").size(100);

    aggsTermPolicyDescription.subAggregation(aggsCardinalityDocId);

    aggsTermPolicyName.subAggregation(aggsCardinalityDocId);
    aggsTermPolicyName.subAggregation(aggsTermPolicyDescription);

    aggsTermPolicyWeight.subAggregation(aggsTermPolicyName);

    SearchSourceBuilder searchSourceBuilder = new SearchSourceBuilder();
    searchSourceBuilder.aggregation(aggsTermPolicyWeight);

    searchRequest.source(searchSourceBuilder);

    SearchResponse searchResponse = getClient().search(searchRequest, RequestOptions.DEFAULT);

```

---

<div class="post-metadata">

**Author:** ![hmkhitaryan](https://avatars.discourse-cdn.com/v4/letter/h/cdc98d/32.png) [@hmkhitaryan](https://discuss.elastic.co/u/hmkhitaryan)\
**Post date:** [November 20, 2022, 7:02am UTC](https://discuss.elastic.co/t/distinct-query-in-elasticsearch-java-api/319349/3 "2022-11-20T07:02:21Z")

</div>

Hi @RabBit_BR I'm trying this, but it again returns hits with duplicates.  
Actually the final response should be like this:

```auto
   [
     {
       "policyName" : "policy name from db", 
       "policyDescription" : "policy desc from db",
       "count" : the count from db
     },
     ....
   ]

```

---

<div class="post-metadata">

**Author:** ![RabBit\_BR](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rabbit_br/32/82261_2.png) [@RabBit\_BR](https://discuss.elastic.co/u/RabBit_BR)\
**Post date:** [November 20, 2022, 11:00am UTC](https://discuss.elastic.co/t/distinct-query-in-elasticsearch-java-api/319349/4 "2022-11-20T11:00:20Z")

</div>

the problem must be in the query. The coding is exactly the query that you presented.

---

<div class="post-metadata">

**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\
**Post date:** [November 20, 2022, 11:11am UTC](https://discuss.elastic.co/t/distinct-query-in-elasticsearch-java-api/319349/5 "2022-11-20T11:11:05Z")

</div>

What do you mean by duplicates? Be aware that cardinality aggregations are spproximations and not necessarily exact.

---

<div class="post-metadata">

**Author:** ![hmkhitaryan](https://avatars.discourse-cdn.com/v4/letter/h/cdc98d/32.png) [@hmkhitaryan](https://discuss.elastic.co/u/hmkhitaryan)\
**Post date:** [November 20, 2022, 11:25am UTC](https://discuss.elastic.co/t/distinct-query-in-elasticsearch-java-api/319349/6 "2022-11-20T11:25:23Z")

</div>

@Christian_Dahlqvist comming back to your question. this whole query retrieves rows, where doc\_id is not uniqe, so I want it to be uniq, like dinstinct, to get the ones, where this doc\_id field doesn't repeat.

---

<div class="post-metadata">

**Author:** ![hmkhitaryan](https://avatars.discourse-cdn.com/v4/letter/h/cdc98d/32.png) [@hmkhitaryan](https://discuss.elastic.co/u/hmkhitaryan)\
**Post date:** [November 20, 2022, 11:31am UTC](https://discuss.elastic.co/t/distinct-query-in-elasticsearch-java-api/319349/7 "2022-11-20T11:31:32Z")

</div>

Actually these two methods does the job, what I need, the only lack here is that I get the rows, where doc\_id field values repeat, so this is the problem actually now, how to do so that this field values be distinct.

```auto
    public BucketList getListOfBucketsTimeRestricted(BucketListInfo bucketListInfo) {
        final SearchRequest searchRequest = new SearchRequest(bucketListInfo.getIndexName());

        final SearchSourceBuilder searchSourceBuilder = new SearchSourceBuilder()
                .query(getRangeQueryBuilderWithOptionalFilter(bucketListInfo.getTimestampFieldName(), bucketListInfo.getFilterFieldName(), bucketListInfo.getFilterFieldValue(),
                        bucketListInfo.getFrom(), bucketListInfo.getTo())).size(100);

        for (String aggrField : bucketListInfo.getAggrFieldList()) {
            final TermsAggregationBuilder aggregationBuilder = AggregationBuilders.terms(aggrField).field(aggrField);
            searchSourceBuilder.aggregation(aggregationBuilder);
        }
        searchRequest.source(searchSourceBuilder);
        try {
            final SearchResponse response = restHighLevelClient.search(searchRequest, RequestOptions.DEFAULT);
            BucketList bucketList = new BucketList();
            for (String aggrField : bucketListInfo.getAggrFieldList()) {
                final Terms terms = response.getAggregations().get(aggrField);
                bucketList.getBuckets().add(terms.getBuckets());
            }
            return bucketList;
        } catch (Exception e) {
            log.error(e.getMessage(), e);
            return null;
        }
    }

```

```auto
    private static final List<String> AGGR_FIELDS = List.of("policy.name.keyword", "policy.description.keyword");
    private static final String IS_COMPLIANT_FIELD = "is_compliant";

    private final SearchClient searchClient;

    public List<PolicyViolation> getEvaluationTimeRestricted(DateTime from, DateTime to) {
        final BucketListInfo bucketListInfo = BucketListInfo.builder()
                .indexName(EVALUATION.getIndexName())
                .timestampFieldName(EVALUATION.getTimestampFieldName())
                .aggrFieldList(AGGR_FIELDS)
                .filterFieldName(IS_COMPLIANT_FIELD)
                .filterFieldValue(false)
                .from(from)
                .to(to)
                .build();

        BucketList buckets = searchClient.getListOfBucketsTimeRestricted(bucketListInfo);
        if (Objects.isNull(buckets)) {
            return Collections.emptyList();
        }

        List<? extends Terms.Bucket> firstBuckets = buckets.getBuckets().get(0);
        List<? extends Terms.Bucket> secondBuckets = buckets.getBuckets().get(1);
        final List<PolicyViolation> evaluationCounts = new ArrayList<>();

        for (int i = 0; i < firstBuckets.size(); i++) {
            evaluationCounts.add(
                    new PolicyViolation(firstBuckets.get(i).getKeyAsString(), secondBuckets.get(i).getKeyAsString(), firstBuckets.get(i).getDocCount()));
        }
        return evaluationCounts;
    }

```

type or paste code here

```auto

```

---

<div class="post-metadata">

**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\
**Post date:** [November 20, 2022, 11:42am UTC](https://discuss.elastic.co/t/distinct-query-in-elasticsearch-java-api/319349/8 "2022-11-20T11:42:27Z")

</div>

Tbe documents retrieved if you have not set size to 0 are matching the filter selection but not affected by aggregations, so will not be unique.

---

<div class="post-metadata">

**Author:** ![hmkhitaryan](https://avatars.discourse-cdn.com/v4/letter/h/cdc98d/32.png) [@hmkhitaryan](https://discuss.elastic.co/u/hmkhitaryan)\
**Post date:** [November 20, 2022, 11:48am UTC](https://discuss.elastic.co/t/distinct-query-in-elasticsearch-java-api/319349/9 "2022-11-20T11:48:12Z")

</div>

@Christian_Dahlqvist will you please show me where to set that size?

---

<div class="post-metadata">

**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\
**Post date:** [November 20, 2022, 4:54pm UTC](https://discuss.elastic.co/t/distinct-query-in-elasticsearch-java-api/319349/10 "2022-11-20T16:54:42Z")

</div>

Please have a look at [the docs](https://www.elastic.co/guide/en/elasticsearch/reference/8.5/search-aggregations.html#return-only-agg-results).

---

<div class="post-metadata">

**Author:** ![hmkhitaryan](https://avatars.discourse-cdn.com/v4/letter/h/cdc98d/32.png) [@hmkhitaryan](https://discuss.elastic.co/u/hmkhitaryan)\
**Post date:** [November 20, 2022, 6:16pm UTC](https://discuss.elastic.co/t/distinct-query-in-elasticsearch-java-api/319349/11 "2022-11-20T18:16:18Z")

</div>

@Christian_Dahlqvist sorry, but I think we don't understand each other.  
I want to do a "select distinct" query analog in elasticsearch java api, and then group by the results, got from that select distinct query. just that.

---

<div class="post-metadata">

**Author:** ![hmkhitaryan](https://avatars.discourse-cdn.com/v4/letter/h/cdc98d/32.png) [@hmkhitaryan](https://discuss.elastic.co/u/hmkhitaryan)\
**Post date:** [November 20, 2022, 6:36pm UTC](https://discuss.elastic.co/t/distinct-query-in-elasticsearch-java-api/319349/12 "2022-11-20T18:36:36Z")

</div>

like this:  
select distinct doc\_id, ..., ... from ... where ..., group by ....

---

<div class="post-metadata">

**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\
**Post date:** [November 20, 2022, 6:37pm UTC](https://discuss.elastic.co/t/distinct-query-in-elasticsearch-java-api/319349/13 "2022-11-20T18:37:23Z")

</div>

As far as I know I do not think that is possible in Elasticsearch.

---

<div class="post-metadata">

**Author:** ![hmkhitaryan](https://avatars.discourse-cdn.com/v4/letter/h/cdc98d/32.png) [@hmkhitaryan](https://discuss.elastic.co/u/hmkhitaryan)\
**Post date:** [November 20, 2022, 7:14pm UTC](https://discuss.elastic.co/t/distinct-query-in-elasticsearch-java-api/319349/14 "2022-11-20T19:14:47Z")

</div>

no way ? 😔

---

<div class="post-metadata">

**Author:** ![stephenb](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/stephenb/32/40856_2.png) [@stephenb](https://discuss.elastic.co/u/stephenb)\
**Post date:** [November 20, 2022, 7:29pm UTC](https://discuss.elastic.co/t/distinct-query-in-elasticsearch-java-api/319349/15 "2022-11-20T19:29:00Z")

</div>

Here is a thought...

Use the SQL API interface

> **[SQL APIs | Elasticsearch Guide \[8.5\] | Elastic](https://www.elastic.co/guide/en/elasticsearch/reference/current/sql-apis.html)**

> **[SQL search API | Elasticsearch Guide \[8.5\] | Elastic](https://www.elastic.co/guide/en/elasticsearch/reference/current/sql-search-api.html)**

Then use the translate API to get the DSL

> **[SQL translate API | Elasticsearch Guide \[8.5\] | Elastic](https://www.elastic.co/guide/en/elasticsearch/reference/current/sql-translate-api.html)**

Perhaps that will help, one word of caution. The SQL API is very picky about single quotes and double quotes etc.

---

<div class="post-metadata">

**Author:** ![hmkhitaryan](https://avatars.discourse-cdn.com/v4/letter/h/cdc98d/32.png) [@hmkhitaryan](https://discuss.elastic.co/u/hmkhitaryan)\
**Post date:** [November 21, 2022, 10:11am UTC](https://discuss.elastic.co/t/distinct-query-in-elasticsearch-java-api/319349/16 "2022-11-21T10:11:09Z")

</div>

@stephenb I started using the sql search api, you suggested, but the problem is now that my index name is "\*-evaluation", and when I'm using the query like this :

```auto
        String query = "{\"query\":\"SELECT 'name' FROM *-evaluation\"" + ","
                + "\"filter\": {"
                   + " \"range\": {"
                      + " \"@cspm.ingested_at\": {"
                         + " \"from\": \"15/11/2022\","
                         + " \"to\": \"16/11/2022\","
                         + " \"format\": \"dd/MM/yyyy\""
                     + " }"
                + " }"
                + "}}";

```

so I get a very unclear json object graph, which I think is not related to the data, which I am expecting. I even excape the index name in string, like '\*-evaluation', but anyway, again no exected results. So any idea how to resolve this?

---

<div class="post-metadata">

**Author:** ![bhuvahh](https://avatars.discourse-cdn.com/v4/letter/b/dec6dc/32.png) [@bhuvahh](https://discuss.elastic.co/u/bhuvahh)\
**Post date:** [November 21, 2022, 1:21pm UTC](https://discuss.elastic.co/t/distinct-query-in-elasticsearch-java-api/319349/17 "2022-11-21T13:21:20Z")

</div>

I want to do a "select distinct" query analog in elasticsearch java api, and then group by the results, got from that select distinct query. just that.

---

<div class="post-metadata">

**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\
**Post date:** [November 21, 2022, 1:36pm UTC](https://discuss.elastic.co/t/distinct-query-in-elasticsearch-java-api/319349/18 "2022-11-21T13:36:48Z")

</div>

I do not think there is any query construct that based on a filter returns all unique values (terms) from a field. The closest I think you can get is to perform a terms aggregation, but that will give you a count together with each term. There is also a limit to the size of the result set, so you may not get all if there are many values.

---

<div class="post-metadata">

**Author:** ![linkerc](https://avatars.discourse-cdn.com/v4/letter/l/13edae/32.png) [@linkerc](https://discuss.elastic.co/u/linkerc)\
**Post date:** [November 29, 2022, 11:19pm UTC](https://discuss.elastic.co/t/distinct-query-in-elasticsearch-java-api/319349/19 "2022-11-29T23:19:05Z")

</div>

You can do nested aggregation, but I'm not sure if that's what you are looking for.  
You can group by unique doc\_id and within each unique doc\_id, you can further group by other fields.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [December 27, 2022, 11:19pm UTC](https://discuss.elastic.co/t/distinct-query-in-elasticsearch-java-api/319349/20 "2022-12-27T23:19:27Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.