Aggregating an order

Aggregation summarizes your data as metrics, statistics, or other analytics. It calculates metrics, such as sum or average, from values of specific fields among the matching documents. It groups the matching documents based on values, value ranges of the specified fields, or criteria.

Order Service supports multiple aggregation types, which you can further categorize as Bucket and Metric aggregation. Bucket aggregation returns the results in the form of a list of value-count pairs, called buckets, that are based on field values, ranges, or other criteria. Metric aggregation calculates metrics, such as a sum or average based on field values.

The following aggregation types are supported:

Aggregation Category	Type	Order Search Aggregation type	Description
Bucket	Date Range	`DATE_RANGE`	A range-based aggregation that accepts date values, where the From dateis included and the To date is excluded. For more information, see Date range aggregator.
Bucket	Missing	`MISSING`	A single bucket aggregation that displays results based on those documents that do not contain the specified term. For more information, see Missing aggregator.
Bucket	Range	`RANGE`	A multi-bucket that can be applied to a value or a range of values.
Bucket	Terms or Nested	`TERMS`	This aggregator displays the documents and their count based on a field or nested fields. For more information, see Terms aggregator.
Metrics	Average	`AVG`	A single metrics aggregator that returns the average of the values from the documents.
Metrics	Cardinality	`DISTINCT_COUNT`	A single metrics aggregator that returns the count of the distinct values in a field.
Metrics	Count	`COUNT`	A single metrics aggregator that returns the count of the values in a field.
Metrics	Maximum	`MAX`	A single metrics aggregator that returns the maximum of the values from the documents.
Metrics	Minimum	`MIN`	A single metrics aggregator that returns the minimum of the values from the documents.
Metrics	Summation	`SUM`	A single value aggregation metric that sums up the values extracted from documents.

Count aggregator

It returns the number of values that the current document set, resulted after aggregation, has for a specific field.

Note: Count does not remove the duplicate values. Therefore, even if a field has duplicates, each value is counted individually. To avoid calculating duplicate values, that is, to count only the distinct values, use the distinct count aggregation.

The following query fetches all the orders that have OriginalTotalAmount greater than 10 and aggregates the search results to find out the number of values for the OrderLine.OrderedQty field.

{
  "query": {
    "match": [
      {
        "condition": "MUST",
        "field": "OriginalTotalAmount",
        "value":"10",
        "operator":"gt"
      }
    ]
  },
  "aggregate":[
    {
      "field":"OrderLine.OrderedQty",
      "type": "COUNT",
      "name":"OrderQty"
    }
  ]
}

Refer to the following output.

"aggregation": [
    {
      "field": "OrderLine.OrderedQty",
      "type": "COUNT",
      "name": "OrderQty",
      "countAgg": 7
    }
  ]

Distinct count

The aggregation returns the count of distinct values or unique terms.

The following query fetches all the orders that have OriginalTotalAmount greater than 10 and aggregates the search results to find out the number of unique values for the OrderLine.OrderedQty field.

{
    "query": {
      "match": [
        {
          "condition": "MUST",
          "field": "OriginalTotalAmount",
          "value":"10",
          "operator":"gt"
        }
      ]
    },
    "aggregate":[
      {
        "field":"OrderLine.OrderedQty",
        "type": "DISTINCT_COUNT",
        "name":"UniqueOrderQty"
      }
    ]
  }

Refer to the following output.

"aggregation": [
    {
      "field": "OrderLine.OrderedQty",
      "type": "DISTINCT_COUNT",
      "name": "UniqueOrderQty",
      "countAgg": 3
    }
  ],

Minimum aggregator

The aggregator returns the minimum value among values that are extracted from the aggregated documents.

Note: The MIN aggregator operates on the numerical representation of the data. Therefore, when you specify the aggregator for a field other than the numerical type, the API results in an error.

The following query fetches all the orders for which OrderLine.PersonInfoShipTo.City is London and finds out the MIN OrderLine.OrderedQty among the aggregated orders.

  {
    "query": {
      "match": [
        {
          "condition": "MUST",
          "field": "OrderLine.PersonInfoShipTo.City",
          "value":"London",
          "operator":"eq"
        }
      ]
    },
    "aggregate":[
      {
        "field":"OrderLine.OrderedQty",
        "type": "MIN",
        "name":"MinimumOrderQty"
      }
    ]
  }

