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?

A reminder that technical writing is an expectation here. "U" is not an English shorthand for "You", except perhaps for chatting with your friends on WhatsApp. Please use real words (and a spell checker). — halfer
– halfer, Commented Aug 9, 2020 at 18:00

Jozef - Spatialized.io · Accepted Answer · 2020-08-02 15:52:20Z

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"
                  }
                }
              }
            }
          }
        }
      }
    }
  }
}

answered Aug 2, 2020 at 15:52

Jozef - Spatialized.io

17k4 gold badges29 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

JeyJ Over a year ago

second time that u save the day. To be honest, I wrote most of the query by myself already (after a few hours of trying ) :) . However, you helped here again and I took the range query which I didnt know how to use :) Thank u very much !!!!!!

Collectives™ on Stack Overflow

Elasticsearch api - order aggregation by @timestamp

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related