For exact filters like event id, scoring does not matter.
What Works Where
| Need | Best OpenSearch Feature |
|---|---|
| Exact event filter | term on event.keyword |
| Search by name text | match on name |
| Sort/group by string | keyword field |
| Sort by amount | numeric field like double |
| Sort by tier | numeric rank field like tierRank |
| Get impacted resource rows | normal search hits |
| Paginate impacted resources | from + size |
| Group customers | nested + terms aggregation |
| Sort customer groups by bill amount | terms + max(customer.billAmount) |
| Sort customer groups by tier | terms + min(customer.tierRank) |
| Deep paginate customer groups | not clean with current index |
| Deep paginate customers cleanly | separate customer-as-document index |
Our Current Customer Insights Flow
Customer list:
impact documents -> nested customer aggregation -> group by customer -> sort by billAmount/tierRank -> API slices resultDrill-down:
impact documents -> filter by event + customerId -> OpenSearch from/size pagination
Customers query:
{
"size": 0, // dont return normal search hits, only grouped results are needed
"query": {//filter
"term": {//exact match
"event.keyword": {//keyword must be used for exact matches
"value": "UE107348"
}
}
},
"aggs": {//group
"customers_nested": {//aggr name - can be anything
"nested": {
"path": "customer"//enter nested customer array inside impact doc
},
"aggs": {
"by_customer": {//group by customer id now that we are inside nested cust-bucket per cust
"terms": {
"field": "customer.customerId",
"size": 100,//return top 100 cust buckets
"order": [
{
"customer_bill_amount": "desc"/ "customer_tier_rank": "asc"//sort by metric aggr
}
]
},
"aggs": {//start sub aggr inside each cust bucket
"customer_bill_amount": {
"max": {
"field": "customer.billAmount"
}
},
"customer_tier_rank": {
"min": {
"field": "customer.tierRank"
}
},
"customer_details": {
"top_hits": {//for each bucket, fetch 1 doc to read cust details
"size": 1,
"_source": {
"includes": [
"customer"//only return cust field from doc
]
}
}
},
"impacted_resource_count": {
"reverse_nested": {}//move up to parent impact level n return doc_count
}
}
}
}
}
}
}
Response:
"hits": {
"total": {
"value": 410,//num of docs matching filter
"relation": "eq"
},
"hits": []//empty bcoz size=0
}
"aggregations": {
"customers_nested": {
"doc_count": 187,//num of aggr cust
"by_customer": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,//custs not in list bcoz of terms.size limit
"buckets": [//unique buckets could be less than actual aggre count
{
"key": "300203",//cust id (grouped by)
"doc_count": 6, // num of nested cust in bucket
"customer_details": {
"hits": {
"total": {
"value": 6,
"relation": "eq"
},
"max_score": 0.0012172856,
"hits": [
{
"_index": "smartsearch-impact-1",
"_id": "UE107348:450184",
"_nested": {
"field": "customer",
"offset": 0
},
"_score": 0.0012172856,
"_source": {
"customerSince": "2016-09-21",
"customerType": "Enterprise Customer",
"billAmount": 7180,
"phone": "3065557020",
"customerId": "300203",
"location": "2278 GOFF PLACE REGINA",
"sortMode": "billAmount",
"customerHref": "http://100.76.172.91:8001/Inventory/RSOpenAPI/party/300203",
"customerRole": "CustomerInsightsCustomer",
"customerName": "Canola Foods Distribution",
"email": "noc@canola-foods.ca"
}
}
]
}
},
"customer_bill_amount": {
"value": 7180
},
"customer_tier_rank": {
"value": null
},
"impacted_resource_count": {
"doc_count": 6
}
},
{
"key": "300187",
...
}
}
}
}
---
| OpenSearch Response | API Model Field |
|---|---|
| bucket key | CustomerInsight.customerId |
| customer details _source.customer[0].customerName | customerName |
| customer details _source.customer[0].billAmount | billAmount |
| customer details _source.customer[0].tier | tier |
| customer_bill_amount.value or customer_tier_rank.value | used for sorting |
| active sort field value | sortValue |
| impacted_resource_count.doc_count | impactedResourceCount |
| number of returned buckets / parsed buckets | totalCustomers |
| OpenSearch field | API field |
|---|---|
| id | impactId |
| name | name |
| entityType | entityType |
| specification | specification |
| impactType | impactType |
| impactSeverity | impactSeverity |
| analysisStatus | analysisStatus |
| href | href |
| event | eventId |
