Wrong counts using nested aggregation with filter having both parent and nested fields

classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

Wrong counts using nested aggregation with filter having both parent and nested fields

thanuja
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": [
 "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": [
  "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 [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/21bd1da6-177c-426d-a282-80b3ccfd5112%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.