ELK Stack — Elasticsearch Querying Exercises

12 minute read

Introduction

Time to put your Elasticsearch querying skills into practice. These five exercises progress from basic searches to complex queries with aggregations. Each exercise uses the e-commerce orders sample dataset defined below.

Setup: Sample Data

Create the index and load sample data before starting:

curl -s -X PUT "localhost:9200/ecommerce-orders" -H 'Content-Type: application/json' -d '{
  "mappings": {
    "properties": {
      "order_id":      { "type": "keyword" },
      "customer_name": { "type": "text", "fields": { "raw": { "type": "keyword" } } },
      "product":       { "type": "text", "fields": { "raw": { "type": "keyword" } } },
      "category":      { "type": "keyword" },
      "price":         { "type": "float" },
      "quantity":      { "type": "integer" },
      "status":        { "type": "keyword" },
      "payment_method": { "type": "keyword" },
      "city":          { "type": "keyword" },
      "notes":         { "type": "text" },
      "@timestamp":    { "type": "date" }
    }
  }
}'
curl -s -X POST "localhost:9200/ecommerce-orders/_bulk" -H 'Content-Type: application/json' -d '
{"index":{"_id":"ord-001"}}
{"order_id":"ord-001","customer_name":"Alice Johnson","product":"Wireless Mouse","category":"electronics","price":29.99,"quantity":2,"status":"delivered","payment_method":"credit_card","city":"New York","notes":"Customer requested gift wrapping","@timestamp":"2026-03-28T10:00:00Z"}
{"index":{"_id":"ord-002"}}
{"order_id":"ord-002","customer_name":"Bob Smith","product":"Mechanical Keyboard","category":"electronics","price":89.99,"quantity":1,"status":"shipped","payment_method":"paypal","city":"San Francisco","notes":"Express shipping requested by customer","@timestamp":"2026-03-29T14:30:00Z"}
{"index":{"_id":"ord-003"}}
{"order_id":"ord-003","customer_name":"Alice Johnson","product":"USB-C Hub","category":"electronics","price":45.50,"quantity":1,"status":"delivered","payment_method":"credit_card","city":"New York","notes":"Second purchase this week","@timestamp":"2026-03-29T16:00:00Z"}
{"index":{"_id":"ord-004"}}
{"order_id":"ord-004","customer_name":"Carol Davis","product":"Running Shoes","category":"sportswear","price":120.00,"quantity":1,"status":"processing","payment_method":"debit_card","city":"Chicago","notes":"Size 9, color blue preferred","@timestamp":"2026-03-30T09:15:00Z"}
{"index":{"_id":"ord-005"}}
{"order_id":"ord-005","customer_name":"David Lee","product":"Python Programming Book","category":"books","price":35.00,"quantity":3,"status":"delivered","payment_method":"credit_card","city":"San Francisco","notes":"Bulk order for training class","@timestamp":"2026-03-30T11:00:00Z"}
{"index":{"_id":"ord-006"}}
{"order_id":"ord-006","customer_name":"Eve Wilson","product":"Yoga Mat","category":"sportswear","price":25.00,"quantity":2,"status":"cancelled","payment_method":"paypal","city":"New York","notes":"Customer cancelled due to delayed shipping","@timestamp":"2026-03-30T13:45:00Z"}
{"index":{"_id":"ord-007"}}
{"order_id":"ord-007","customer_name":"Frank Brown","product":"Laptop Stand","category":"electronics","price":55.00,"quantity":1,"status":"shipped","payment_method":"credit_card","city":"Chicago","notes":"Adjustable height model requested","@timestamp":"2026-03-31T08:00:00Z"}
{"index":{"_id":"ord-008"}}
{"order_id":"ord-008","customer_name":"Alice Johnson","product":"HDMI Cable","category":"electronics","price":12.99,"quantity":4,"status":"delivered","payment_method":"credit_card","city":"New York","notes":"Needed for office setup","@timestamp":"2026-03-31T10:30:00Z"}
{"index":{"_id":"ord-009"}}
{"order_id":"ord-009","customer_name":"Bob Smith","product":"Running Shorts","category":"sportswear","price":40.00,"quantity":2,"status":"processing","payment_method":"paypal","city":"San Francisco","notes":"Marathon training gear","@timestamp":"2026-03-31T15:00:00Z"}
{"index":{"_id":"ord-010"}}
{"order_id":"ord-010","customer_name":"Carol Davis","product":"Data Science Handbook","category":"books","price":49.99,"quantity":1,"status":"delivered","payment_method":"debit_card","city":"Chicago","notes":"Recommended by instructor for advanced course","@timestamp":"2026-04-01T09:00:00Z"}
'

