use maximum value of nested array for sum aggregation in elasticsearch

Question

I have below mapping in my index:

{
"testIndex": {
    "mappings": {
        "type1": {
            "properties": {
                "text": {
                    "type": "string"
                },
                "time_views": {
                    "type": "nested",
                    "properties": {
                        "timestamp": {
                            "type": "long"
                        },
                        "views": {
                            "type": "integer"
                        }
                    }
                }
            }
        }
    }
}
}

actually "time_views" is an array, but inner attributes not array. this nested type holds history of view count of type1. "views" is an accumulative attribute.

I want to query on my document that retrieve sum of "views" of all documents that an arbitrary word in "text" field occurs in that documents.

I know I should use aggregation but I don't know how for this query.

{
    "query": {
        "term":{
            "text": "anyword"
        }
    },
    "size": 0,
    "aggs":{
        ???
    }
}

as I mentioned above "time_views" is an array for each document and I just want to use the maximum value of "views" of each array.

Sample data

{
    "text": "red car",
    "time_views": [
        {
            "timestamp": 1651116565,
            "views": 100
        },
        {
            "timestamp": 1651546456,
            "views": 153
        },
        {
            "timestamp": 165446456,
            "views": 200
        }
    ]
},
{
    "text": "blue car",
    "time_views": [
        {
            "timestamp": 1651116565,
            "views": 20
        },
        {
            "timestamp": 1651546456,
            "views": 70
        },
        {
            "timestamp": 165446456,
            "views": 130
        }
    ]
},
{
    "text": "green car",
    "time_views": [
        {
            "timestamp": 1651116565,
            "views": 4
        },
        {
            "timestamp": 1651546456,
            "views": 86
        },
        {
            "timestamp": 165446456,
            "views": 100
        }
    ]
}

I expect to get below result when I query for "car":

{
    "text": "car"
    "views": 430
}

where 430 = 200 (max value in first doc) + 130 (max value in second doc) + 100 (max value in third doc)

I don't care about Json structure of the result, I just need the information.

so what should I do? tnx :)

Can you also share a few sample documents and what you expect to get back in the response? — Val
– Val, Commented Jul 21, 2016 at 5:05
I've deleted my answer as it seems it didn't solve your problem. Personally I don't think it's possible without adding a separate field/object "max_view" to the root document which would hold maximum view object which you would need to provide at index time — mbudnik
– mbudnik, Commented Jul 23, 2016 at 7:20
"views" attribute is accumulative, so the last one in the array in the maximum. — Saeed Zhiany
– Saeed Zhiany, Commented Jul 23, 2016 at 8:20

Saeed Zhiany · Accepted Answer · 2016-07-23 14:11:24Z

after doing many search I got finally find a solution. I used "scripted_metric" aggregation and write a custom one. here is my code

{
  "query": {
    "term": {
      "text": "car"
  }
},
"aggs": {
    "views_sum": {
        "scripted_metric": {
            "init_script": "_agg['maximum'] = []",
            "map_script": "max = _source.time_views[0].views; for(tv in _source.time_views){ if(tv.views > max){max = tv.views; }}; _agg.maximum.add(max);",
            "combine_script": "sum = 0; for (m in _agg.maximum) { sum += m }; return sum;",
            "reduce_script": "sum = 0; for (a in _aggs) { sum += a }; return sum;"
        }
    }
},
"size": 0
}

and this is my result:

{
"took": 3,
"timed_out": false,
"_shards": {
    "total": 5,
    "successful": 5,
    "failed": 0
},
"hits": {
    "total": 3,
    "max_score": 0,
    "hits": []
},
"aggregations": {
    "views_sum": {
        "value": 430
    }
}
}

Collectives™ on Stack Overflow

use maximum value of nested array for sum aggregation in elasticsearch

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related