# How do I aggregate based on these fields?

**URL:** <https://discuss.elastic.co/t/how-do-i-aggregate-based-on-these-fields/284821>\
**Category:** Elasticsearch\
**Created:** [September 22, 2021, 9:02am UTC](https://discuss.elastic.co/t/how-do-i-aggregate-based-on-these-fields/284821 "2021-09-22T09:02:54Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![blueren](https://avatars.discourse-cdn.com/v4/letter/b/ecae2f/32.png) [@blueren](https://discuss.elastic.co/u/blueren)\
**Post date:** [September 22, 2021, 9:02am UTC](https://discuss.elastic.co/t/how-do-i-aggregate-based-on-these-fields/284821/1 "2021-09-22T09:02:54Z")

</div>

Hi all,

I'm trying to come up with a query that results in a table that is similar to:

SourceCity | dstCity | dstCountry | dstCountryISO | total sum of flows | total data transferred  
cityA | cityB | countryB | CB | 15 | 3GB  
cityA | cityC | countryB | CB | 25 | 2GB  
cityB | cityD | countryD | CD | 125 | 12GB

The records in ES are similar to the below (pruned other data that isn't required)

```auto
{
        "_index" : "flows-2021.07.03-000001",
        "_type" : "_doc",
        "_id" : "flows-2021.07.03+0+82",
        "_score" : 1.0,
        "_source" : {
                  "startTime": (unix timestamp)
		  "srcCountry" : "CountryA",
		  "srcCountryISO": "CA",
          "srcCity" : "cityA",
		  "srcLatLon": "lat,lon",
          "dstCountry" : "CountryB",
		  "dstCountryISO" : "CB",
          "dstCity" : "cityB",
		  "dstLatLon": "lat,lon",
          "srcAddr" : "192.168.100.1",
          "dstAddr" : "104.211.26.226"
        }
      },

```

How do I aggregate flows based on timestamp (eg, in the last 24hours) and unique destiations, and get the following: dstCountry, dstCountryISO, dstCity, dstLatLon

I tried having nested aggregations of dstCity, dstCountry, dstCountryISO and I found it to be very combursome since the results come in the same order of nesting. I'm sure there is a better and elegant "right" way to query. Something that my current knowledge on ES queries isn't helping me with. Some pointers to this would be useful!

Thanks!

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [October 20, 2021, 9:03am UTC](https://discuss.elastic.co/t/how-do-i-aggregate-based-on-these-fields/284821/2 "2021-10-20T09:03:11Z")

</div>

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