Exercise 1: Basic Match Queries

Difficulty: ⭐ Beginner

Scenario: You work for an e-commerce company and need to search order notes for customer-reported issues and find specific product categories.

Requirements:

  1. Find all orders where the notes field contains the word “customer”
  2. Find all orders where the notes field contains the exact phrase “bulk order”
  3. Search for the word “shipping” across both the notes and product fields using multi_match
  4. Find all orders in the “electronics” category using a term query
  5. Find all orders with a price between 30.00 and 100.00
Show Solution

1. Match query for “customer” in notes:

curl -s -X GET "localhost:9200/ecommerce-orders/_search?pretty" -H 'Content-Type: application/json' -d '{
  "query": {
    "match": {
      "notes": "customer"
    }
  }
}'
{
  "hits": {
    "total": { "value": 3, "relation": "eq" },
    "hits": [
      { "_source": { "order_id": "ord-001", "notes": "Customer requested gift wrapping" } },
      { "_source": { "order_id": "ord-002", "notes": "Express shipping requested by customer" } },
      { "_source": { "order_id": "ord-006", "notes": "Customer cancelled due to delayed shipping" } }
    ]
  }
}

2. Match phrase for “bulk order”:

curl -s -X GET "localhost:9200/ecommerce-orders/_search?pretty" -H 'Content-Type: application/json' -d '{
  "query": {
    "match_phrase": {
      "notes": "bulk order"
    }
  }
}'
{
  "hits": {
    "total": { "value": 1, "relation": "eq" },
    "hits": [
      { "_source": { "order_id": "ord-005", "notes": "Bulk order for training class" } }
    ]
  }
}

3. Multi-match for “shipping”:

curl -s -X GET "localhost:9200/ecommerce-orders/_search?pretty" -H 'Content-Type: application/json' -d '{
  "query": {
    "multi_match": {
      "query": "shipping",
      "fields": ["notes", "product"]
    }
  }
}'
{
  "hits": {
    "total": { "value": 2, "relation": "eq" },
    "hits": [
      { "_source": { "order_id": "ord-002", "notes": "Express shipping requested by customer" } },
      { "_source": { "order_id": "ord-006", "notes": "Customer cancelled due to delayed shipping" } }
    ]
  }
}

4. Term query for “electronics”:

curl -s -X GET "localhost:9200/ecommerce-orders/_search?pretty" -H 'Content-Type: application/json' -d '{
  "query": {
    "term": {
      "category": "electronics"
    }
  }
}'
{
  "hits": {
    "total": { "value": 5, "relation": "eq" },
    "hits": [
      { "_source": { "order_id": "ord-001", "category": "electronics", "product": "Wireless Mouse" } },
      { "_source": { "order_id": "ord-002", "category": "electronics", "product": "Mechanical Keyboard" } },
      { "_source": { "order_id": "ord-003", "category": "electronics", "product": "USB-C Hub" } },
      { "_source": { "order_id": "ord-007", "category": "electronics", "product": "Laptop Stand" } },
      { "_source": { "order_id": "ord-008", "category": "electronics", "product": "HDMI Cable" } }
    ]
  }
}

5. Range query for price:

curl -s -X GET "localhost:9200/ecommerce-orders/_search?pretty" -H 'Content-Type: application/json' -d '{
  "query": {
    "range": {
      "price": {
        "gte": 30.00,
        "lte": 100.00
      }
    }
  }
}'
{
  "hits": {
    "total": { "value": 5, "relation": "eq" },
    "hits": [
      { "_source": { "order_id": "ord-002", "price": 89.99 } },
      { "_source": { "order_id": "ord-003", "price": 45.50 } },
      { "_source": { "order_id": "ord-005", "price": 35.00 } },
      { "_source": { "order_id": "ord-007", "price": 55.00 } },
      { "_source": { "order_id": "ord-009", "price": 40.00 } },
      { "_source": { "order_id": "ord-010", "price": 49.99 } }
    ]
  }
}