Refer to the following example.

  "aggregation": [
    {
      "field": "OrderLine.OrderedQty",
      "type": "MIN",
      "name": "OrderQty",
      "numAgg": 1
    }
  ]

Maximum aggregator

This aggregator returns the maximum value among values that are extracted from the aggregated documents.

Note: The MAX aggregator operates on the numerical representation of the data. Therefore, when you specify the aggregator for a field other than the numerical type, the API results in an error.

The following search query fetches all the orders for which OrderLine.PersonInfoShipTo.City is London and finds out the maximum OrderLine.OrderedQty among the aggregated orders.

  {
    "query": {
      "match": [
        {
          "condition": "MUST",
          "field": "OrderLine.PersonInfoShipTo.City",
          "value":"London",
          "operator":"eq"
        }
      ]
    },
    "aggregate":[
      {
        "field":"OrderLine.OrderedQty",
        "type": "MAX",
        "name":"MaximumOrderQty"
      }
    ]
  }

Refer to the following output.

  "aggregation": [
    {
      "field": "OrderLine.OrderedQty",
      "type": "MAX",
      "name": "MaximumOrderQty",
      "numAgg": 15
    }
  ]

Average aggregator

This aggregator returns the average of the values in the current aggregation bucket. The following search query fetches all the orders for which OrderLine.PersonInfoShipTo.City is London and finds out the average of the OriginalTotalAmount values for those orders.

  {
    "query": {
      "match": [
        {
          "condition": "MUST",
          "field": "OrderLine.PersonInfoShipTo.City",
          "value":"London",
          "operator":"eq"
        }
      ]
    },
    "aggregate":[
      {
        "field":"OriginalTotalAmount",
        "type": "AVG",
        "name":"AvgOfOriginalTotalAmount"
      }
    ]
  }

Refer to the following output.

  "aggregation": [
    {
      "field": "OriginalTotalAmount",
      "type": "AVG",
      "name": "AvgOfOriginalTotalAmount",
      "numAgg": 40.75
    }
  ]

Summation aggregator

The aggregator returns the sum of the values in current aggregation bucket. The following search query fetches all the orders for which OrderLine.PersonInfoShipTo.City is London and finds out the sum of the OriginalTotalAmount values for those orders.

  {
    "query": {
      "match": [
        {
          "condition": "MUST",
          "field": "OrderLine.PersonInfoShipTo.City",
          "value":"London",
          "operator":"eq"
        }
      ]
    },
    "aggregate":[
      {
        "field":"OriginalTotalAmount",
        "type": "SUM",
        "name":"TotalOfOriginalTotalAmount"
      }
    ]
  }

Refer to the following output.

  "aggregation": [
    {
      "field": "OriginalTotalAmount",
      "type": "SUM",
      "name": "TotalOfOriginalTotalAmount",
      "numAgg": 81.5
    }
  ]

Terms aggregator

This aggregator represents a multi-bucket value, which is based on aggregations where buckets are built dynamically, with one bucket per unique value.

The following query fetches the orders that have OriginalTotalAmount greater than 10, and then aggregate those order based on the OrderLine.PersonInfoShipTo.City. For each unique value that OrderLine.PersonInfoShipTo.City has, the application creates a bucket with value-count pairs. Value, in this case, is the actual city and count is the number of order documents for that city.


{
 "query": {
   "match": [
     {
       "condition": "MUST",
       "field": "OriginalTotalAmount",
       "value":"10",
       "operator":"gt"
     }
   ]
 },
 "aggregate":[
    {
     "field":"OrderLine.PersonInfoShipTo.City",
     "type":"TERMS",
     "name":"ShipToCity"
   }
 ]
}

Refer to the following response.

"aggregation": [
 {
   "field": "OrderLine.PersonInfoShipTo.City",
   "type": "TERMS",
   "name": "ShipToCity",
   "result": [
     {
       "value": "Littleton",
       "count": 3
     },
     {
       "value": "London",
       "count": 1
     },
     {
       "value": "Mumbai",
       "count": 1
     },
     {
       "value": "Pune",
       "count": 1
     }
   ]
 }
]

