python + elasticsearch distinct field value query returning empty result

Question

I am completely new to elastic search. I have data

{"username": "tom",
 "dept" : "SE",
 "location": "NY"
}

{"username": "john",
 "dept" : "SE",
 "location": "MA"
}

{"username": "tom",
 "dept" : "DQ",
 "location": "NY"
}

{"username": "mary",
 "dept" : "TY",
 "location": "TA"
}

i want to make elasticsearch query equivalent to

select distinct username from my_index

which will give me result:

["tom", "john", "mary"]

I tried these answers ElasticSearch - Return Unique Values

And made the query

query = {
            "size": 0,
            "aggs": {
                "unique_username": {
                    "terms": {
                        "field": "username.keyword",
                        "size": 200
                    }
                }
            }
        }
es.search(index="my_index", body=query)

This returns

{'took': 64, 'timed_out': False, '_shards': {'total': 5, 'successful': 5, 'skipped': 0, 'failed': 0}, 'hits': {'total': 3200, 'max_score': 0.0, 'hits': []}, 'aggregations': {'unique_username': {'buckets': []}}}

with the help of the query, as per those answers, i was expecting unique username with there count in buckets list, but bucket seems to be empty list

{'buckets': []}

What am I doing wrong?

Also when I am doing https://localhost:9200/my_index/_search?pretty=true&size=5

I get result

{

  "took": 18,
  "timed_out": false,
  "_shards": {
    "total": 5,
    "successful": 5,
    "skipped": 0,
    "failed": 0
  },
  "hits": {
    "total": 3200,
    "max_score": 1,
    "hits": [
      {
        "_index": "my_index_9y9f2b4e-5t90-44a2-b444-t7537fr6656b",
        "_type": "my_table",
        "_id": "1",
        "_score": 1,
        "_source": {
          "username": "tom",
          "dept": "SE",
          "location": "NY"
        }
      },
      {
        "_index": "my_index_9y9f2b4e-5t90-44a2-b444-t7537fr6656b",
        "_type": "my_table",
        "_id": "2",
        "_score": 1,
        "_source": {
          "username": "john",
          "dept": "SE",
          "location": "MA"
        }
      },
      {
        "_index": "my_index_9y9f2b4e-5t90-44a2-b444-t7537fr6656b",
        "_type": "my_table",
        "_id": "3",
        "_score": 1,
        "_source": {
          "username": "tom",
          "dept": "DQ",
          "location": "NY"
        }
      },
      {
        "_index": "my_index_9y9f2b4e-5t90-44a2-b444-t7537fr6656b",
        "_type": "my_table",
        "_id": "4",
        "_score": 1,
        "_source": {
          "username": "mary",
          "dept": "TY",
          "location": "TA"
        }
      }
    ]
  }
}

Thanks in advance!

Dharman · Accepted Answer · 2021-07-09 05:46:20Z

0

It worked

query = {
            "size": 0,
            "aggs": {
                "unique_username": {
                    "terms": {
                        "field": "username.raw", # previously username.keyword
                        "size": 200
                    }
                }
            }
        }
es.search(index="my_index", body=query)

It was my silly mistake, just changing username.keyword to username.raw made it work. as raw was linked to this field instead of keyword

Thank you

edited Jul 9, 2021 at 5:46

Dharman♦

33.9k27 gold badges104 silver badges157 bronze badges

answered Jul 9, 2021 at 5:41

young_minds1

1,5113 gold badges14 silver badges32 bronze badges

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

Collectives™ on Stack Overflow

python + elasticsearch distinct field value query returning empty result

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related