Elasticsearch api - order aggregation by @timestamp

Question

In my index I have a lot of documents with a different structure. The shared keys between all the documents are the following keys: (Store,owner,products,timestamp)

{"Store":"books for school","owner":"user_15","products":40,"@timestamp":2020/08/02T18:00, "a1":1,"a2":...}
{"Store":"books for school","owner":"user_15","products":45,"@timestamp":2020/08/02T19:00,"b1":1...}
{"Store":"books for school","owner":"user_17","products":55,"@timestamp":2020/08/02T20:00, "b2":1....}

In my app, I'm trying to get the most recent shared keys for each store (owner,products). So for this example I wanted to get the last document in the example.

I tried to create an aggregation query on all the shared keys but I'm not sure how to order the inner results by the date (so that the most newest value will be first):

{
  "size": 0,
  "aggs": {
    "store_aggr": {
      "terms": {
        "field": "Store"
      },
      "aggs": {
        "owner_aggr": {
          "terms": {
            "field": "owner"
          }
          }
          ,
            "products_aggr": {
              "terms": {
                "field": "products"
              }
            }
                
        }
      }
    }
  
}

How can I order the inner buckets of the query by @timestamp? In this way I can just take the first value and it definitely will be the newest..

In addition, how can I filter the data so that the documents will be from the last two days? Do I need to add a query filter on the @timestamp field?

Answer 1

Yes, you'll need a range query to select only the last two days. As to the sorting -- you can use a ordered top_hits agg to retrieve the underlying docs:

{
  "query": {
    "range": {
      "@timestamp": {
        "gte": "now-2d"
      }
    }
  }, 
  "size": 0,
  "aggs": {
    "store_aggr": {
      "terms": {
        "field": "Store"
      },
      "aggs": {
        "owner_aggr": {
          "terms": {
            "field": "owner"
          },
          "aggs": {
            "top_hits_aggr": {
              "top_hits": {
                "sort": {
                  "@timestamp": {
                    "order": "desc"
                  }
                }
              }
            }
          }
        },
        "products_aggr": {
          "terms": {
            "field": "products"
          },
          "aggs": {
            "top_hits_aggr": {
              "top_hits": {
                "sort": {
                  "@timestamp": {
                    "order": "desc"
                  }
                }
              }
            }
          }
        }
      }
    }
  }
}