Help with aggregations

(Sérgio) #1

Hello,

I'm trying to build a query to get something like:

SELECT * FROM 'my_index' WHERE field1 = 'value1' GROUP BY 'field2' ORDER BY 'timestamp' DESC LIMIT 1

Is this possible to do in Elasticsearch? I have tried to use sub-aggregations, but I can't seem to find a way to get the documents in the buckets..

Thanks in advance

(David Pilato) #2

What did you do so far?

Could you provide a full recreation script as described in

It will help to better understand what you are doing.
Please, try to keep the example as simple as possible.

(Sérgio) #3

I was trying to build from here:

   GET /my_index/_search
   query: {
        match: {
            'foo': 'bar'
        }
   },  
   aggs: {
       'agg1': {
           terms: {
               field: 'some_field.keyword'
           }
       }
   }

But I didn't get anywhere. Because this way the results I get are from all documents that match 'foo' = 'bar', an array of buckets for each value of 'some_field'. And the buckets only give me the possible values of 'some_field' and the document count. For each bucket I want to get the whole document with the most recent timestamp, i.e., order by timestamp desc, and limiting to 1 document per possible value of 'some_field'.
Is aggregations the best way to get this? It seems I only get the document count with the buckets. Is there any other approach that might be better to do this?

(David Pilato) #4

Please format your code using </> icon as explained in this guide. It will make your post more readable.

Or use markdown style like:

```
CODE
```

May be Top Hits would help: https://www.elastic.co/guide/en/elasticsearch/reference/6.0/search-aggregations-metrics-top-hits-aggregation.html

(system) closed #5

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