Query the last document of each type on Elasticsearch
I'm trying to run what looks like a simple query against Elasticsearch, but I just can't get the result I'm looking for.
Here's a quick example of what I'm trying to do:
I have a news base. Each news item contains a source, headline, timestamp and user.
I want the title of the latest (based on timestamp) title for every available source for a given user.
#!/bin/bash
export ELASTICSEARCH_ENDPOINT="http://localhost:9200"
# Create indexes
curl -XPUT "$ELASTICSEARCH_ENDPOINT/news" -d '{
"mappings": {
"news": {
"properties": {
"source": { "type": "string", "index": "not_analyzed" },
"headline": { "type": "object" },
"timestamp": { "type": "date", "format": "date_hour_minute_second_millis" },
"user": { "type": "string", "index": "not_analyzed" }
}
}
}
}'
# Index documents
curl -XPOST "$ELASTICSEARCH_ENDPOINT/_bulk?refresh=true" -d '
{"index":{"_index":"news","_type":"news"}}
{"user": "John", "source": "CNN", "headline": "Great news", "timestamp": "2015-07-28T00:07:29.000"}
{"index":{"_index":"news","_type":"news"}}
{"user": "John", "source": "CNN", "headline": "More great news", "timestamp": "2015-07-28T00:08:23.000"}
{"index":{"_index":"news","_type":"news"}}
{"user": "John", "source": "ESPN", "headline": "Sports news", "timestamp": "2015-07-28T00:09:32.000"}
{"index":{"_index":"news","_type":"news"}}
{"user": "John", "source": "ESPN", "headline": "More sports news", "timestamp": "2015-07-28T00:10:35.000"}
{"index":{"_index":"news","_type":"news"}}
{"user": "Mary", "source": "Yahoo", "headline": "More news", "timestamp": "2015-07-28T00:11:54.000"}
{"index":{"_index":"news","_type":"news"}}
{"user": "Mary", "source": "Yahoo", "headline": "Crazy news", "timestamp": "2015-07-28T00:12:31.000"}
'
So how can I get the latest CNN and the latest ESPN headlines from John, for example?
I've been looking into the Multiple Search API, but that would mean I would need to know all the sources in advance (CNN and ESPN in this case).
source to share
First, note that I had to change your collation for the field headline
to string
, as in the headers of your sample documents string
, not object
s.
So, a query like the following will retrieve what you expect:
curl -XPOST "$ELASTICSEARCH_ENDPOINT/news/_search" -d '{
"size": 0,
"query": {
"filtered": {
"filter": {
"term": {
"user": "John" <--- filter for user=John
}
}
}
},
"aggs": {
"sources": {
"terms": {
"field": "source" <--- aggregate by source
},
"aggs": {
"latest": {
"top_hits": {
"size": 1, <--- only take the first...
"_source": [ <--- only the date and headline
"headline",
"timestamp"
],
"sort": {
"timestamp": "desc" <--- ...and only the latest hit
}
}
}
}
}
}
}'
This will give something like this:
{
...
"aggregations" : {
"sources" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [ {
"key" : "CNN",
"doc_count" : 2,
"latest" : {
"hits" : {
"total" : 2,
"max_score" : null,
"hits" : [ {
"_index" : "news",
"_type" : "news",
"_id" : "AU7Sh3VDGDddn2ZNuDVl",
"_score" : null,
"_source":{
"headline": "More great news",
"timestamp": "2015-07-28T00:08:23.000"
},
"sort" : [ 1438042103000 ]
} ]
}
}
}, {
"key" : "ESPN",
"doc_count" : 2,
"latest" : {
"hits" : {
"total" : 2,
"max_score" : null,
"hits" : [ {
"_index" : "news",
"_type" : "news",
"_id" : "AU7Sh3VDGDddn2ZNuDVn",
"_score" : null,
"_source":{
"headline": "More sports news",
"timestamp": "2015-07-28T00:10:35.000"
},
"sort" : [ 1438042235000 ]
} ]
}
}
} ]
}
}
}
source to share