Friday, 15 November 2019

(Elasticsearch) How to get the last element of a nested field of all documents then perform sub-aggregations

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

Sample Output
Output 1

Then I tried adding sub-aggregation
Output 2

Then this is the output Output 3

Is there any other ways that I can perform this?

To review my ideal steps:

  1. Access my eng nested field
  2. Target / Focus on the eng nested field
  3. Get the "latest" / most recent element for that eng nested field (indicated by the highest value of its sibling date_updated field)
  4. Now, after getting those "most recent" nested elements, make sub-aggregations for its sibling nested fields, for example: getting the sum of the soc_like_count or soc_share_count of all the most recent element of the eng field


from (Elasticsearch) How to get the last element of a nested field of all documents then perform sub-aggregations

No comments:

Post a Comment