Exercise 2: Bool Compound Queries

Difficulty: ⭐⭐ Intermediate

Scenario: The customer support team needs a complex search to find specific orders. Build a query that combines multiple conditions.

Requirements:

Build a single bool query that finds orders matching ALL of these conditions:

  1. Category must be “electronics” OR “sportswear”
  2. Status must NOT be “cancelled”
  3. Price must be greater than 20.00
  4. Orders from the last 7 days (relative to 2026-04-01)
  5. Boost orders that contain “requested” in the notes field
Show Solution
curl -s -X GET "localhost:9200/ecommerce-orders/_search?pretty" -H 'Content-Type: application/json' -d '{
  "query": {
    "bool": {
      "filter": [
        {
          "terms": { "category": ["electronics", "sportswear"] }
        },
        {
          "range": { "price": { "gt": 20.00 } }
        },
        {
          "range": {
            "@timestamp": {
              "gte": "2026-03-25T00:00:00Z",
              "lte": "2026-04-01T23:59:59Z"
            }
          }
        }
      ],
      "must_not": [
        { "term": { "status": "cancelled" } }
      ],
      "should": [
        {
          "match": {
            "notes": {
              "query": "requested",
              "boost": 2
            }
          }
        }
      ]
    }
  }
}'
{
  "took": 3,
  "hits": {
    "total": { "value": 6, "relation": "eq" },
    "max_score": 1.8185701,
    "hits": [
      {
        "_score": 1.8185701,
        "_source": {
          "order_id": "ord-001",
          "product": "Wireless Mouse",
          "category": "electronics",
          "price": 29.99,
          "status": "delivered",
          "notes": "Customer requested gift wrapping"
        }
      },
      {
        "_score": 1.8185701,
        "_source": {
          "order_id": "ord-002",
          "product": "Mechanical Keyboard",
          "category": "electronics",
          "price": 89.99,
          "status": "shipped",
          "notes": "Express shipping requested by customer"
        }
      },
      {
        "_score": 1.5411091,
        "_source": {
          "order_id": "ord-007",
          "product": "Laptop Stand",
          "category": "electronics",
          "price": 55.00,
          "status": "shipped",
          "notes": "Adjustable height model requested"
        }
      },
      {
        "_score": 0.0,
        "_source": {
          "order_id": "ord-003",
          "product": "USB-C Hub",
          "category": "electronics",
          "price": 45.50,
          "status": "delivered"
        }
      },
      {
        "_score": 0.0,
        "_source": {
          "order_id": "ord-004",
          "product": "Running Shoes",
          "category": "sportswear",
          "price": 120.00,
          "status": "processing"
        }
      },
      {
        "_score": 0.0,
        "_source": {
          "order_id": "ord-009",
          "product": "Running Shorts",
          "category": "sportswear",
          "price": 40.00,
          "status": "processing"
        }
      }
    ]
  }
}

Explanation:

  • filter handles the category, price, and date conditions without scoring (cached)
  • must_not excludes cancelled orders
  • should boosts orders with “requested” in notes — these appear first
  • Orders without “requested” still appear (score 0.0) because filter makes should optional

Exercise 3: Aggregations

Difficulty: ⭐⭐⭐ Intermediate-Advanced

Scenario: The analytics team needs a dashboard showing order metrics. Build queries that provide the data.

Requirements:

  1. Calculate the total revenue per category (hint: you’ll need a script or quantity * price — simplify by using just price × quantity as separate aggs)
  2. Show the count of orders per status
  3. Show average price per category, sorted by average price descending
  4. Create a date histogram showing order count per day
  5. Combine them: For each category, show the count, total revenue (sum of price), and a breakdown by status
Show Solution

1. Revenue per category using a scripted metric:

