# Query filtering based on one element in an array of nested elements

**URL:** <https://discuss.elastic.co/t/query-filtering-based-on-one-element-in-an-array-of-nested-elements/161533>\
**Category:** Elasticsearch\
**Created:** [December 19, 2018, 1:41pm UTC](https://discuss.elastic.co/t/query-filtering-based-on-one-element-in-an-array-of-nested-elements/161533 "2018-12-19T13:41:09Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![codobbit](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/codobbit/32/39562_2.png) [@codobbit](https://discuss.elastic.co/u/codobbit)\
**Post date:** [December 19, 2018, 1:41pm UTC](https://discuss.elastic.co/t/query-filtering-based-on-one-element-in-an-array-of-nested-elements/161533/1 "2018-12-19T13:41:09Z")

</div>

Hello everyone!

Been grappling this issue for a couple of days now and cannot find a way out of it. I'm using C# and NEST but the issue is in my filtration.  
I have a product with nested prices array for each different price groups. I want to be able to get Min, Max and Avg aggregations but only based on the object from the nested list which is in the current price group.

The following code returns a valid response but the Min, Max and Avg values returned analyze all the prices in the price groups instead of just the one that matches. This leads me to believe that the issue is somewhere in a filtered query.

This is what the mapping looks like:  
"\_index": "products\_da-dk",  
"\_type": "productbase",  
"\_id": "320",  
"\_version": 2,  
"\_score": 1,  
"\_source": {

```
"id": 320,
"sku": "1456",
"variantSku": "1364",
"productId": "320",
...
"productPrices": { 
"priceForPriceGroups": [
    {
        "priceGroupName": "PriceGroup1",
        "currency": "DKK",
        "price": 124.5,
        "priceWithVAT": 155.625,
        "priceWithoutVAT": 124.5,
        "priceBeforeDiscount": 124.5,
        "vATValue": 31.125,
        "priceGroupVATPercentage": 25
    }
    ,
    {
        "priceGroupName": "PriceGroup10",
        "currency": "GBP",
        "price": 5.39,
        "priceWithVAT": 6.7375,
        "priceWithoutVAT": 5.39,
        "priceBeforeDiscount": 5.39,
        "vATValue": 1.3475,
        "priceGroupVATPercentage": 25
    }
]

```

}  
And this is the C# code I am using to get the Aggregations.

```
return new SearchDescriptor<ProductBase>()
             .Index(_indexName)
             .From(0)
             .Size(10000)
             .Aggregations(ag => ag
                 .Terms("categories", t => t
                    .Field(p => p.Categories.Suffix("name").Suffix("keyword"))
                    .Size(100))
                 .Terms("certificates", term => term
                     .Field(field => field.Certificates.Suffix("keyword")))
                 .Terms("contents", term => term
                     .Field(field => field.Contents.Suffix("keyword")))
                 .Terms("type", term => term
                     .Field(field => field.Properties["Type"].Suffix("keyword")))
                 .Terms("function", term => term
                     .Field(field => field.Properties["Function"].Suffix("keyword")))
                   
                    .Filter("filterPriceGroup", filter => filter
                        .Filter(f => f
                            .Term(t => t
                                .Field(field => field.ProductPrices.PriceForPriceGroups.Suffix("priceGroupName").Suffix("keyword"))
                                .Value(_currentPriceGroup.Name)))
                   .Aggregations( agg => agg
                                        .Min("min", st => st
                                                .Field(priceField))
                                        .Max("max", st => st
                                                .Field(priceField))
                                        .Average("avg", st => st
                                            .Field(priceField))
                                        .Range("price", ra => ra
                                            .Field(priceField)
                                            .Ranges(
                                                r => r.From(min).To(avg),
                                                r => r.From(avg).To(max)))
                                    ))
                              );

```

(Just as extra context, the priceField is dynamically selected to fetch the price with or without VAT.)

Anyone has any ideas?

---

<div class="post-metadata">

**Author:** ![abdon](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/abdon/32/9195_2.png) [@abdon](https://discuss.elastic.co/u/abdon)\
**Post date:** [December 21, 2018, 10:31am UTC](https://discuss.elastic.co/t/query-filtering-based-on-one-element-in-an-array-of-nested-elements/161533/2 "2018-12-21T10:31:01Z")

</div>

Is `priceForPriceGroups` really mapped as a nested type? Can you share the output of:

```auto
GET products_da-dk/_mapping

```

I suspect that `priceForPriceGroups` is mapped as a regular object instead of a nested type.

Your aggregations run on the entire document rather than just the priceGroup that matches your filter. You will need to remodel your data to use a nested type, and then wrap your aggregations in a [nested aggregation](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-nested-aggregation.html).

---

<div class="post-metadata">

**Author:** ![codobbit](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/codobbit/32/39562_2.png) [@codobbit](https://discuss.elastic.co/u/codobbit)\
**Post date:** [January 7, 2019, 10:55am UTC](https://discuss.elastic.co/t/query-filtering-based-on-one-element-in-an-array-of-nested-elements/161533/3 "2019-01-07T10:55:18Z")

</div>

Hi Abdon,

Thanks for the reply. I finally got a chance to pick the issue up again. Here is the output regarding the Product Prices bit from the query:

```
* "productPrices": {
  * "properties": {
    * "id": {
      * "type": "text",
      * "fields": {
        * "keyword": {
          * "type": "keyword",
          * "ignore_above": 256}}},
    * "priceForPriceGroups": {
      * "properties": {
        * "currency": {
          * "type": "text",
          * "fields": {
            * "keyword": {
              * "type": "keyword",
              * "ignore_above": 256}}},
        * "price": {
          * "type": "double"},
        * "priceBeforeDiscount": {
          * "type": "double"},
        * "priceGroupName": {
          * "type": "text",
          * "fields": {
            * "keyword": {
              * "type": "keyword",
              * "ignore_above": 256}}},
        * "priceGroupVATPercentage": {
          * "type": "integer"},
        * "priceWithVAT": {
          * "type": "double"},
        * "priceWithoutVAT": {
          * "type": "double"},
        * "productCatalogName": {
          * "type": "text",
          * "fields": {
            * "keyword": {
              * "type": "keyword",
              * "ignore_above": 256}}},
        * "vATValue": {
          * "type": "double"}}},
     "productId": {
       "type": "text",
      * "fields": {
        * "keyword": {
          * "type": "keyword",
          * "ignore_above": 256}}}}},

```

Changed it to nested instead. It looks like this now:

```
"pricesForPriceGroups": {

    "type": "nested",
    "properties": {
        "currency": {
            "type": "text",
            "fields": {
                "keyword": {
                    "type": "keyword",
                    "ignore_above": 256
                }
            }
        },
        "price": {
            "type": "double"
        },
        "priceBeforeDiscount": {
            "type": "double"
        },
        "priceGroupName": {
            "type": "text",
            "fields": {
                "keyword": {
                    "type": "keyword",
                    "ignore_above": 256
                }
            }
        },
        "priceGroupVATPercentage": {
            "type": "integer"
        },
        "priceWithVAT": {
            "type": "double"
        },
        "priceWithoutVAT": {
            "type": "double"
        },
        "productCatalogName": {
            "type": "text",
            "fields": {
                "keyword": {
                    "type": "keyword",
                    "ignore_above": 256
                }
            }
        },
        "vATValue": {
            "type": "double"
        }
    }

},

```

And I am attempting to use a nested aggregation and ensure that I am getting the data only from a single price group:

```
 return new SearchDescriptor<ProductBase>()
                .Index(_indexName)
                .From(0)
                .Size(10000)
                .Aggregations(ag => ag
                    .Terms("categories", t => t
                        .Field(p => p.Categories.Suffix("name").Suffix("keyword"))
                        .Size(100))
                    .Terms("certificates", term => term
                        .Field(field => field.Certificates.Suffix("keyword")))
                    .Terms("contents", term => term
                        .Field(field => field.Contents.Suffix("keyword")))
                    .Terms("type", term => term
                        .Field(field => field.Properties["Type"].Suffix("keyword")))
                    .Terms("function", term => term
                        .Field(field => field.Properties["Function"].Suffix("keyword")))
                    .Nested("nested", n => n
                         .Path(f => f.PricesForPriceGroups) 
                         .Aggregations(agg => agg
                                 .Filters("fil", fil => fil
                                    .AnonymousFilters(fi => fi
                                        .Bool(b => b
                                            .Must(m => m
                                                .Term(te => te
                                                    .Field(field => field.PricesForPriceGroups.Suffix("priceGroupName").Suffix("keyword"))
                                                    .Value(_currentPriceGroup.Name))))))
                                    .Min("min", st => st
                                          .Field(p => p.PricesForPriceGroups.Suffix("priceWithoutVAT")) ) 

```

But the inner aggregations are null and the values are also 0 or empty.

I also tried this:

```
...
 .Nested("nested", n => n
                         .Path(f => f.PricesForPriceGroups)
                         .Aggregations(agg => agg
                                 .Filters("fil", fil => fil
                                    .AnonymousFilters(fi => fi
                                        .Bool(b => b
                                            .Must(m => m
                                                    .Match( ma => ma
                                                    .Field( field => field.PricesForPriceGroups.Suffix("priceGroupName").Suffix("keyword"))
                                                    .Query(_currentPriceGroup.Name))))))
                                    .Min("min", st => st
                                          .Field(p => p.PricesForPriceGroups.Suffix("priceWithoutVAT")) )

```

But it has the same result. Any input is appreciated.

---

<div class="post-metadata">

**Author:** ![abdon](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/abdon/32/9195_2.png) [@abdon](https://discuss.elastic.co/u/abdon)\
**Post date:** [January 16, 2019, 7:56pm UTC](https://discuss.elastic.co/t/query-filtering-based-on-one-element-in-an-array-of-nested-elements/161533/4 "2019-01-16T19:56:08Z")

</div>

I think you are hitting a typo. The field is called `pricesForPriceGroups` in the mapping, but `priceForPriceGroups` in your sample document (there is a missing `s`).

---

<div class="post-metadata">

**Author:** ![codobbit](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/codobbit/32/39562_2.png) [@codobbit](https://discuss.elastic.co/u/codobbit)\
**Post date:** [January 18, 2019, 10:53am UTC](https://discuss.elastic.co/t/query-filtering-based-on-one-element-in-an-array-of-nested-elements/161533/5 "2019-01-18T10:53:21Z")

</div>

Yeah, that was one of the issues. Got it work finally- turned out that the problem was that when I removed and rebuilt the index, it indexed the old property that wasn't nested as well as the new one- which was empty. Removed, re-indexed and it worked.

---

<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:** [February 15, 2019, 10:53am UTC](https://discuss.elastic.co/t/query-filtering-based-on-one-element-in-an-array-of-nested-elements/161533/6 "2019-02-15T10:53:31Z")

</div>

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