Sort Results by category and occurrences in other indexes

Good morning,

I am new to ElasticSearch, and have the basics of querying, sorting, etc.
working with some test data.

I have a concept that I would like to solve, but am not really sure how to
do it, so I thought I would post here and see if you all can help me figure
this out.

I have the following indexes (note, not all fields in the indexes are

Categories Index (we have approximately 4 main category groupings, and 94
different sub-category groupings)

  • Id (int, identifies the category)
  • Heading (character varying, heading for the category)
  • ParentId (int, set to zero if a main category item, otherwise set to
    which item it is a subcategory item of)

Products Index (approximately 60,000 in test elasticsearch, with a 3 node

  • Sku (character varying, identifies the product)
  • Level1CatId (int, identifies which main category the product belongs to)
  • Level1CatHeading (character varying, main category heading)
  • Level2CatId (int, identfies which sub-category the product belongs to)
  • Level2CatHeading (character varying, sub-category heading)

LogEvents Index

  • DateTime (date, date and time the activity occurred)
  • Type (character varying, can be either 'OfferClickThrough',
    'SearchResultClickThrough', 'AdClickThrough' amongst others like 'Login',
    'Logout', 'Errors')
  • Sku (product identifier)
  • SubCategoryId (sub categoryId the part belongs to)

What I would like to do is create results that look like:

Results {
Category {
Product {
Product {
Category {

where, in English, it works like:

1.) Results are first split into categories.

2.) Within each category, products should first be sorted by sku based on
the the most recent LogEvents Sku with types of 'OfferClickThrough',
'SearchResultsClickThrough', and 'AdClickThrough'). After that, products
should be randomized so the same items are not always presented the same
every time the query has been run.

3.) Category groupings in the Results should first be sorted by the most
recent LogEvents SubCategoryId with the types of 'OfferClickThrough',
'SearchResultsClickThrough', and 'AdClickThrough' . After that, the
remaining categories should be randomized so they do not always come back
in the same order every time the query has been run.

Is this doable with ElasticSearch?



You received this message because you are subscribed to the Google Groups "elasticsearch" group.
To unsubscribe from this group and stop receiving emails from it, send an email to
To view this discussion on the web visit
For more options, visit