Correlated Queries for Aggregation

Elastic Stack Kibana
1

Hi,

I have a use case for writing an aggregation which if written in SQL can be achieved using correlated queries.

I have a index called listings where the properties/columns are ListDate, ListPrice, SoldDate, SoldPrice, OffMarketDate.

ListDate is not nullable, but SoldDate,SoldPrice, OffMarketDate can be nullable.

I want to aggregate stats from the above index based on the following requirement.

  1. I want to have monthly stats, which I see can be achieved by DateHistogramAggregation
  2. For each month from the DateHistogramAggregation, I want to find the listings as follows:
    Example: For Jan 2019, get all the listings where (ListDate< Feb 1st, 2019) and (SoldDateis null or SoldDate<Jan 1st, 2019) and (OffMarketDateis null or OffMarketDate< Jan 1st, 2019)

Then run the aggregation function for those lists each month.

I appreciate any suggestions to implement this use case. Thanks in advance for the help.

2

Hi @amrit.khatri. If I understand correctly, it looks like you'll want to use a bucket aggregation similar to this example in our documentation.

You should be able to do this out of the box in most Kibana visualizations. The TSVB visualization can do even more advanced calculations.

Let us know if you have more questions.

3

Thanks Nickpeihl for the reply. I am looking something similar, but one extra thing that I am looking for each that for each month, I want to re-query the listings to grab data based on #2 logic in my original post, and based on that new bucket, I want the aggregation result.

The example you pointed is working on the datahistogramaggregation bucket, that is the bucket is created based on the date. My use case need further query based on the date, and then only do aggregation.

I hope I am making some sense in my explanation.

4

Hi @amrit.khatri. Thanks for clarifying. I wonder if you can specify the addtional queries in #2 by splitting the buckets using a Filters aggregation? Filters aggregations are also supported in Kibana visualizations.

5

Thanks @nickpeihl for the reply. Some questions about the Filters aggregation:

  1. Will filter aggregation filter only the listings that falls under a given month. For example, for bucket Jan 2019 by Listdate, is further business logic filter applies only within the bucket listings or as a whole overall. The reason I am asking is that our usecase requires to span through each month and add business logic for each month for the aggregation.
  2. How can I access the date value from the parent aggregation to inject into the child aggregation logic?
  3. The ranges of date is dynamic. We are making call from .Net NestAPI which will have dynamic range of dates, and best on that ranges, the returned aggregations should span through all the dates?

Hope I am making sense.
Thanks

6

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.