curl -s -X GET "localhost:9200/ecommerce-orders/_search?pretty" -H 'Content-Type: application/json' -d '{
  "size": 0,
  "aggs": {
    "by_category": {
      "terms": { "field": "category" },
      "aggs": {
        "total_revenue": {
          "sum": {
            "script": {
              "source": "doc['"'"'price'"'"'].value * doc['"'"'quantity'"'"'].value"
            }
          }
        }
      }
    }
  }
}'
{
  "aggregations": {
    "by_category": {
      "buckets": [
        { "key": "electronics", "doc_count": 5, "total_revenue": { "value": 293.44 } },
        { "key": "sportswear", "doc_count": 3, "total_revenue": { "value": 250.00 } },
        { "key": "books", "doc_count": 2, "total_revenue": { "value": 154.99 } }
      ]
    }
  }
}

2. Order count per status:

curl -s -X GET "localhost:9200/ecommerce-orders/_search?pretty" -H 'Content-Type: application/json' -d '{
  "size": 0,
  "aggs": {
    "by_status": {
      "terms": { "field": "status" }
    }
  }
}'
{
  "aggregations": {
    "by_status": {
      "buckets": [
        { "key": "delivered", "doc_count": 5 },
        { "key": "processing", "doc_count": 2 },
        { "key": "shipped", "doc_count": 2 },
        { "key": "cancelled", "doc_count": 1 }
      ]
    }
  }
}

3. Average price per category, sorted:

curl -s -X GET "localhost:9200/ecommerce-orders/_search?pretty" -H 'Content-Type: application/json' -d '{
  "size": 0,
  "aggs": {
    "by_category": {
      "terms": {
        "field": "category",
        "order": { "avg_price": "desc" }
      },
      "aggs": {
        "avg_price": {
          "avg": { "field": "price" }
        }
      }
    }
  }
}'
{
  "aggregations": {
    "by_category": {
      "buckets": [
        { "key": "sportswear", "doc_count": 3, "avg_price": { "value": 61.67 } },
        { "key": "electronics", "doc_count": 5, "avg_price": { "value": 46.69 } },
        { "key": "books", "doc_count": 2, "avg_price": { "value": 42.50 } }
      ]
    }
  }
}

4. Date histogram — orders per day:

curl -s -X GET "localhost:9200/ecommerce-orders/_search?pretty" -H 'Content-Type: application/json' -d '{
  "size": 0,
  "aggs": {
    "orders_per_day": {
      "date_histogram": {
        "field": "@timestamp",
        "calendar_interval": "day"
      }
    }
  }
}'
{
  "aggregations": {
    "orders_per_day": {
      "buckets": [
        { "key_as_string": "2026-03-28T00:00:00.000Z", "doc_count": 1 },
        { "key_as_string": "2026-03-29T00:00:00.000Z", "doc_count": 2 },
        { "key_as_string": "2026-03-30T00:00:00.000Z", "doc_count": 3 },
        { "key_as_string": "2026-03-31T00:00:00.000Z", "doc_count": 3 },
        { "key_as_string": "2026-04-01T00:00:00.000Z", "doc_count": 1 }
      ]
    }
  }
}

5. Combined: Category → Status breakdown with revenue:

curl -s -X GET "localhost:9200/ecommerce-orders/_search?pretty" -H 'Content-Type: application/json' -d '{
  "size": 0,
  "aggs": {
    "by_category": {
      "terms": { "field": "category" },
      "aggs": {
        "total_price": {
          "sum": { "field": "price" }
        },
        "by_status": {
          "terms": { "field": "status" }
        }
      }
    }
  }
}'
{
  "aggregations": {
    "by_category": {
      "buckets": [
        {
          "key": "electronics",
          "doc_count": 5,
          "total_price": { "value": 233.47 },
          "by_status": {
            "buckets": [
              { "key": "delivered", "doc_count": 3 },
              { "key": "shipped", "doc_count": 2 }
            ]
          }
        },
        {
          "key": "sportswear",
          "doc_count": 3,
          "total_price": { "value": 185.00 },
          "by_status": {
            "buckets": [
              { "key": "cancelled", "doc_count": 1 },
              { "key": "processing", "doc_count": 2 }
            ]
          }
        },
        {
          "key": "books",
          "doc_count": 2,
          "total_price": { "value": 84.99 },
          "by_status": {
            "buckets": [
              { "key": "delivered", "doc_count": 2 }
            ]
          }
        }
      ]
    }
  }
}

