# Nested Aggregations returns wrong counts. Any help appreciated

**URL:** <https://discuss.elastic.co/t/nested-aggregations-returns-wrong-counts-any-help-appreciated/23214>\
**Category:** Elasticsearch\
**Created:** [April 13, 2015, 5:14pm UTC](https://discuss.elastic.co/t/nested-aggregations-returns-wrong-counts-any-help-appreciated/23214 "2015-04-13T17:14:37Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![thanuja](https://avatars.discourse-cdn.com/v4/letter/t/b487fb/32.png) [@thanuja](https://discuss.elastic.co/u/thanuja)\
**Post date:** [April 13, 2015, 5:14pm UTC](https://discuss.elastic.co/t/nested-aggregations-returns-wrong-counts-any-help-appreciated/23214/1 "2015-04-13T17:14:37Z")

</div>

I have a catalog of products that I want to calculate aggregates on. The  
trouble comes with trying to do nested aggregations with filter that has  
both nested and parent fields in it. Either it gives wrong counts or 0  
hits. Here is a sample of my product object mapping:

```
"Products": {
        "properties": {
           "ProductID": {
              "type": "long"
           },
           "ProductType": {
              "type": "long"
           },
           "ProductName": {
              "type": "string",
              "fields": {
                 "raw": {
                    "type": "string",
                    "index": "not_analyzed"
                 }
              }
           },
           "Prices": {
              "type": "nested",
              "properties": {
                 "CurrencyType": {
                    "type": "integer"
                 },
                 "Cost": {
                    "type": "double"                        
                 }
              }
          } 
      }
  }

```

Here is an example of the sql query that I am trying to replicate in  
elastic:

```
SELECT PRODPR.Cost AS PRODPR_Cost 
,COUNT(PROD.ProdcutID) AS PROD_ProductID_Count
FROM Products PROD WITH (NOLOCK)
LEFT OUTER JOIN Prices PRODPR WITH (NOLOCK) ON (PRODPR.objectid = 

```

PROD.objectid)  
WHERE PRODPR.CurrencyType = 4  
AND PROD.ProductType IN (  
11273  
,11293  
,11294  
)  
GROUP BY PRODPR.Cost

Elastic Search queries I came up with:

_First One (following query returns correct counts with just CurrencyType  
as filter but when I add ProductType filter, it gives me wrong counts)_

GET /IndexName/Products/\_search  
{  
"aggs": {  
"price\_agg": {  
"filter": {  
"bool": {  
\*\*"must": [  
{  
"nested": {  
"path": "Prices",  
"filter": {  
"term": {  
"Prices.CurrencyType": "8"  
}  
}  
}  
},  
{  
"terms": {  
"ProductType": [ ---------------- Parent  
field  
"11273",  
"11293",  
"11294"  
]  
}  
}  
]  
}  
},  
"aggs": {  
"price\_nested\_agg": {  
"nested": {  
"path": "Prices"  
},  
"aggs": {  
"59316518\_group\_agg": {  
"terms": {  
"field": "Prices.Cost",  
"size": 0  
},  
"aggs": {  
"product\_count": {  
"reverse\_nested": { },  
"aggs": {  
"ProductID\_count\_agg": {  
"value\_count": {  
"field": "ProductID"  
}  
}  
}  
}  
}  
}  
}  
}  
}  
}  
},  
"size": 0  
}

- Second One (following query returns correct counts with just  
CurrencyType as filter but when I add ProductType filter, it gives me 0  
hits):\*

GET /IndexName/Prodcuts/\_search  
{  
"aggs": {  
"price\_agg": {  
"nested": {  
"path": "Prices"  
},  
"aggs": {  
"currency\_filter": {  
"filter": {  
"bool": {  
"must": [  
{  
"term": {  
"Prices.CurrrencyType": "4"  
}  
},  
{  
"terms": {  
"ProductType": [ ---------------- Parent  
field  
"11273",  
"11293"  
]  
}  
}  
]  
}  
},  
"aggs": {  
"59316518\_group\_agg": {  
"terms": {  
"field": "Prices.Cost",  
"size": 0  
},  
"aggs": {  
"product\_count": {  
"reverse\_nested": {},  
"aggs": {  
"ProductID\_count\_agg": {  
"value\_count": {  
"field": "ProductID"  
}  
}  
}  
}  
}  
}  
}  
}  
}  
}  
},  
"size": 0  
}  
I have tried some more queries but the above two are the closest I came up  
with. Has anyone come across this use case? What am I doing wrong? Any help  
is appreciated. Thanks!

--  
You received this message because you are subscribed to the Google Groups "elasticsearch" group.  
To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com).  
To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/a1905997-8ddf-44f0-9623-f272ef33c164%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/a1905997-8ddf-44f0-9623-f272ef33c164%40googlegroups.com).  
For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).

---

<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:** [July 6, 2017, 12:20am UTC](https://discuss.elastic.co/t/nested-aggregations-returns-wrong-counts-any-help-appreciated/23214/2 "2017-07-06T00:20:02Z")

</div>