Filter by histogram bucket key

[tomisu (Tomás Tarragón)]

Hello, everyone!

Setup

ElasticSearch 5.4

I have 2 types of document: Contact, and Message. Both with a created_on field.
Message's parent is Contact.

Goal

I'm trying to make a date histogram with 2 values nested in each bucket: total of active users, and how many of them are returning users.

• An active user would be a user with a message in the interval.
• A returning user would be a user with a message in the interval, and a message before the interval.

Problem

I'd like to add a filter bucket to each of the histogram buckets, but that filter would need to access the interval's key (the date).

I can't seem to get this right. I've tried using scripts, but I can't access the key.

I've also tried to do the intervals manually, but it doesn't seem to be a very elegant an efficient solution.

_______________________________

Any suggestions?

Thank you in advance!

[thiago (Thiago Souza)]

You should be able to solve this problem with Filter Aggregation

[jminuscula (Jacobo)]

Thanks for the suggestion @thiago, but how would you reference the bucket key inside the nested filter aggregation? That's the key piece we're missing.

Cheers!

[thiago (Thiago Souza)]

You don't apply the filter on the key of the buckets, instead you would apply a range filter aggregation (nested inside a date histogram) on the timestamp field itself and the resulting date histogram will include only the relevant dates.

[tomisu (Tomás Tarragón)]

Thanks for your response!

However, I still don't understand how to do this. I can nest a range filter aggregation inside the date histogram, yes, but how can I get the values of each histogram bucket start time?

Maybe an example can help. This is a query for Contact. Inside returning, I'd like to filter the contacts in each interval that have a message before the interval.

How could I fill lte to reference the interval start?

'aggs': {
    'by_interval': {
        'date_histogram': {
            'time_zone': 'UTC',
            'field': 'created_on',
            'interval': 'day',
            'min_doc_count': 0,
            'extended_bounds': {
                'min': 1505858400,
                'max': 1506410687
            }
        },
        'aggs': {
            'returning': {
                'filter': {
                    'has_child': {
                        'query': {
                            'range': {
                                'created_on': {
                                    'lte': ???
                                }
                            }
                        },
                        'type': 'message'
                    }
                }
            },
        }
    }
}

Thank you very much for your time!