Exercise 4: Sorting and Pagination with search_after

Difficulty: ⭐⭐⭐ Intermediate-Advanced

Scenario: Build a paginated API that returns 3 orders per page, sorted by timestamp descending. Use search_after to implement pagination.

Requirements:

  1. Fetch the first page (3 results), sorted by @timestamp descending with _id as tiebreaker
  2. Using the sort values from the last result, fetch the second page
  3. Fetch the third page
  4. Only return order_id, product, price, and @timestamp fields
Show Solution

Page 1:

curl -s -X GET "localhost:9200/ecommerce-orders/_search?pretty" -H 'Content-Type: application/json' -d '{
  "size": 3,
  "query": { "match_all": {} },
  "_source": ["order_id", "product", "price", "@timestamp"],
  "sort": [
    { "@timestamp": "desc" },
    { "_id": "asc" }
  ]
}'
{
  "hits": {
    "total": { "value": 10, "relation": "eq" },
    "hits": [
      {
        "_id": "ord-010",
        "sort": [1743498000000, "ord-010"],
        "_source": { "order_id": "ord-010", "product": "Data Science Handbook", "price": 49.99, "@timestamp": "2026-04-01T09:00:00Z" }
      },
      {
        "_id": "ord-009",
        "sort": [1743433200000, "ord-009"],
        "_source": { "order_id": "ord-009", "product": "Running Shorts", "price": 40.00, "@timestamp": "2026-03-31T15:00:00Z" }
      },
      {
        "_id": "ord-008",
        "sort": [1743417000000, "ord-008"],
        "_source": { "order_id": "ord-008", "product": "HDMI Cable", "price": 12.99, "@timestamp": "2026-03-31T10:30:00Z" }
      }
    ]
  }
}

Page 2 — use sort values from ord-008:

curl -s -X GET "localhost:9200/ecommerce-orders/_search?pretty" -H 'Content-Type: application/json' -d '{
  "size": 3,
  "query": { "match_all": {} },
  "_source": ["order_id", "product", "price", "@timestamp"],
  "sort": [
    { "@timestamp": "desc" },
    { "_id": "asc" }
  ],
  "search_after": [1743417000000, "ord-008"]
}'
{
  "hits": {
    "hits": [
      {
        "_id": "ord-007",
        "sort": [1743408000000, "ord-007"],
        "_source": { "order_id": "ord-007", "product": "Laptop Stand", "price": 55.00, "@timestamp": "2026-03-31T08:00:00Z" }
      },
      {
        "_id": "ord-006",
        "sort": [1743349500000, "ord-006"],
        "_source": { "order_id": "ord-006", "product": "Yoga Mat", "price": 25.00, "@timestamp": "2026-03-30T13:45:00Z" }
      },
      {
        "_id": "ord-005",
        "sort": [1743339600000, "ord-005"],
        "_source": { "order_id": "ord-005", "product": "Python Programming Book", "price": 35.00, "@timestamp": "2026-03-30T11:00:00Z" }
      }
    ]
  }
}

Page 3 — use sort values from ord-005:

curl -s -X GET "localhost:9200/ecommerce-orders/_search?pretty" -H 'Content-Type: application/json' -d '{
  "size": 3,
  "query": { "match_all": {} },
  "_source": ["order_id", "product", "price", "@timestamp"],
  "sort": [
    { "@timestamp": "desc" },
    { "_id": "asc" }
  ],
  "search_after": [1743339600000, "ord-005"]
}'
{
  "hits": {
    "hits": [
      {
        "_id": "ord-004",
        "sort": [1743333300000, "ord-004"],
        "_source": { "order_id": "ord-004", "product": "Running Shoes", "price": 120.00, "@timestamp": "2026-03-30T09:15:00Z" }
      },
      {
        "_id": "ord-003",
        "sort": [1743271200000, "ord-003"],
        "_source": { "order_id": "ord-003", "product": "USB-C Hub", "price": 45.50, "@timestamp": "2026-03-29T16:00:00Z" }
      },
      {
        "_id": "ord-002",
        "sort": [1743265800000, "ord-002"],
        "_source": { "order_id": "ord-002", "product": "Mechanical Keyboard", "price": 89.99, "@timestamp": "2026-03-29T14:30:00Z" }
      }
    ]
  }
}

