Hi,
I have an index with two fields; ID and Status. Each ID will have 3 statuses; start, pending and end, which are included in the same index. What I want to achieve is to get the last status for each ID from the index so that I can use it with a Vega visualization to create a pie-chart.
I'm trying to collapse search results based on field values. Here's the API call I tried on my index.
GET /demo*/_search
{
"collapse": {
"field": "ID.keyword",
"inner_hits": {
"name": "by_status",
"collapse": {
"field": "Status.keyword"
},
"sort": [
{
"Status.keyword": {
"order": "asc"
}
}
],
"size": 1
}
},
"sort": [
{
"ID.keyword": {
"order": "asc"
}
}
]
}
It delivers the need to get the last status for each ID. Here's the response from Elasticsearch.
{
"took" : 5,
"timed_out" : false,
"_shards" : {
"total" : 2,
"successful" : 2,
"skipped" : 0,
"failed" : 0
},
"hits" : {
"total" : {
"value" : 16,
"relation" : "eq"
},
"max_score" : null,
"hits" : [
{
"_index" : "demo-index",
"_type" : "_doc",
"_id" : "0b2JR3YBsgdxC8Q2vhg5",
"_score" : null,
"_source" : {
"message" : "1,start",
"host" : "host-computer",
"ID" : "1",
"@timestamp" : "2020-12-09T12:46:16.532Z",
"Status" : "start",
"path" : "/path/to/the/data/file.csv",
"@version" : "1"
},
"fields" : {
"ID.keyword" : [
"1"
]
},
"sort" : [
"1"
],
"inner_hits" : {
"by_status" : {
"hits" : {
"total" : {
"value" : 3,
"relation" : "eq"
},
"max_score" : null,
"hits" : [
{
"_index" : "demo-index",
"_type" : "_doc",
"_id" : "2L2JR3YBsgdxC8Q2whgP",
"_score" : null,
"_source" : {
"message" : "1,end",
"host" : "host-computer",
"ID" : "1",
"@timestamp" : "2020-12-09T12:46:16.586Z",
"Status" : "end",
"path" : "/path/to/the/data/file.csv",
"@version" : "1"
},
"fields" : {
"Status.keyword" : [
"end"
]
},
"sort" : [
"end"
]
}
]
}
}
}
},
{
"_index" : "demo-index",
"_type" : "_doc",
"_id" : "0r2JR3YBsgdxC8Q2vhjG",
"_score" : null,
"_source" : {
"message" : "2,start",
"host" : "host-computer",
"ID" : "2",
"@timestamp" : "2020-12-09T12:46:16.588Z",
"Status" : "start",
"path" : "/path/to/the/data/file.csv",
"@version" : "1"
},
"fields" : {
"ID.keyword" : [
"2"
]
},
"sort" : [
"2"
],
"inner_hits" : {
"by_status" : {
"hits" : {
"total" : {
"value" : 2,
"relation" : "eq"
},
"max_score" : null,
"hits" : [
{
"_index" : "demo-index",
"_type" : "_doc",
"_id" : "072JR3YBsgdxC8Q2vhjG",
"_score" : null,
"_source" : {
"message" : "2,end",
"host" : "host-computer",
"ID" : "2",
"@timestamp" : "2020-12-09T12:46:16.588Z",
"Status" : "end",
"path" : "/path/to/the/data/file.csv",
"@version" : "1"
},
"fields" : {
"Status.keyword" : [
"end"
]
},
"sort" : [
"end"
]
}
]
}
}
}
},
{
"_index" : "demo-index",
"_type" : "_doc",
"_id" : "1L2JR3YBsgdxC8Q2vhjG",
"_score" : null,
"_source" : {
"message" : "3,start",
"host" : "host-computer",
"ID" : "3",
"@timestamp" : "2020-12-09T12:46:16.589Z",
"Status" : "start",
"path" : "/path/to/the/data/file.csv",
"@version" : "1"
},
"fields" : {
"ID.keyword" : [
"3"
]
},
"sort" : [
"3"
],
"inner_hits" : {
"by_status" : {
"hits" : {
"total" : {
"value" : 3,
"relation" : "eq"
},
"max_score" : null,
"hits" : [
{
"_index" : "demo-index",
"_type" : "_doc",
"_id" : "1r2JR3YBsgdxC8Q2vhjG",
"_score" : null,
"_source" : {
"message" : "3,end",
"host" : "host-computer",
"ID" : "3",
"@timestamp" : "2020-12-09T12:46:16.590Z",
"Status" : "end",
"path" : "/path/to/the/data/file.csv",
"@version" : "1"
},
"fields" : {
"Status.keyword" : [
"end"
]
},
"sort" : [
"end"
]
}
]
}
}
}
}
]
}
}
I tried using the same collapse in Vega as the body of the data field as shown below.
"data": [
{
"name": "table",
"url": {
"%context%": true,
"%timefield%": "@timestamp",
"index": "demo*",
"body": {
"collapse": {
"field": "ID.keyword",
"inner_hits": {
"name": "by_location",
"collapse": {"field": "Status.keyword"},
"sort": [{"Status.keyword": {"order": "asc"}}],
"size": 1
}
},
"sort": [{"ID.keyword": {"order": "asc"}}]
}
},
"format": {
"property": "hits.hits.inner_hits.by_status.hits.hits.fields"
},
"transform": [{"type": "pie", "field": "Status.keyword"}]
}
]
But it produces an error.
What am I doing wrong? Is using a collapse search in Vega not possible? Is there any alternative way for achieving this requirement in Vega or any other type of visualization?
Any help would be greatly appreciated. 
Cheers!