Note: By default, the results are sorted by count in the descending order, which can be changed if needed. For more information on extending the features provided by the TERMS aggregator, such as arranging the results in the ascending order, see Extended features.

Range aggregator

The aggregator returns multiple buckets, each associated with a pre-defined value range of fields. The value of the fields in all documents for each bucket might fall within the buckets range.

Note: The range parameter is available with the searchOrder API, which uses from, to, and name fields. You can use these fields along with the RANGE aggregator to return multiple ranges that contain the optional name. You can achieve unbounded range aggregation by passing either from or to parameter. To achieve a similar operation with the DATE fields, use the DATE-RANGE field.

The following search query fetches all orders that have OriginalTotalAmount greater than 10. Among those orders, it looks for orders that have OrderLine.OrderedQty in the range 1 - 90, where the to value is excluded. Optionally, you can provide this range of orders with name. Refer to the following example: the OrderQtyBetween1To90 and orders that have OrderLine.OrderedQty from 100 to unbounded (infinity).

{
    "query": {
      "match": [
        {
          "condition": "MUST",
          "field": "OriginalTotalAmount",
          "value":"10",
          "operator":"gt"
        }
      ]
    },
    "aggregate":[
      {
        "field":"OrderLine.OrderedQty",
        "type": "RANGE",
        "name":"OrderQty",
        "range": [
          {
            "from": 1,
            "to":90,
            "name": "OrderQtyBetween1To90"
          },
          {
            "from": 100,
            "name": "OrderQtyFrom100"
          }
        ]
      }
    ]
  }

Refer to the following response.

  "aggregation": [
    {
      "field": "OrderLine.OrderedQty",
      "type": "RANGE",
      "name": "OrderQty",
      "result": [
        {
          "value": "OrderQtyFrom100",
          "count": 3
        },
        {
          "value": "OrderQtyBetween1To90",
          "count": 3
        }
      ]
    }
  ]

Date range aggregator

The aggregator represents the range aggregation that is dedicated for the date values or a range of dates. The difference between the date-range aggregation and range aggregation is that in the date range aggregation, you can express from and to values in Date Math expressions. Also, you can specify the date format in which from and to results are returned.

Note:

The range parameter is available with the searchOrder API, which uses from, to, and name fields. You can use the parameter with the date range aggregator to provide multiple ranges that contain optional name. You can achieve unbounded range aggregation by passing from or to parameter.
The dateRangeFormat parameter available with the searchOrder API to specify a date format based on which from and to response is returned. The default format for the date range is yyyy-MM.

The following search query fetches all orders for which BuyerUserId starts with bill101. Then, among those orders, it finds out how many orders are placed in a year that is specified in from and to parameters.

{
    "query": {
        "match": [
          {
            "condition": "MUST",
            "field": "BuyerUserId",
            "value":"bill101",
            "operator":"startsWith"
          }
        ]
      },
    "aggregate": [
      {
        "field": "OrderDate",
        "type": "DATE_RANGE",
        "dateRangeFormat": "yyyy-MM-dd",
        "range": [
          {
            "from": "2020-01-01",
            "to": "2022-01-01"
          }
        ]
      }
    ]
  }

Refer to the following response.

 "aggregation": [
    {
      "field": "OrderDate",
      "type": "DATE_RANGE",
      "name": "OrderDate",
      "result": [
        {
          "value": "2020-01-01-2022-01-01",
          "count": 11
        }
      ]
    }
  ]

Missing aggregator

The aggregator returns a single bucket, which specifies the total number of order documents that do not have the specified field. The following search query fetches all the orders that have OriginalTotalAmount greater than 10 and shows count for all the orders that do not have the OrderLine.PersonInfoShipTo.City field.

  {
    "query": {
      "match": [
        {
          "condition": "MUST",
          "field": "OriginalTotalAmount",
          "value":"10",
          "operator":"gt"
        }
      ]
    },
    "aggregate":[
       {
        "field":"OrderLine.PersonInfoShipTo.City",
        "name":"OrdersWithoutOrderLinePersonInfoShipToCity",
        "type":"MISSING"
      }
    ]
  }

