elasticsearch group-by multiple fields

Question

I am Looking for the best way to group data in elasticsearch. Elasticsearch doesn't support something like 'group by' in sql.

Lets say I have 1k categories and millions of products. What do you think is the best way to render a complete category tree? Off course you need some metadata (icon, link-target, seo-titles,...) and custom sorting for the categories.

Using Aggregations: Example: https://found.no/play/gist/8124563 Looks usable if you have to group by one field, and need some extra fields.
Using multiple Fields in a Facet (won't work): Example: https://found.no/play/gist/1aa44e2114975384a7c2 Here we lose the relationship between the different fields.
Building funny Facets: https://found.no/play/gist/8124810

For example, building a category tree using these 3 "solutions" sucks. Solution 1 May work (ES 1 isn't stable right now) Solution 2 Doesn't work Solution 3 Is a pain because it feels ugly, you need to prepare a lot of data and the facets blow up.

Maybe an alternative could be not to store any category data in ES, just the id https://found.no/play/gist/a53e46c91e2bf077f2e1

Then you could get the associated category from another system, like redis, memcache or the database.

This would end up in clean code, but the performance could become a problem. For example loading, 1k Categories from Memcache / Redis / a database could be slow. Another problem is that syncing 2 database is harder than syncing one.

How do you deal with such problems?

I am sorry for the links, but I can't post more than 2 in one article.

user_4412026 · Answer

You can use Composite Aggregation query as follows. This type of query also paginates the results if the number of buckets exceeds from the normal value of ES. By using the field 'after' you can access the rest of buckets:

"aggs": {
    "my_buckets": {
      "composite": {
        "sources": [
          {
            "field1": {
              "terms": {
                "field": "field1"
              }
            }
          },
          {
            "field2": {
              "terms": {
                "field": "field2"
              }
            }
          },
         {
            "field3": {
              "terms": {
                "field": "field3"
              }
            }
          },
        ]
      }
    }
  }

You can find more detail in ES page bucket-composite-aggregation.

elasticsearch group-by multiple fields

4 Answers