Key points:

  • _id as tiebreaker ensures unique sort order
  • Each page uses the sort array from the last hit of the previous page
  • No limit on how deep you can paginate (unlike from/size)

Exercise 5: Combining Queries with Aggregations

Difficulty: ⭐⭐⭐⭐ Advanced

Scenario: Build a search dashboard query that finds relevant orders AND provides analytics facets in a single request.

Requirements:

Build a single query that:

  1. Searches for “electronics” category orders that are NOT cancelled
  2. Only includes orders from the last 5 days (relative to 2026-04-01)
  3. Returns the top 3 results sorted by price descending
  4. Returns only order_id, product, price, and status fields
  5. Includes these aggregations (scoped to the query):
    • Average price
    • Price stats (min, max, avg, sum)
    • Order count per payment method
    • Order count per day (date histogram)
Show Solution
curl -s -X GET "localhost:9200/ecommerce-orders/_search?pretty" -H 'Content-Type: application/json' -d '{
  "size": 3,
  "query": {
    "bool": {
      "filter": [
        { "term": { "category": "electronics" } },
        {
          "range": {
            "@timestamp": {
              "gte": "2026-03-27T00:00:00Z",
              "lte": "2026-04-01T23:59:59Z"
            }
          }
        }
      ],
      "must_not": [
        { "term": { "status": "cancelled" } }
      ]
    }
  },
  "sort": [
    { "price": "desc" }
  ],
  "_source": ["order_id", "product", "price", "status"],
  "aggs": {
    "avg_price": {
      "avg": { "field": "price" }
    },
    "price_stats": {
      "stats": { "field": "price" }
    },
    "by_payment_method": {
      "terms": { "field": "payment_method" }
    },
    "orders_per_day": {
      "date_histogram": {
        "field": "@timestamp",
        "calendar_interval": "day"
      }
    }
  }
}'
{
  "took": 4,
  "hits": {
    "total": { "value": 5, "relation": "eq" },
    "max_score": null,
    "hits": [
      {
        "sort": [89.99],
        "_source": { "order_id": "ord-002", "product": "Mechanical Keyboard", "price": 89.99, "status": "shipped" }
      },
      {
        "sort": [55.00],
        "_source": { "order_id": "ord-007", "product": "Laptop Stand", "price": 55.00, "status": "shipped" }
      },
      {
        "sort": [45.50],
        "_source": { "order_id": "ord-003", "product": "USB-C Hub", "price": 45.50, "status": "delivered" }
      }
    ]
  },
  "aggregations": {
    "avg_price": {
      "value": 46.694000244140625
    },
    "price_stats": {
      "count": 5,
      "min": 12.989999771118164,
      "max": 89.98999786376953,
      "avg": 46.694000244140625,
      "sum": 233.47000122070312
    },
    "by_payment_method": {
      "buckets": [
        { "key": "credit_card", "doc_count": 4 },
        { "key": "paypal", "doc_count": 1 }
      ]
    },
    "orders_per_day": {
      "buckets": [
        { "key_as_string": "2026-03-28T00:00:00.000Z", "doc_count": 1 },
        { "key_as_string": "2026-03-29T00:00:00.000Z", "doc_count": 2 },
        { "key_as_string": "2026-03-31T00:00:00.000Z", "doc_count": 2 }
      ]
    }
  }
}

Explanation:

  • filter handles category and date range without scoring (efficient, cached)
  • must_not excludes cancelled orders
  • sort by price descending, size: 3 limits returned hits
  • _source limits returned fields
  • All aggregations run on the filtered dataset (5 electronics orders, not 10 total)
  • The hits show only the top 3 by price, but aggregations reflect all 5 matching documents

What’s Next?

Once you’re comfortable with these exercises, try:

  • Creating your own queries on the web-logs index from earlier chapters
  • Combining post_filter with aggregations for faceted navigation
  • Using function_score to customize ranking
  • Experimenting with PIT + search_after for consistent pagination

Return to the Elasticsearch Querying chapter hub.