Elasticsearch show sum of same keys of each data

Question

can anyone please help me with this complicated sum with elasticsearch since I've been searching and trying for days, yet seems no one have the same problem as mine.

let say I have this sample data :

[
    {
        "id": 4,
        "status": "draft",
        "video": {
            "platforms": [
                {
                    "names": "youtube",
                    "views": 1
                },
                {
                    "names": "dailymotion",
                    "views": 4
                }
            ]
        }
    },
    {
        "id": 2,
        "status": "published",
        "video": {
            "platforms": [
                {
                    "names": "youtube",
                    "views": 2
                },
                {
                    "names": "dailymotion",
                    "views": 10
                }
            ]
        }
    },
    {
        "id": 1,
        "status": "published",
        "video": {
            "platforms": [
                {
                    "names": "youtube",
                    "views": 5
                },
                {
                    "names": "dailymotion",
                    "views": 3
                }
            ]
        }
    }
]

this is what I have so far :

{
    "size": 0,
    "aggs": {
        "sum_views": {  
            "nested": {
                "path": "video.platforms"
            },
            "aggs": {
                "platform_terms": {
                    "terms": {
                        "field": "video.platforms.names"
                    },
                    "aggs": {
                        "platform_sum": {
                            "sum": {
                                "field": "video.platforms.views"
                            }
                        }
                    }
                }
            }
        }
    }
}

but they only return this :

"aggregations": {
        "sum_views": {
            "doc_count": 3,
            "platform_terms": {
                "doc_count_error_upper_bound": 0,
                "sum_other_doc_count": 0,
                "buckets": [
                    {
                        "key": "youtube",
                        "doc_count": 3,
                        "platform_sum": {
                            "value": 8.0
                        }
                    },
                    {
                        "key": "dailymotion",
                        "doc_count": 3,
                        "platform_sum": {
                            "value": 17.0
                        }
                    }
                ]
            }
        },

what I'm looking for is how to sum total views from both platforms (separate), in each status. something like :

[
    {
        "status": "draft",
        "views": [
            {
                "names": "youtube",
                "views": 1
            },
            {
                "names": "dailymotion",
                "views": 4
            }
        ]
    },
    {
        "status": "published",
        "views": [
            {
                "names": "youtube",
                "views": 7
            },
            {
                "names": "dailymotion",
                "views": 13
            }
        ]
    }
]

so they sum both youtube and dailymotion and also grouping them by status. I've tried aggregation sum from elasticsearch and I only can have sum of all views from youtube and dailymotion separately OR sum of all views from published and draft. I can't find how to combine both condition

Answer 1

You're on the right path, you just need to aggregate by your status field first, like this:

{
  "size": 0,
  "aggs": {
    "status": {
      "terms": {
        "field": "status",
        "size": 10
      },
      "aggs": {
        "sum_views": {
          "nested": {
            "path": "video.platforms"
          },
          "aggs": {
            "platform_terms": {
              "terms": {
                "field": "video.platforms.names"
              },
              "aggs": {
                "platform_sum": {
                  "sum": {
                    "field": "video.platforms.views"
                  }
                }
              }
            }
          }
        }
      }
    }
  }
}