Search syntax to provide unique values for a single field in the result set


(Jeremy Colton) #1

I'm trying to find all countries whose users visited a specific website. My ES document has 'publisherDomain' and 'geoip.country_name'.

So far I have:

GET /logstash-*/_search?size=100
{
  "query": {     
    "match": {
      "publisherDomain": "mysite.com"
    }
  }
}

How do I filter the results to only include the 'geoip.country_code' values?

Many thanks.


(Shaunak Kashyap) #2

As this more of an Elasticsearch question than Kibana, I'm moving it to the Elasticsearch category. If there is a followup Kibana question to this, feel free to move it to the Kibana category at that time.

If I understand your question correctly, you want the resulting hits in the response to only show the geoip.country_code field (as opposed to all the fields under _source. To do this you can use source filtering to only include the geoip.country_code field in the results.

Of course, this would give you all values in the geoip.country_code, as opposed to just the unique ones. If you want unique ones you can use the terms aggregation on the geoip.country_code field. Note that, in this case, you might as well specify size as 0 as the unique list of terms you are looking for will be in the aggregations section of the response, not the hits section.


(Jeremy Colton) #3

Thanks for your reply. I have your first option working but getting unique string values is much better as you suggested. I have checked the aggregrations link you provided and have this so far:

GET /logstash-*/_search?size=100
{
  "_source": "geoip.country_name",
  "query": {     
    "match": {
      "publisherDomain": "fashionseoul.com"
    }
  },
  "aggs" : {
        "genres" : {
            "terms" : { "field" : "geoip.country_code" }
        }
    }
}

But it doesn't aggregate the results. How do I combine the filter in the 'aggs'?

I also tried the simpler:

GET /logstash-*/_search?
{
  "_source": "geoip.country_name",
    "aggs" : {
        "genres" : {
            "terms" : { "field" : "geoip.country_code" }
        }
    }
}

Again, this does not aggregate anything! All the results are under 'hits' and nothing is under 'aggregations'. What am i missing?

Many thanks.


(Jeremy Colton) #4

@shaunak I managed to get it working with this:

GET /logstash-*/_search?search_type=count
{
	"size": 0,
	"query": {
		"filtered": {
			"query": {
				"query_string": {
					"query": "publisherDomain:mysite.com",
					"analyze_wildcard": true
				}
			}
		}
	},
	"aggs": {
		"domains": {
			"terms": {
				"field": "geoip.country_name.raw",
			  "size": 5,
				"order": {
					"_count": "desc"
				}
			}
		}
	}
}

Please let me know if you think it can be improved.

Many thanks.


(system) #5