Refer to the following output.

  "aggregation": [
    {
      "field": "OrderLine.PersonInfoShipTo.City",
      "type": "MISSING",
      "name": "OrderMissingCity",
      "result": [
        {
          "value": "OrderMissingCity",
          "count": 1
        }
      ]
    }
  ]

Subaggregation

You can use subaggregation to continue to refine and separate groups of criteria of interest. Then, you can apply metrics at various levels in the aggregation hierarchy to refine those results.

All aggregator under category bucket supports subaggregation.

CAUTION:

The metric aggregation does not support subaggregation.

For example, to aggregate the search results based on OrderLine.PersonInfoShipTo.City and then to get the minimum of OriginalTotalAmount among the orders, you can use subaggregation. Refer to the following example query.

{
        "query": {
          "match": [
            {
              "condition": "MUST",
              "field": "OriginalTotalAmount",
              "value":"10",
              "operator":"gt"
            }
          ]
        },
        "aggregate":[
           {
            "field":"OrderLine.PersonInfoShipTo.City",
            "type":"TERMS",
            "name":"ShipToCity",
            "aggregate": {
              "field": "OriginalTotalAmount",
              "type": "MIN"
            }
          }
        ]
      }

Refer to the following output.

"aggregation": [
        {
          "field": "OrderLine.PersonInfoShipTo.City",
          "type": "TERMS",
          "name": "ShipToCity",
          "result": [
            {
              "value": "Littleton",
              "count": 3,
              "aggregation": {
                "field": "OriginalTotalAmount",
                "type": "MIN",
                "name": "OriginalTotalAmount",
                "numAgg": 30
              }
            },
            {
              "value": "London",
              "count": 1,
              "aggregation": {
                "field": "OriginalTotalAmount",
                "type": "MIN",
                "name": "OriginalTotalAmount",
                "numAgg": 100.48
              }
            },
            {
              "value": "Mumbai",
              "count": 1,
              "aggregation": {
                "field": "OriginalTotalAmount",
                "type": "MIN",
                "name": "OriginalTotalAmount",
                "numAgg": 400
              }
            },
            {
              "value": "Pune",
              "count": 1,
              "aggregation": {
                "field": "OriginalTotalAmount",
                "type": "MIN",
                "name": "OriginalTotalAmount",
                "numAgg": 500
              }
            }
          ]
        }
      ]

Note: The Order Service worker supports n-level of aggregation. But each parent aggregator must be a type of bucket aggregator.

Missing parameter

The missing parameter defines how order documents that are missing a field are treated. By default, they are ignored in aggregation results. However, it is possible to treat those order documents as if the documents had a value, while aggregating the results.

For example, the following query displays the results for all the order documents that have OrderLine.PersonInfoShipTo.City start with L. It also displays the average of the OriginalTotalAmount for those order documents.

{
    "queryFilter": [
     {
       "condition":"MUST",
       "type":"REGEX",
       "field":"OrderLine.PersonInfoShipTo.City",
       "value":"L.*"
     }
    ],
    "aggregate": [
     {
       "field": "OriginalTotalAmount",
       "type": "AVG"
     }
    ]
    }

In absence of the missing parameter, the application calculates the average as sum of OriginalTotalAmount from all orders that have OrderLine.PersonInfoShipTo.City staring with L divided by the number of order documents that contain OriginalTotalAmount.

For example, 7 documents have OrderLine.PersonInfoShipTo.City staring with L, but only 4 of them contain the OriginalTotalAmount field.

Refer to the following example of the results displayed in absence of the missing parameter.

"aggregation": [
    {
      "field": "OriginalTotalAmount",
      "type": "AVG",
      "name": "OriginalTotalAmount",
      "numAgg": 52.995000000000005
    }
  ]

But, you can add the missing parameter by setting its value as 0. Then, the orders that do not contain the OriginalTotalAmount field will be treated as orders with OriginalTotalAmount set as 0, and calculated accordingly. This impacts the results displayed.

"aggregation": [
    {
      "field": "OriginalTotalAmount",
      "type": "AVG",
      "name": "OriginalTotalAmount",
      "numAgg": 30.282857142857146
    }
  ]

You can use this parameter in combination with different aggregator to refine search results.

Important: The COUNT and MISSING aggregator do not support this parameter.