# Sum distinct numeric values in object datatype

**URL:** <https://discuss.elastic.co/t/sum-distinct-numeric-values-in-object-datatype/69693>\
**Category:** Elasticsearch\
**Created:** [December 21, 2016, 6:12pm UTC](https://discuss.elastic.co/t/sum-distinct-numeric-values-in-object-datatype/69693 "2016-12-21T18:12:26Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![geppo](https://avatars.discourse-cdn.com/v4/letter/g/3be4f8/32.png) [@geppo](https://discuss.elastic.co/u/geppo)\
**Post date:** [December 21, 2016, 6:12pm UTC](https://discuss.elastic.co/t/sum-distinct-numeric-values-in-object-datatype/69693/1 "2016-12-21T18:12:26Z")

</div>

Hello,  
I'm now indexing some newspaper documents. I have created some object fields in my index that collect all the entities founded in the text field and its frequencies in the same text field, E.G.:

> ```
> "people": [
> {
> "count": 2,
> "value": "Ermanno"
> },
> {
> "count": 2,
> "value": "Anna Finocchiaro"
> },
> {
> "count": 2,
> "value": "Roberto Calderoli"
> },
> {
> "count": 2,
> "value": "Silvio Berlusconi"
> },
> {
> "count": 2,
> "value": "Denis Verdini"
> },
> {
> "count": 2,
> "value": "Paolo Romani"
> },
> {
> "count": 2,
> "value": "Juncker"
> },
> {
> "count": 2,
> "value": "Federica Mogherini"
> },
> {
> "count": 4,
> "value": "Angela Merkel"
> },
> {
> "count": 2,
> "value": "Matteo Renzi"
> },
> {
> "count": 2,
> "value": "Junker"
> },
> {
> "count": 2,
> "value": "Beppe Grillo"
> },
> {
> "count": 4,
> "value": "Giancarlo Galan"
> },
> {
> "count": 2,
> "value": "Myrta Merlino"
> },
> {
> "count": 2,
> "value": "Yara Gambirasio"
> },
> {
> "count": 2,
> "value": "Francesco Dettori"
> },
> {
> "count": 2,
> "value": "John Kerry"
> },
> {
> "count": 2,
> "value": "Obama"
> },
> {
> "count": 2,
> "value": "Putin"
> },
> {
> "count": 2,
> "value": "Kuchma"
> },
> {
> "count": 6,
> "value": "Prandelli"
> },
> {
> "count": 2,
> "value": "Cesare"
> },
> {
> "count": 2,
> "value": "Chiellini"
> },
> {
> "count": 2,
> "value": "Pirlo"
> },
> {
> "count": 2,
> "value": "Balotelli"
> }
> ]
> 
> ```

the mapping settings are these ones:

> ```
> "people":{
> "properties": {
> "count": {
> "type": "integer",
> "doc_values": true,
> "index": true
> },
> "value":{  
> "type": "text",
> "analyzer": "namedentities_analyzer",
> "fielddata": true
> }
> }
> }
> 
> ```

Now I would like to make a query to retrieve all the people entity found in all the newspapers of the last two months and order them by the sums of their frequencies count. Something like this:

value: Bergoglio, sum\_of\_count: 256, doc\_count:200  
value: Berlusconi, sum\_of\_count: 239, doc\_count: 180,  
etc....  
How i can do that? I have to change my data structure?

---

<div class="post-metadata">

**Author:** ![xavierfacq](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/xavierfacq/32/8744_2.png) [@xavierfacq](https://discuss.elastic.co/u/xavierfacq)\
**Post date:** [December 21, 2016, 9:58pm UTC](https://discuss.elastic.co/t/sum-distinct-numeric-values-in-object-datatype/69693/2 "2016-12-21T21:58:31Z")

</div>

Hi,

In order to use aggregations, I think you'll have to convert your field "value" to not\_analyzed ?  
After, you should try to make some test queries and play with Aggregations.

bye,  
Xavier

---

<div class="post-metadata">

**Author:** ![geppo](https://avatars.discourse-cdn.com/v4/letter/g/3be4f8/32.png) [@geppo](https://discuss.elastic.co/u/geppo)\
**Post date:** [December 22, 2016, 10:41am UTC](https://discuss.elastic.co/t/sum-distinct-numeric-values-in-object-datatype/69693/3 "2016-12-22T10:41:00Z")

</div>

HI, I have changed my index mapping according your suggestion, but unfortunately the result doesn't change. I have tried to produce the desired output concatenating two aggregation, the first one a term aggregation on location.value and for the second one I have tried sum aggregation, cardinality, and value\_count, but I can't understand the results:

> {  
> "\_source": {  
> "includes": ["organization.value", "organization.count"],  
> "excludes": ["text","vectterms", "sentence.sentence\_text"]  
> },  
> "size": 0,  
> "aggs": {  
> "group\_by\_org": {  
> "terms": {  
> "field": "organization.value"  
> } ,  
> "aggs": {  
> "count\_sum": {  
> "cardinality": {  
> "field": "organization.count"  
> }}}}}}

with cardinality aggregation, I have this strange output, where sometimes the value of the second aggregation is less than the value of doc\_count - what is this value?:

> "aggregations": {  
> "group\_by\_org": {  
> "doc\_count\_error\_upper\_bound": 2,  
> "sum\_other\_doc\_count": 91,  
> "buckets": [  
> {  
> "key": "Consiglio comunale",  
> "doc\_count": 7,  
> "count\_sum": {  
> "value": 6  
> }  
> },  
> {  
> "key": "AA",  
> "doc\_count": 5,  
> "count\_sum": {  
> "value": 6  
> }  
> },  
> {  
> "key": "Cassazione",  
> "doc\_count": 5,  
> "count\_sum": {  
> "value": 6  
> }  
> },  
> {  
> "key": "Corte dei conti",  
> "doc\_count": 5,  
> "count\_sum": {  
> "value": 6  
> }  
> },  
> {  
> "key": "Metroweb",  
> "doc\_count": 5,  
> "count\_sum": {  
> "value": 6  
> }  
> },  
> {  
> "key": "Milan",  
> "doc\_count": 5,  
> "count\_sum": {  
> "value": 6  
> }  
> },  
> {  
> "key": "Minardi",  
> "doc\_count": 5,  
> "count\_sum": {  
> "value": 6  
> }

the other two aggregations tried, sum and value\_count, return to me a very high value of this aggregation - In the same way of the previous query, replacing cardinality with sum and value\_count, I think this is the number of all the organization entity in all document where appears that entity, but I can't understand well:

output of sum aggregation - I have only 9 documents in my index, and each entity return less than 10 times per document :

> "buckets": [  
> {  
> "key": "Consiglio comunale",  
> "doc\_count": 7,  
> "count\_sum": {  
> "value": 332  
> }  
> },  
> {  
> "key": "AA",  
> "doc\_count": 5,  
> "count\_sum": {  
> "value": 301  
> }  
> },  
> {  
> "key": "Cassazione",  
> "doc\_count": 5,  
> "count\_sum": {  
> "value": 301  
> }  
> },  
> {  
> "key": "Corte dei conti",  
> "doc\_count": 5,  
> "count\_sum": {  
> "value": 301

output of value\_count:

> "buckets": [  
> {  
> "key": "Consiglio comunale",  
> "doc\_count": 7,  
> "count\_sum": {  
> "value": 105  
> }  
> },  
> {  
> "key": "AA",  
> "doc\_count": 5,  
> "count\_sum": {  
> "value": 80  
> }  
> },  
> {  
> "key": "Cassazione",  
> "doc\_count": 5,  
> "count\_sum": {  
> "value": 80  
> }  
> },  
> {  
> "key": "Corte dei conti",  
> "doc\_count": 5,  
> "count\_sum": {  
> "value": 80  
> }

Can someone help me?

---

<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:** [January 19, 2017, 10:41am UTC](https://discuss.elastic.co/t/sum-distinct-numeric-values-in-object-datatype/69693/4 "2017-01-19T10:41:24Z")

</div>

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