These final problems combine the earlier lessons rather than introducing unrestricted new DSL. Write a short logical formula before JSON. Mark the shared restrictions, the alternative branches, the scored text and the scope of each metric. This makes errors visible before testing a long query.
Validate a result in layers: syntax, full matching population, order or aggregation values, then required semantic context. A page of plausible documents does not prove a query is correct. Pay particular attention to exact price boundaries, missing ratings, equal sort values and the difference between parent counts and locally filtered counts.
- Shared query restrictions
- Search branch A OR branch B
- Groups
- Local eligibility filter
- Metrics for eligible documents
- Local eligibility filter
Global restrictions apply to the whole tree. Alternative branches and local analytics keep their own scopes.
Examples use the public products dataset on Elasticsearch 9.4.3. Responses are shortened JSON fragments: timing, shard and index metadata are omitted. Numeric scores are illustrative and can vary. Examples teach the method; exercise values may differ.
Complex business rules
Start with the formula S AND (A OR B). S is shared eligibility: stock and permitted statuses. Each branch combines its own category with its own price interval. In this example S uses only preorder status, A is Accessories below 200 and B is Smartphones from 200 inclusive to 900 exclusive.
Place the shared restrictions in the outer filter, the two branches in outer should and minimum_should_match: 1 at that same outer level. Within each branch use a nested bool.filter for category AND price. Flattening all four branch leaves into one should lets a cheap product from the wrong category pass.
Test the lower boundary, upper boundary, a document satisfying only category, a document satisfying only price and an out-of-stock document satisfying a full branch. These cases distinguish a correctly grouped expression from an accidentally broad query. Apply the same reasoning with the different values specified in the practical task.
| Shared S | Branch A | Branch B | Accept? |
|---|---|---|---|
| false | true | true | No |
| true | false | false | No |
| true | true | false | Yes |
| true | false | true | Yes |
Try a different scenario
{
"query": {
"bool": {
"filter": [
{
"term": {
"in_stock": true
}
},
{
"term": {
"status": "preorder"
}
}
],
"should": [
{
"bool": {
"filter": [
{
"term": {
"category": "Accessories"
}
},
{
"range": {
"price": {
"lt": 200
}
}
}
]
}
},
{
"bool": {
"filter": [
{
"term": {
"category": "Smartphones"
}
},
{
"range": {
"price": {
"gte": 200,
"lt": 900
}
}
}
]
}
}
],
"minimum_should_match": 1
}
},
"size": 2,
"_source": [
"name",
"category",
"price",
"status",
"in_stock"
],
"sort": [
{
"id": "asc"
}
]
}Elasticsearch response
{
"hits": {
"total": {
"value": 15,
"relation": "eq"
},
"hits": [
{
"_id": "4",
"_score": null,
"_source": {
"name": "Logitech Gaming Keyboard 4",
"category": "Accessories",
"price": 199,
"in_stock": true,
"status": "preorder"
}
},
{
"_id": "25",
"_score": null,
"_source": {
"name": "Apple USB Cable 25",
"category": "Smartphones",
"price": 499,
"in_stock": true,
"status": "preorder"
}
}
]
}
}How to read this result
Each hit passes the shared restrictions and one complete category-price branch. A condition from branch A cannot be paired with an unrelated condition from branch B.
Relevance under constraints
Separate three responsibilities: must describes text relevance, filter describes eligibility, and must_not describes exclusions. Our example searches travel using name^3 and description, requires stock and price below 800, then excludes preorder. Keep multi_match in the scored must path rather than inside a local or outer filter.
Do not add a field sort when the goal is a relevance ranking. A boost placed on an unrelated clause or inside an aggregation does not give the actual text query the intended weighting. Inspect the complete matching set and the relative order of documents rather than hardcoding the numeric score of one hit.
Try a different scenario
{
"query": {
"bool": {
"must": [
{
"multi_match": {
"query": "travel",
"fields": [
"name^3",
"description"
]
}
}
],
"filter": [
{
"term": {
"in_stock": true
}
},
{
"range": {
"price": {
"lt": 800
}
}
}
],
"must_not": [
{
"term": {
"status": "preorder"
}
}
]
}
},
"size": 2,
"_source": [
"name",
"price",
"status",
"in_stock"
]
}Elasticsearch response
{
"hits": {
"total": {
"value": 34,
"relation": "eq"
},
"hits": [
{
"_id": "23",
"_score": 4.8997154,
"_source": {
"name": "Dell Travel Speaker 23",
"price": 100,
"in_stock": true,
"status": "discontinued"
}
},
{
"_id": "53",
"_score": 4.8997154,
"_source": {
"name": "Dell Travel Speaker 53",
"price": 100,
"in_stock": true,
"status": "discontinued"
}
}
]
}
}How to read this result
The text query determines relative ranking among eligible, non-excluded products. Stock, price and exclusions determine membership, not a relevance bonus.
A catalog page for an API
An API contract can require matching, ordering, pagination and projection at once. First restrict stock and indexed rating and exclude preorder. Then sort rating descending, price ascending, id ascending. Finally select page two of three items and only name, price and rating in _source. Each step is independently meaningful.
Filtering rating with exists before sorting removes missing and null values from the population instead of relying on default missing-value sort placement. The unique final id makes ties in rating and price reproducible. An implementation can return the right IDs yet still violate the contract by including an extra source property.
Try a different scenario
{
"query": {
"bool": {
"filter": [
{
"term": {
"in_stock": true
}
},
{
"exists": {
"field": "rating"
}
}
],
"must_not": [
{
"term": {
"status": "preorder"
}
}
]
}
},
"size": 3,
"_source": [
"name",
"price",
"rating"
],
"sort": [
{
"rating": "desc"
},
{
"price": "asc"
},
{
"id": "asc"
}
],
"from": 3
}Elasticsearch response
{
"hits": {
"total": {
"value": 86,
"relation": "eq"
},
"hits": [
{
"_id": "299",
"_score": null,
"_source": {
"name": "Dell Premium Travel Laptop 299",
"price": 1299,
"rating": 4.9
}
},
{
"_id": "38",
"_score": null,
"_source": {
"name": "Samsung Headphones Premium 38",
"price": 1000,
"rating": 4.8
}
},
{
"_id": "158",
"_score": null,
"_source": {
"name": "Samsung Headphones Premium 158",
"price": 1000,
"rating": 4.8
}
}
]
}
}How to read this result
Read total, the three ordered IDs and the exact source keys separately. Their combination defines this page contract; changing size or from changes the requested page.
Category and brand analytics
Use three levels: category buckets, then brand buckets, then numeric metric leaves. Our example analyzes active products, groups them by category and brand.keyword and computes three price metrics per pair. All metrics under a brand inherit both the category restriction and the brand restriction.
The query is outside aggs and restricts all groups. Each terms size is local to that level. At sizes 10 and 20, the conservative bucket estimate is 10 + 10 × 20 = 210, within the trainer budget of 1000. The metric leaves occupy the third level but do not multiply the number of buckets.
Read the response as categories.buckets → brands.buckets → metric.value. Never combine the metrics of different category-brand pairs unless the business question explicitly asks for a new calculation. A mean of group means is generally not the same as the overall document mean.
- query: active
- categories (level 1)
- brands (level 2)
- avg / min / max (level 3)
- brands (level 2)
- categories (level 1)
Each indentation level narrows the document population before the metrics are calculated.
Try a different scenario
{
"size": 0,
"query": {
"term": {
"status": "active"
}
},
"aggs": {
"categories": {
"terms": {
"field": "category",
"size": 10
},
"aggs": {
"brands": {
"terms": {
"field": "brand.keyword",
"size": 20
},
"aggs": {
"average_price": {
"avg": {
"field": "price"
}
},
"min_price": {
"min": {
"field": "price"
}
},
"max_price": {
"max": {
"field": "price"
}
}
}
}
}
}
}
}Elasticsearch response
{
"hits": {
"total": {
"value": 100,
"relation": "eq"
},
"hits": []
},
"aggregations": {
"categories": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": "Accessories",
"doc_count": 25,
"brands": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": "apple",
"doc_count": 25,
"average_price": {
"value": 859.4
},
"min_price": {
"value": 99
},
"max_price": {
"value": 2499
}
}
]
}
},
{
"key": "Audio",
"doc_count": 25,
"brands": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": "apple",
"doc_count": 25,
"average_price": {
"value": 859.4
},
"min_price": {
"value": 99
},
"max_price": {
"value": 2499
}
}
]
}
},
{
"key": "Computers",
"doc_count": 25,
"brands": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": "Sony",
"doc_count": 25,
"average_price": {
"value": 589.2
},
"min_price": {
"value": 49
},
"max_price": {
"value": 1299
}
}
]
}
},
{
"key": "Smartphones",
"doc_count": 25,
"brands": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": "Sony",
"doc_count": 25,
"average_price": {
"value": 589.2
},
"min_price": {
"value": 49
},
"max_price": {
"value": 1299
}
}
]
}
}
]
}
}
}How to read this result
Every brand bucket is inside one category. Its metrics summarize only active products in that pair, even if the same brand appears elsewhere in the response.
Conditional metrics within groups
Keep parent counts and local eligibility separate. The top-level query selects statuses active or preorder in this example. A brands bucket counts every matching document of that brand. Its eligible child then requires stock AND (rating at least 4.5 OR no indexed rating). The inner minimum_should_match makes the rating alternatives mandatory.
Write missing rating as bool.must_not containing exists. Keep stock outside the rating alternatives, so an unrated but unavailable item does not qualify. The same missing-value logic treats null as unindexed in our fixed mapping. A zero rating exists and fails the threshold; it must not pass the missing branch.
Place average_price, min_price and max_price inside eligible.aggs. If a brand has active or preorder products but none are eligible, its parent bucket remains with a positive doc_count, eligible.doc_count is zero, and all three metrics are null. Moving the eligibility filter into query would erase this distinction and answer a different question.
Try a different scenario
{
"size": 0,
"query": {
"terms": {
"status": [
"active",
"preorder"
]
}
},
"aggs": {
"brands": {
"terms": {
"field": "brand.keyword",
"size": 20
},
"aggs": {
"eligible": {
"filter": {
"bool": {
"filter": [
{
"term": {
"in_stock": true
}
}
],
"should": [
{
"range": {
"rating": {
"gte": 4.5
}
}
},
{
"bool": {
"must_not": [
{
"exists": {
"field": "rating"
}
}
]
}
}
],
"minimum_should_match": 1
}
},
"aggs": {
"average_price": {
"avg": {
"field": "price"
}
},
"min_price": {
"min": {
"field": "price"
}
},
"max_price": {
"max": {
"field": "price"
}
}
}
}
}
}
}
}Elasticsearch response
{
"hits": {
"total": {
"value": 200,
"relation": "eq"
},
"hits": []
},
"aggregations": {
"brands": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": "Apple",
"doc_count": 50,
"eligible": {
"doc_count": 19,
"min_price": {
"value": 49
},
"average_price": {
"value": 864.8421052631579
},
"max_price": {
"value": 1299
}
}
},
{
"key": "Logitech",
"doc_count": 50,
"eligible": {
"doc_count": 15,
"min_price": {
"value": 99
},
"average_price": {
"value": 919.7333333333333
},
"max_price": {
"value": 2499
}
}
},
{
"key": "Sony",
"doc_count": 50,
"eligible": {
"doc_count": 0,
"min_price": {
"value": null
},
"average_price": {
"value": null
},
"max_price": {
"value": null
}
}
},
{
"key": "apple",
"doc_count": 50,
"eligible": {
"doc_count": 0,
"min_price": {
"value": null
},
"average_price": {
"value": null
},
"max_price": {
"value": null
}
}
}
]
}
}
}How to read this result
Compare each brand doc_count with eligible.doc_count. They can differ. In this fixed dataset some brand buckets have no eligible products, showing the null metric case explicitly.
Key takeaways
- Write the logical formula before the query tree.
- Shared filters belong outside alternative branches.
- Scored multi_match belongs in must, outside filter paths.
- Validate set, ordering, page and source projection separately.
- Parent counts precede local filters; empty child metrics are null.
Official reference
Ready to put this into practice?
Confirm reading to save your place. Reading and exercise progress are tracked separately in this browser using your guest cookie.