Sub-queries in KQL Kibana

I am using ELK stack to perform some analysis on my data. My index pattern has 2 types of data

  1. The ID of the product clicked
  2. The ID of the product sold

I would like to first find all the IDs sold out and based on that find the number of times that ID was clicked.
1, clicked
1, sold
2, clicked
2, clicked
2, sold
3. clicked

So in this data-> ID1 and ID2 were sold. And ID1 was clicked once and ID2 was clicked twice.

I am unable to achieve this in KQL.

You can achieve this by using filter aggregations in a visualization.
Either in Lens or even in normal Bar Chart.
You do a terms Aggregation on the product ID, with a count metric. Then you can split that aggregation with a filter one where the first filter is "action: clicked" and the second one is "action: sold".

This will give me 2 Aggregations one for 'sold' and the other one for 'clicked'.
I would like to get the Number of clicks for all the Sold Items only. When I apply a filter on 'sold', I get only sold rows. Now how can I get the data for the click? Its similar to sub-queries in SQL.

I checked many posts, and all say that Sub-Queries in ES is not possible. Moreover, if I start using "Canvas" and "SQL", it does not support all the Sub-Queries.
As of now, it seems like I will have to store all the data on a database (MySql or MongoDB) and use Grafana for Visualization.....

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