How to perform filter based on aggregation of nested fields? (similar to nested sorting)

I have an index like below:

cargroup

 "mappings": {
    "properties": {
      "id":     { "type": "integer" },
      "cars":   { "type": "nested",
                            "properties": {
                                    "brand": {
                                        "type": "keyword"
                                    },
                                    "value": {
                                        "type": "integer"
                                    }
                                }
                        },

How can I only get cargroup which has 'toyota' brand with sum of value > 100? (sum of value is calculated only among 'toyota' brand)

for example given following documents:

[id: 1,
cars:[{
brand: toyota,
value: 30},
{
brand: toyota,
value: 30},
{
brand: honda,
value: 30}
],
id: 2,
cars:[{
brand: toyota,
value: 30},
{
brand: toyota,
value: 50},
{
brand: honda,
value: 30}
],
id: 1,
cars:[{
brand: toyota,
value: 30},
{
brand: toyota,
value: 80},
{
brand: honda,
value: 30}
]]

I would only want to obtain doc no 3, since its toyota cars sums of value is (80 + 30 = 110) is gt than 100

I had used terms and filtered aggregation to divide car groups into 'id' Bucket and then used sum aggregation on each bucket. However, I hadnt found a way to 'link' each car group to its corresponding bucket for subsequent filtering.

my requirement is similar to nested sort functionality. I can provide a path and filtering logic to nested fields, and then aggregate the result using sort mode. I need similar functionality for filtering.

Thank you!!

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