# Filter on distinct nested documents **URL:** \ **Category:** Elasticsearch\ **Created:** [January 28, 2023, 9:44pm UTC](https://discuss.elastic.co/t/filter-on-distinct-nested-documents/324168 "2023-01-28T21:44:43Z")\ **Posts on this page:** 3\ **Page:** 1
**Author:** ![Sadia\_Mukhtar](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/sadia_mukhtar/32/105737_2.png) [@Sadia\_Mukhtar](https://discuss.elastic.co/u/Sadia_Mukhtar)\ **Post date:** [January 28, 2023, 9:44pm UTC](https://discuss.elastic.co/t/filter-on-distinct-nested-documents/324168/1 "2023-01-28T21:44:43Z")
I am very new to Elasticsearch and it's data modeling techniques. My dataset is a list of users and locations the users' have been associated with. I am trying to implement a type-ahead search on locations, I need to get a distinct set of locations from my dataset and then filter on those distinct locations based on user input (sample kibana commands are provided below) my data: ```auto [ { "Username" : "user2", "FirstName" : "Jane", "LastName" : "Doe", "UserLocations" : [ { "CountryDisplayText" : "USA", "CountryID" : 1 } ] }, { "Username" : "user1", "FirstName" : "John", "LastName" : "Doe", "UserLocations" : [ { "CountryDisplayText" : "USA", "CountryID" : 1 }, { "CountryDisplayText" : "UK", "CountryID" : 2 } ] }, { "Username" : "user3", "FirstName" : "Elliot", "LastName" : "Richardson", "UserLocations" : [ { "CountryDisplayText" : "USA", "CountryID" : 1 }, { "CountryDisplayText" : "Canada", "CountryID" : 3 } ] } ] ``` For example, if the user enters U, I only want to return USA and UK in the result. But the query I wrote returns all locations for the users that contain even 1 location that starts with U. my query: ```auto GET /users/_search { "query": { "nested": { "path": "UserLocations", "query": { "bool": { "must": [ {"multi_match": { "query": "U", "type": "phrase_prefix", "fields": ["UserLocations.CountryDisplayText"] }} ] } } } }, "aggs": { "locations": { "nested": { "path": "UserLocations" }, "aggs": { "distinctcountries": { "multi_terms": { "terms": [{ "field": "UserLocations.CountryDisplayText.keyword" }, { "field": "UserLocations.CountryID" }] } } } } }, "_source" : false, "size": 0 } ``` Result, I get Canada in the list as well. All I would like to see is UK and USA. I basically want to do a group by and where clause on the result set. ```auto ... "aggregations" : { "locations" : { "doc_count" : 5, "distinctcountries" : { "doc_count_error_upper_bound" : 0, "sum_other_doc_count" : 0, "buckets" : [ { "key" : [ "USA", 1 ], "key_as_string" : "USA|1", "doc_count" : 3 }, { "key" : [ "Canada", 3 ], "key_as_string" : "Canada|3", "doc_count" : 1 }, { "key" : [ "UK", 2 ], "key_as_string" : "UK|2", "doc_count" : 1 } ] } ... ``` Sample data setup ```auto PUT /users { "mappings" : { "properties" : { "Username" : { "type" : "text", "fields" : { "keyword" : { "type" : "keyword", "ignore_above" : 256 } } }, "FirstName" : { "type" : "text", "fields" : { "keyword" : { "type" : "keyword", "ignore_above" : 256 } } }, "LastName" : { "type" : "text", "fields" : { "keyword" : { "type" : "keyword", "ignore_above" : 256 } } }, "UserLocations" : { "type": "nested", "properties" : { "CountryDisplayText" : { "type" : "text", "fields" : { "keyword" : { "type" : "keyword", "ignore_above" : 256 } } }, "CountryID" : { "type" : "long" } } } } }, "settings" : { "index" : { "routing" : { "allocation" : { "include" : { "_tier_preference" : "data_content" } } }, "number_of_shards" : "1", "number_of_replicas" : "1" } } } PUT /users/_doc/1 { "Username" : "user1", "FirstName" : "John", "LastName" : "Doe", "UserLocations" : [ { "CountryDisplayText": "USA", "CountryID":1 }, { "CountryDisplayText": "UK", "CountryID":2 } ] } PUT /users/_doc/2 { "Username" : "user2", "FirstName" : "Jane", "LastName" : "Doe", "UserLocations" : [ { "CountryDisplayText": "USA", "CountryID":1 } ] } PUT /users/_doc/3 { "Username" : "user3", "FirstName" : "Elliot", "LastName" : "Richardson", "UserLocations" : [ { "CountryDisplayText": "USA", "CountryID":1 }, { "CountryDisplayText": "Canada", "CountryID":3 } ] } GET /users/_search ``` the following query gets me all distinct location but I want to be able to do starts with or contains search on the distinct location for typeahead. ```auto GET /users/_search { "aggs": { "UserLocations": { "nested": { "path": "UserLocations" }, "aggs": { "distinctCountries": { "multi_terms": { "terms": [{ "field": "UserLocations.CountryDisplayText.keyword" }, { "field": "UserLocations.CountryID" }] } } } } }, "_source" : false, "size": 0 } ``` Thank you so much for your help ---
**Author:** ![Sadia\_Mukhtar](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/sadia_mukhtar/32/105737_2.png) [@Sadia\_Mukhtar](https://discuss.elastic.co/u/Sadia_Mukhtar)\ **Post date:** [January 30, 2023, 5:09pm UTC](https://discuss.elastic.co/t/filter-on-distinct-nested-documents/324168/2 "2023-01-30T17:09:26Z")
tested out an option recommened on the elasticsearch slack channel. But still no luck... any help would be greatly appreciated ```auto GET /users/_search { "query": { "bool": { "must": [ { "nested": { "path": "UserLocations", "query": { "bool": { "filter": [ { "prefix": { "UserLocations.CountryDisplayText.keyword": "U" } } ] } } } } ] } }, "aggs": { "distinctCountries":{ "terms": { "field": "UserLocations.CountryDisplayText.keyword" } } }, "_source" : false, "size": 0 } ``` results: ```auto { "took" : 1, "timed_out" : false, "_shards" : { "total" : 1, "successful" : 1, "skipped" : 0, "failed" : 0 }, "hits" : { "total" : { "value" : 3, "relation" : "eq" }, "max_score" : null, "hits" : [] }, "aggregations" : { "distinctCountries" : { "doc_count_error_upper_bound" : 0, "sum_other_doc_count" : 0, "buckets" : [] } } } ``` ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [February 27, 2023, 5:09pm UTC](https://discuss.elastic.co/t/filter-on-distinct-nested-documents/324168/3 "2023-02-27T17:09:27Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.