# Retrieve unique key names from "property" field **URL:** \ **Category:** Elasticsearch\ **Created:** [June 16, 2020, 9:45am UTC](https://discuss.elastic.co/t/retrieve-unique-key-names-from-property-field/237268 "2020-06-16T09:45:43Z")\ **Posts on this page:** 10\ **Page:** 1
**Author:** ![mashuma](https://avatars.discourse-cdn.com/v4/letter/m/a87d85/32.png) [@mashuma](https://discuss.elastic.co/u/mashuma)\ **Post date:** [June 16, 2020, 9:45am UTC](https://discuss.elastic.co/t/retrieve-unique-key-names-from-property-field/237268/1 "2020-06-16T09:45:44Z")
Hello everyone, Quick question.. I've got following documents. ```auto [ { "aid": 1, "ameta": { "City": "Brussels", "Fleet": "", "Works": "Building", "Status": "new", "Country": "Belgium" } }, { "aid": 2, "ameta": { "City": "Amsterdam", "Works": "Home", "Country": "Netherlands" } }, { "aid": 3, "ameta": { "City": "Berlin", "Status": "ready", "Timezone": "CET", "Country": "Germany" } } ] ``` I'm looking for an aggregation query that can return all unique "terms" for `ameta` field. Something like ```auto City Fleet Works Status Country Timezone ``` In PGSQL I can do `select distinct skeys(ameta) from ...` (given ameta is hstore), but I'm struggling to find same approach to elastic. Thanks in advance! ---
**Author:** ![gab.bernasconi](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/gab.bernasconi/32/57955_2.png) [@gab.bernasconi](https://discuss.elastic.co/u/gab.bernasconi)\ **Post date:** [June 16, 2020, 10:44am UTC](https://discuss.elastic.co/t/retrieve-unique-key-names-from-property-field/237268/2 "2020-06-16T10:44:27Z")
I think you are looking for the [Terms Aggregation](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-terms-aggregation.html) Given your example dataset, I would do something like this: ```auto GET / **Author:** ![mashuma](https://avatars.discourse-cdn.com/v4/letter/m/a87d85/32.png) [@mashuma](https://discuss.elastic.co/u/mashuma)\ **Post date:** [June 16, 2020, 11:31am UTC](https://discuss.elastic.co/t/retrieve-unique-key-names-from-property-field/237268/3 "2020-06-16T11:31:50Z") Thanks for the feedback, but the expected output is not the unique values for 'ameta.city' or any other field values under 'ameta', but the unique key names across all 'ameta' fields. The query or aggregation I'm looking for should produce something like ```auto ["City", "Fleet", "Works", "Status", "Country", "Timezone"] ``` from the example input of ```auto [ { "aid": 1, "ameta": { "City": "Brussels", "Fleet": "", "Works": "Building", "Status": "new", "Country": "Belgium" } }, { "aid": 2, "ameta": { "City": "Amsterdam", "Works": "Home", "Country": "Netherlands" } }, { "aid": 3, "ameta": { "City": "Berlin", "Status": "ready", "Timezone": "CET", "Country": "Germany" } } ] ``` The actual format of the output does not really matter as long as I can extrapolate the key names from it later in the code that would consuming it. ---
**Author:** ![mashuma](https://avatars.discourse-cdn.com/v4/letter/m/a87d85/32.png) [@mashuma](https://discuss.elastic.co/u/mashuma)\ **Post date:** [June 16, 2020, 11:33am UTC](https://discuss.elastic.co/t/retrieve-unique-key-names-from-property-field/237268/4 "2020-06-16T11:33:33Z")
The whole issue is that we want to provide list of possible keys to the user to choose from. We do not know how many different "keys" there are in the index under "ameta" beforehand. What we used to do is get all fields and their values and extrapolate from that. However after upgrading our labs to 7.6.x from 6.8.x we run into error ```auto field expansion matches too many fields, limit: 1024, got: 1672 ``` ---
**Author:** ![gab.bernasconi](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/gab.bernasconi/32/57955_2.png) [@gab.bernasconi](https://discuss.elastic.co/u/gab.bernasconi)\ **Post date:** [June 16, 2020, 11:38am UTC](https://discuss.elastic.co/t/retrieve-unique-key-names-from-property-field/237268/5 "2020-06-16T11:38:12Z")
Ok, sorry I misunderstood your question. Would the [Get mapping API](https://www.elastic.co/guide/en/elasticsearch/reference/current/indices-get-mapping.html) be useful in this case? ---
**Author:** ![mashuma](https://avatars.discourse-cdn.com/v4/letter/m/a87d85/32.png) [@mashuma](https://discuss.elastic.co/u/mashuma)\ **Post date:** [June 16, 2020, 11:43am UTC](https://discuss.elastic.co/t/retrieve-unique-key-names-from-property-field/237268/6 "2020-06-16T11:43:38Z")
Yes and no. Mapping API would solve this, if the data would be for whole index. In this case I can take a look at mapping and get the fields from there. This is sort of what we have been doing thus far by grabbing all data as mentioned in previous reply. Actual use case involves retrieving list of "ameta" fields (or key names) for a subset of records.. Such as `aid` between 100 and 200 or some other criteria. I've looked around quite a bit and starting to feel like such use case is not supported in elastic ☹ ---
**Author:** ![gab.bernasconi](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/gab.bernasconi/32/57955_2.png) [@gab.bernasconi](https://discuss.elastic.co/u/gab.bernasconi)\ **Post date:** [June 16, 2020, 11:53am UTC](https://discuss.elastic.co/t/retrieve-unique-key-names-from-property-field/237268/7 "2020-06-16T11:53:16Z")
Maybe the [\_field\_names](https://www.elastic.co/guide/en/elasticsearch/reference/current/mapping-field-names-field.html) field could help you in this case, but again, I don't think it can be coupled with search criteria. ---
**Author:** ![mashuma](https://avatars.discourse-cdn.com/v4/letter/m/a87d85/32.png) [@mashuma](https://discuss.elastic.co/u/mashuma)\ **Post date:** [June 16, 2020, 2:33pm UTC](https://discuss.elastic.co/t/retrieve-unique-key-names-from-property-field/237268/8 "2020-06-16T14:33:48Z")
Unfortunately it is a hard requirement to have this aggregation done for subset of documents. As each targeted "segment" of data may have very different available keys to choose from. ---
**Author:** ![Vinayak\_Sapre](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/vinayak_sapre/32/45939_2.png) [@Vinayak\_Sapre](https://discuss.elastic.co/u/Vinayak_Sapre)\ **Post date:** [June 16, 2020, 7:18pm UTC](https://discuss.elastic.co/t/retrieve-unique-key-names-from-property-field/237268/9 "2020-06-16T19:18:44Z")
@mashuma I am not sure how well it will perform. But you can achieve it using scripted metric aggregation ```auto { "size": 0, "aggs": { "ameta_fields": { "scripted_metric": { "init_script": "state.propsMap = new HashMap()", "map_script": "for (String p : params['_source']['ameta'].keySet()) { state.propsMap.put(p, 1); }", "combine_script": "return state.propsMap;", "reduce_script": "Map combined = new HashMap(); for (s in states) { for (k in s.keySet()) { combined.put(k, 1); } } return combined" } } } } ``` ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [July 14, 2020, 7:21pm UTC](https://discuss.elastic.co/t/retrieve-unique-key-names-from-property-field/237268/10 "2020-07-14T19:21:33Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.