Sub-Aggregation
Sub-Aggregation lets you perform aggregations based on multiple fields at multiple level within a single query. This lets you analyze data from various angles simultaneously.
Sub-aggregation lets you perform aggregations (calculations like sum, average, count) on groups of fields, and then performing further aggregations on those groups. Currently, Adaptive Search supports upto 10 levels of aggregation. Below are a couple of sample sub-aggregation queries.
Querying an Aggregation of Industry with a Sub-Aggregation of Owner
The below example shows a sample request payload and response payload to aggregate the primary industry with a sub-aggregation of the primary owner.
cURL Command
curl --location
'https://servername.fa.us2.oraclecloud.com/crmRestApi/searchResources/11.13.18.05/custom-actions/queries'Sample Request Payload
{
"entity": "Account",
"limit": 0,
"aggregations": {
"PrimaryIndustry": {
"ignore": false,
"terms": {
"attribute": "PrimaryIndustry",
"maxNumberOfBuckets": 5,
"other": false,
"missing": false,
"localize": true
},
"aggregations": {
"Owner": {
"ignore": false,
"terms": {
"attribute": "Owner",
"maxNumberOfBuckets": 5,
"other": false,
"missing": false,
"localize": true
}
}
}
}
}
}
Sample Reponse Payload
{
"aggregations": [
{
"PrimaryIndustry": {
"other": null,
"missing": null,
"buckets": [
{
"key": "1700",
"count": 16,
"localizedKey": "Financial Services",
"aggregations": [
{
"Owner": {
"other": null,
"missing": null,
"buckets": [
{
"key": "gabrielle lee",
"count": 16,
"localizedKey": "Gabrielle Lee",
"q": {
"op": "$in",
"attribute": "Owner",
"values": ["gabrielle lee"]
}
}
]
}
}
],
"q": {
"op": "$in",
"attribute": "PrimaryIndustry",
"values": ["1700"]
}
},
{
"key": "1400",
"count": 15,
"localizedKey": "Consumer Goods",
"aggregations": [
{
"Owner": {
"other": null,
"missing": null,
"buckets": [
{
"key": "gabrielle lee",
"count": 15,
"localizedKey": "Gabrielle Lee",
...
...
"totalResults": 75
}
Querying a Date Range Aggregation with a Sub-Aggregation of Primary Contact
The below example shows a sample request payload and response payload to aggregate based on a date range with a sub-aggregation of primary contact.
cURL Command
curl --location
'https://servername.fa.us2.oraclecloud.com/crmRestApi/searchResources/11.13.18.05/custom-actions/queries'Sample Request Payload
{
"entity": "Account",
"limit": 0,
"aggregations": {
"CreationDate": {
"ignore": true,
"range": {
"attribute": "CreationDate",
"localize": true,
"ranges": [
{
"key": "ThisQuarter",
"from": "now/M-2M",
"to": "now/M+1M"
},
{
"key": "ThisYear",
"from": "now/y",
"to": "now/y+1y"
}
]
},
"aggregations": {
"PrimaryContact": {
"ignore": true,
"terms": {
"attribute": "PrimaryContact",
"maxNumberOfBuckets": 5,
"other": false,
"missing": true,
"localize": true
}
}
}
}
}
}
Sample Reponse Payload
Querying a Date Histogram Aggregation with a Sub-Aggregation of Primary Contact
The below example shows a sample request payload and response payload to aggregate based on a date range with a sub-aggregation of primary contact.
cURL Command
curl --location
'https://servername.fa.us2.oraclecloud.com/crmRestApi/searchResources/11.13.18.05/custom-actions/queries'{
"entity": "Account",
"limit": 0,
"aggregations": {
"CreationDate": {
"ignore": true,
"date_histogram": {
"attribute": "CreationDate",
"localize": true,
"minBucketCount": 0,
"maxExtendedBound": "now/M+6M-1d",
"minExtendedBound": "now/M",
"frequency": "month"
},
"aggregations": {
"PrimaryContact": {
"ignore": true,
"terms": {
"attribute": "PrimaryContact",
"maxNumberOfBuckets": 5,
"other": false,
"missing": true,
"localize": true
}
}
}
}
}
}Querying a Date Range Aggregation on Effective Data with a Multiple Sub-Aggregations
The below example shows a sample request payload for requesting a date range aggregation on effective date with the sub-aggregations giving a sum of revenue and on the status code. The status code also has a sub aggregation giving the sum of the revenue by the status.
cURL Command
curl --location
'https://servername.fa.us2.oraclecloud.com/crmRestApi/searchResources/11.13.18.05/custom-actions/queries'{
"entity": "Opportunity",
"limit": 0,
"aggregations": {
"EffectiveDate": {
"ignore": true,
"range": {
"attribute": "EffectiveDate",
"localize": true,
"ranges": [
{
"key": "ThisQuarter",
"from": "now/M-2M",
"to": "now/M+1M"
},
{
"key": "ThisYear",
"from": "now/y",
"to": "now/y+1y"
}
]
},
"aggregations": {
"SumRevnAmountByEffectiveDate": {
"attribute": "ListOfRevenues.RevnAmount",
"metric": "sum"
},
"StatusCode": {
"ignore": true,
"terms": {
"attribute": "StatusCode",
"maxNumberOfBuckets": 5,
"other": false,
"missing": true,
"localize": true
},
"aggregations": {
"SumRevnAmountByStatus": {
"attribute": "ListOfRevenues.RevnAmount",
"metric": "sum"
}
}
}
}
}
}
}Querying Revenue with an Aggregation on Status Code and Multiple Sub-Aggregations
The below example shows a sample request payload for Revenue requesting an aggregation on Status Code, further grouped by Oppty Name with a sum of the revenue at both the oppty level and the status level. The Oppty Name grouping also contains the top hits.
cURL Command
curl --location
'https://servername.fa.us2.oraclecloud.com/crmRestApi/searchResources/11.13.18.05/custom-actions/queries'{
"entity": "Revenue",
"fields": [
"RevnAmount",
"SalesCreditTypeCode"
],
"onlyData": true,
"debug": {
"query": true
},
"limit": 0,
"aggregations": {
"Opportunity.StatusCode": {
"ignore": false,
"terms": {
"attribute": "Opportunity.StatusCode",
"maxNumberOfBuckets": 5,
"localize": true
},
"aggregations": {
"SumRevnAmountByStatus": {
"attribute": "RevnAmount",
"metric": "sum"
},
"Opportunity.Name": {
"ignore": false,
"terms": {
"attribute": "Opportunity.Name",
"maxNumberOfBuckets": 25,
"localize": true
},
"topHits": 5,
"aggregations": {
"SumRevnAmountByOpty": {
"attribute": "RevnAmount",
"metric": "sum"
}
}
}
}
}
}
}