Data Aggregation Examples

Info

Below are examples of data aggregation, which were performed via the CaseSearchExtRest#groupByQuery(POST) service. The syntax of the Group By clause was described in the article Lucene Index Queries. The examples were performed using the SoapUI tool.

Example 1

Counting cases created on a given day.

ignoreCaseHeaderInResponse

To simplify the presentation of the result in the context object, the ignoreCaseHeaderInResponse parameter is set to true (ignore the data header in the response). See the article SOAP/REST Services Request Context.

Content of the request sent to the service

{
  "context": {
    "appName": "mercury-ws-app",
    "appVersion": "1.0",
    "userName": "anonymous",
    "comment": null,
    "maxResults": 100000,
    "queryTimeout": 2147483647,
    "locale": "pl_PL",
    "timeZone": "Europe/Warsaw",
    "userFullName": null,
    "eager4omdBuilder": "true",
    "trustedData": false,
    "ignoredCustomFields": null,
    "currentRole": null,
    "userRoles": null,
    "sourceOfRequest": "USER_DEV.localhost",
    "rootVersionContextID": null,
    "rootTagName": null,
    "directRequest": false,
    "formats": {
      "date.format.long": "dd-MM-yyyy HH:mm:ss XXX"
    },
    "ignoreAlternateFields": true,
    "decodeResult": "DATE_AND_LOB",
    "maxDepthResult": 3,
    "decodeRequest": "DATE_AND_LOB",
    "ignoreCaseHeaderInResponse": true,
    "cacheUsage": "REFRESH",
    "httpResponseCacheUsage": "NONE",
    "defaultLuceneSortClause": null,
    "viewDefinition": null
  },
  "query": "mrc_status: A",
  "groupByClause": "trunc(mrc_createDate, DD) as createDatePerMonth, count(1) as count",
  "filterClause": null,
  "additionalDateRange":  "mrc_createDate:[946681200000 TO 1564608394143]",
  "page": {
    "size": 60,
    "number": 1
  },
  "resultTypeName": null,
  "resultPkPropertyName": null
}

Example 2

Using the aggregate functions SUM, MAX, MIN, AVG (see Counting (aggregating) functions).

ignoreCaseHeaderInResponse

To simplify the presentation of the result in the context object, the parameter ignoreCaseHeaderInResponse was set to true (ignore the data header in the response). See the article SOAP/REST services request context.

Content of the request sent to the service

{
  "context": {
    "appName": "mercury-ws-app",
    "appVersion": "1.0",
    "userName": "anonymous",
    "comment": null,
    "maxResults": 100000,
    "queryTimeout": 2147483647,
    "locale": "pl_PL",
    "timeZone": "Europe/Warsaw",
    "userFullName": null,
    "eager4omdBuilder": "true",
    "trustedData": false,
    "ignoredCustomFields": null,
    "currentRole": null,
    "userRoles": null,
    "sourceOfRequest": "USER_DEV.localhost",
    "rootVersionContextID": null,
    "rootTagName": null,
    "directRequest": false,
    "formats": {
      "date.format.long": "dd-MM-yyyy HH:mm:ss XXX"
    },
    "ignoreAlternateFields": true,
    "decodeResult": "DATE_AND_LOB",
    "maxDepthResult": 3,
    "decodeRequest": "DATE_AND_LOB",
    "ignoreCaseHeaderInResponse": true,
    "cacheUsage": "REFRESH",
    "httpResponseCacheUsage": "NONE",
    "defaultLuceneSortClause": null,
    "viewDefinition": null
  },
  "query": "mrc_status: A",
  "groupByClause": "id, sum(months.costs) as sum, max(months.costs) as max, min(months.costs) as min, avg(months.costs) as avg",
  "filterClause": null,
  "additionalDateRange":  "mrc_createDate:[946681200000 TO 1564608394143]",
  "page": {
    "size": 60,
    "number": 1
  },
  "resultTypeName": null,
  "resultPkPropertyName": null
}

Example 3

Using the DECODE function with COUNT data aggregation (counting data).

ignoreCaseHeaderInResponse

To simplify the presentation of the result in the context object, the ignoreCaseHeaderInResponse parameter was set to true (ignore the data header in the response). See the article SOAP/REST service request context.

Request content sent to the service

