# Filter, sum and nested fields

**URL:** https://discuss.elastic.co/t/filter-sum-and-nested-fields/300486
**Category:** Elasticsearch
**Tags:** painless
**Created:** [March 23, 2022, 4:11pm UTC](https://discuss.elastic.co/t/filter-sum-and-nested-fields/300486 "2022-03-23T16:11:09Z")
**Posts on this page:** 2
**Page:** 1

<div class="post-metadata">

### Author: ![Martin.Br](https://avatars.discourse-cdn.com/v4/letter/m/e0b2c6/32.png) [@Martin.Br](https://discuss.elastic.co/u/Martin.Br)
#### Post date: [March 23, 2022, 4:11pm UTC](https://discuss.elastic.co/t/filter-sum-and-nested-fields/300486/1 "2022-03-23T16:11:09Z")

</div>

Hello,

I am facing some difficulties to execute a search query with filter, sum and nested fields.  
I wish to search within my data all the documents containing positions with assetId 42 and 43.  
Moreover, I want to filter out documents where the sum of the weight of assetId 42 and 43 is above 0.1

Here are some more details regarding my data:

My index mapping

```auto
"mappings" : {
      "properties" : {
        "dataA" : {
          "type" : "float"
        },
        "positions" : {
          "type" : "nested",
          "properties" : {
            "assetId" : {
              "type" : "long"
            },
            "weight" : {
              "type" : "float"
            }
          }
        }
      }
    }

```

Data Samples

```auto
          {"dataA" : 0.0385718379061706,
          "positions" : [
            {
              "assetId" : 46,
              "weight" : 0.02
            },
            {
              "assetId" : 43,
              "weight" : 0.004
            },
            {
              "assetId" : 42,
              "weight" : 0.03
            }
           ]},
          {"dataA" : 0.021344216,
          "positions" : [
            {
              "assetId" : 43,
              "weight" : 0.05
            },
            {
              "assetId" : 48,
              "weight" : 0.074
            },
            {
              "assetId" : 42,
              "weight" : 0.07
            }
           ]}

```

I am having trouble getting only results containing positions both on assetId 42 and 43.  
I have tried to use a Terms query but it seems that it also retrieves documents containing only assetId 42 or only assetId 43.

Any lead on how I could do the sum of the weight of only the positions with assetId 42 and 43 will be great. I have tried to do it with filter and painless script but I have difficulties to reach nested properties value.

I know this is a pretty specific case but I would really appreciated any help.  
Thank you very much,  
Regards,

Martin

---

<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: [April 20, 2022, 4:11pm UTC](https://discuss.elastic.co/t/filter-sum-and-nested-fields/300486/2 "2022-04-20T16:11:21Z")

</div>

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