Unique filter in Data Table visualisation

Elastic Stack Kibana
1

Hello

Given the following set of documents:

"eventType":"Added",
"movieId":1,
"user":"Jane.Blogs@gmail.com"
"name":"Terminator",
"userId":1,
"eventDate":"01-01-2020"

"eventType":"Viewed",
"movieId":1,
"user":"Jane.Blogs@gmail.com",
"name":"Terminator",
"userId":1,
"eventDate":"02-01-2020"

"eventType":"Viewed",
"movieId":1,
"user":"Jane.Blogs@gmail.com",
"name":"Terminator",
"userId":1,
"eventDate":"03-01-2020"

I am trying to create a Data Table visualisation that will have 2 metrics aggregated by email address as in the following example:

............email ................... added viewed
Jane.Blogs@gmail.com ........1 .......... 1

So the count for a viewed should be distinct/unique for a movie.

I can easily create aggregation to count all views but have not being able to figure out how to aggregate the distinct values in viewed column.

Any help will be greatly appreciated

2

It looks like your Added and Viewed columns have value of the movie ID. But in the data, "added" is a different event type than "viewed", and a document won't have both values. The aggregation works by making groups and splitting the groups into rows, so "added" and "viewed" will be in different rows.

Would this work?

You could also show movie ID using a "top hits" metric aggregation instead of a terms aggregation.

image
image2828×1670 386 KB

3

Thank you Tim for your answer

The business requirements are as outlined in my question, I cannot have email address replicated in the rows. Movie id is not required in the output.

I think I will try to refine the query to aggregate data or use painless query in json input to filter data.

4

I'm sorry - it wasn't clear what the 1 value is in your example table, and I thought it referred to the movieId.

Is it supposed to be a document count? That wouldn't add up because your example data has a count of 2 for the Viewed event types.

5

Thank you Tim

Yes I can easily get counts, the problem is getting unique count for a specific eventType in the same row of data.

I thought there may be some standard way to accomplish this, I will try with Lucene query and/or painless on json data

6

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