{
  "context": {
    "appName": "mercury-ws-app",
    "appVersion": "1.0",
    "userName": "anonymous",
    "comment": null,
    "maxResults": 100000,
    "queryTimeout": 2147483647,
    "locale": "pl_PL",
    "timeZone": "Europe/Warsaw",
    "userFullName": null,
    "eager4omdBuilder": "true",
    "trustedData": false,
    "ignoredCustomFields": null,
    "currentRole": null,
    "userRoles": null,
    "sourceOfRequest": "USER_DEV.localhost",
    "rootVersionContextID": null,
    "rootTagName": null,
    "directRequest": false,
    "formats": {
      "date.format.long": "dd-MM-yyyy HH:mm:ss XXX"
    },
    "ignoreAlternateFields": true,
    "decodeResult": "DATE_AND_LOB",
    "maxDepthResult": 3,
    "decodeRequest": "DATE_AND_LOB",
    "ignoreCaseHeaderInResponse": true,
    "cacheUsage": "REFRESH",
    "httpResponseCacheUsage": "NONE",
    "defaultLuceneSortClause": null,
    "viewDefinition": null
  },
  "query": "mrc_status: A",
  "groupByClause": "id, decode(months.costs, \"[* TO 125000]\", \"low\", \"[125001 TO 140000]\", \"medium\", \"high\") as cost, count(*) as count",
  "filterClause": null,
  "additionalDateRange":  "mrc_createDate:[946681200000 TO 1564608394143]",
  "page": {
    "size": 60,
    "number": 1
  },
  "resultTypeName": null,
  "resultPkPropertyName": null
}

Example 4

Using the CONCAT function.

ignoreCaseHeaderInResponse

To simplify the presentation of the result in the context object, the ignoreCaseHeaderInResponse parameter was set to true (ignore the data header in the response). See the article SOAP/REST service request context.

Request content sent to the service

{
  "context": {
    "appName": "mercury-ws-app",
    "appVersion": "1.0",
    "userName": "anonymous",
    "comment": null,
    "maxResults": 100000,
    "queryTimeout": 2147483647,
    "locale": "pl_PL",
    "timeZone": "Europe/Warsaw",
    "userFullName": null,
    "eager4omdBuilder": "true",
    "trustedData": false,
    "ignoredCustomFields": null,
    "currentRole": null,
    "userRoles": null,
    "sourceOfRequest": "USER_DEV.localhost",
    "rootVersionContextID": null,
    "rootTagName": null,
    "directRequest": false,
    "formats": {
        "date.format.long": "dd-MM-yyyy HH:mm:ss XXX"
    },
    "ignoreAlternateFields": true,
    "decodeResult": "DATE_AND_LOB",
    "maxDepthResult": 3,
    "decodeRequest": "DATE_AND_LOB",
    "ignoreCaseHeaderInResponse": true,
    "cacheUsage": "REFRESH",
    "httpResponseCacheUsage": "NONE",
    "defaultLuceneSortClause": null,
    "viewDefinition": null
  },
  "query": "mrc_typeCodeValue:ElixClient",
  "groupByClause": "concat(clientID, \": \", clientName) as acronym",
  "filterClause": null,
  "additionalDateRange":  null,
  "page": {
    "size": 60,
    "number": 1
  },
  "resultTypeName": null,
  "resultPkPropertyName": null
}

Example 5

Using the DECODE function with the MATH function (as an argument to the DECODE function) and the COUNT data aggregation (counting data).

ignoreCaseHeaderInResponse

To simplify the presentation of the result in the context object, the ignoreCaseHeaderInResponse parameter was set to true (ignore the data header in the response). See the article SOAP/REST service request context.

Request content sent to the service

{
  "context": {
    "appName": "mercury-ws-app",
    "appVersion": "1.0",
    "userName": "anonymous",
    "comment": null,
    "maxResults": 100000,
    "queryTimeout": 2147483647,
    "locale": "pl_PL",
    "timeZone": "Europe/Warsaw",
    "userFullName": null,
    "eager4omdBuilder": "true",
    "trustedData": false,
    "ignoredCustomFields": null,
    "currentRole": null,
    "userRoles": null,
    "sourceOfRequest": "USER_DEV.localhost",
    "rootVersionContextID": null,
    "rootTagName": null,
    "directRequest": false,
    "formats": {
      "date.format.long": "dd-MM-yyyy HH:mm:ss XXX"
    },
    "ignoreAlternateFields": true,
    "decodeResult": "DATE_AND_LOB",
    "maxDepthResult": 3,
    "decodeRequest": "DATE_AND_LOB",
    "ignoreCaseHeaderInResponse": true,
    "cacheUsage": "REFRESH",
    "httpResponseCacheUsage": "NONE",
    "defaultLuceneSortClause": null,
    "viewDefinition": null
  },
  "query": "mrc_typeCodeValue:Quarter",
  "groupByClause": "id, decode(MATH(mrc_endDate - mrc_createDate), \"[* TO -1]\", \"still open\", \"[0 TO 86400000]\", \"1 day\", \"[86400001 TO 172800000]\", \"2 days\", \"very long\") as cost, count(*) as count",
  "filterClause": null,
  "additionalDateRange":  "mrc_createDate:[946681200000 TO 1564608394143]",
  "page": {
    "size": 60,
    "number": 1
  },
  "resultTypeName": null,
  "resultPkPropertyName": null
}