0

I need to create 15m bucket on Timestamp and then in each timestamp I need sum on each type of books and of course the total of books.

for example, my data is like below

[
   {
      "books":[
         {
            "id":0,
            "count":10
         },
         {
            "id":1,
            "count":11
         },
         {
            "id":2,
            "count":7
         },
         {
            "id":3,
            "count":9
         },
         {
            "id":4,
            "count":16
         }
      ],
      "timestamp":1613693700000,
      "total":53
   },
   {
      "books":[
         {
            "id":0,
            "count":0
         },
         {
            "id":1,
            "count":4
         },
         {
            "id":2,
            "count":9
         },
         {
            "id":3,
            "count":10
         },
         {
            "id":4,
            "count":1
         }
      ],
      "timestamp":1613694600000,
      "total":24
   }
]

I need output like below :

[
   {
      "timestamp":1613693700000,
      "total_count":77,
      "data":[
         {
            "id":0,
            "count":10
         },
         {
            "id":1,
            "count":15
         },
         {
            "id":2,
            "count":16
         },
         {
            "id":3,
            "count":19
         },
         {
            "id":4,
            "count":17
         }
      ]
   }
]

I have tried below query and now I am stuck with the nested query to get sum on each book type in each timestamp bucket. Need help on this.

{
    "aggs": {
        "count": {

            "date_histogram": {
                "field": "timestamp",
                "interval": "15m"
            },
            "aggs": {
                "total_count": {
                    "sum": {
                        "field": "total"
                    }
                }
            }
        }
    }
}
missingsphinx
  • 73
  • 1
  • 6
  • can you please check your sample index data? Do both the documents contain only `"id":0"`? And you want to calculate the sum of count corresponding to each `id` in both the documents? – ESCoder Feb 23 '21 at 00:52
  • Sorry for the typo , fixed it in the question. Yes, I want calculate the sum of count corresponding to each id, there could be n number of such documents – missingsphinx Feb 23 '21 at 19:34

1 Answers1

1

Worked. Not exact same naming structure in output but it's solving the actual problem I had in the question

Posting it if someone is on same boat.

{
   "aggs":{
      "bucket_by_time":{
         "date_histogram":{
            "field":"timestamp",
            "interval":"15m"
         },
         "aggs":{
            "bucket_by_type":{
               "nested":{
                  "path":"data"
               },
               "aggs":{
                  "books":{
                     "terms":{
                        "field":"data.id"
                     },
                     "aggs":{
                        "count":{
                           "sum":{
                              "field":"data.count"
                           }
                        }
                     }
                  },
                  "total_count":{
                     "sum_bucket":{
                        "buckets_path":"books>count"
                     }
                  }
               }
            }
         }
      }
   }
}
missingsphinx
  • 73
  • 1
  • 6