I have an index called socialmedia and trying to create queries with this field called eng (omitted some unnecessary fields)
"id" : "1",
"eng":
[
{
"soc_mm_score" : "3",
"date_updated" : "1520969306",
},
{
"soc_mm_score" : "1",
"date_updated" : "1520972191",
},
{
"soc_mm_score" : "4",
"date_updated" : "1520937222",
}
]
I have a lot of documents from this index that contains eng nested field that also contains a lot of "sub-objects"
Now, my main goal is, what Elasticsearch query should I formulate to filter out these nested objects
STEP 1
Get the nested object with the highest date_updated value
STEP 2
After getting those nested objects, perform a sum aggregation so I could add all the values of the soc_mm_score field for the corresponding "latest nested object"
I have tried this query but seems to fail
ATTEMPT # 1 (I'm using elasticsearch-php API so please trust my query that it's working with this format)
'aggs' => [
'ENG' => [
'nested' => [
'path' => 'eng'
],
'aggs' => [
'FILTER' => [
'filter' => [
'bool' => [
'must' => [
[
// I'm thinking of using max aggregation here
]
]
]
]
]
'LATEST' => [
'top_hits' => [
'size' => 1,
'sort' => [
'eng.date_updated' => [
'order' => 'desc'
]
]
]
]
]
]
]
PRO/S: it is returning the correct nested object
CON/S: I cannot perform further aggregations
Then I tried adding sub-aggregation
Is there any other ways that I can perform the desired query?