Unique filter in Data Table visualisation

[ravnica1]

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

[tsullivan (Tim Sullivan)]

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?

• First bucket: Aggregate the documents by user terms:
  
  
• Second bucket: Sub-aggregate the users by filtering the for the different types of event types
  
  
• Third bucket: Reveal the MovieID
  
  

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

[ravnica1]

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.

[tsullivan (Tim Sullivan)]

@ravnica1

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

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.

[ravnica1]

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