Filter, sum and nested fields

Elastic Stack Elasticsearch
1

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

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

Data Samples

          {"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

2

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