Aggregation Data Tables

🏡 Home 📖 Chapter Home 👉 Next
⚡  ElasticsearchBook.com is crafted by Jozef Sorocin and powered by:

Simple Tables

As touched upon in the introduction to 3. Table & Charts, pagination requests are highly useful for building simple tables, lists, grids, and galleries.
In order to remain demand-oriented, you should aim to limit the response hits' content to only those attributes that are actually needed to populate said components. As such, you can specify, via the includes parameter, which fields should be returned. Analogously, you can control which ones can be skipped via the excludes parameter. Wildcards are supported too:
POST index_name/_search
{
  "size": 24,
  "from": 24,
  "query": { ... },
  "_source": {
    "includes": ["id", "price", "photos"],
    "excludes": ["irrelevant_field", "internal_*"]
  }
}
The returned hits would then function as the table rows, their attributes as the individual cells.
It's also possible to post-process an attribute's value so that it can be, say, rendered in the frontend without any further manipulation — think date formatting, applying a discount, or simply transforming a boolean into an emoji (as seen in Retool):
POST my_index/_search
{
  ...
  "script_fields": {
    "emoji_booleans": {
      "script": {
        "source": "doc['boolean_field'].value == true ? \"✅\" : \"❌\"" 
      }
    }
  }
} 
For further script_fields applications see Script Fields & Debugging.

Advanced Reporting Tables

Let's imagine a typical e-commerce scenario — once the paginated & faceted e-shop is running, management is going to ask for:
  • sums & totals,
  • averages & medians,
  • click-through rates & conversion ratios,
  • and other aggregate metrics.

Use Case: Price Range Metrics

Management would like to know
  • how many products were sold in the $0 < x ≤ $25, $25 < x ≤ $100 and >$100 ranges
  • and how much revenue was generated in each of those categories
given that your documents are structured as:
POST products/_doc
{
  "category": "phone_case",
  "price": 20,
  "status": "sold"
}

POST products/_doc
{
  "category": "powerbank",
  "price": 99,
  "status": "sold"
}
The desired table should look like this:
Price Range Metrics Table
Range in USD
Sold Count
Revenue in USD
$0 to $25
120
$2,560
$26 to $100
50
$4,100
$101+
30
$6,750

Approach

We'll capitalize on the fact that a single Elasticsearch _search request supports multiple bucket aggregations, any of which can have an arbitrary number sub-aggregations. *
 
Since all rows share the sold filter, they can be wrapped in a top-level filter of their own.
 
Each row (→ range) can function as a bucket encompassed within a filter aggregation declaring a range query,

Already purchased? Sign in here.