Extended Search and Data Aggregation Services - CaseSearchExt

Info

WADL definition is available at:
https://<hgdb_server_name>[:port]/mercury-ws-app/restServices/CaseSearchExtRest.wadl

Example: https://testcluster.hgdb.io/mercury-ws-app/restServices/CaseSearchExtRest.wadl.

Extended set of methods related to searching and aggregating HgDB system data:

	Method(HTTP Method)	Description
✅️	dateMetricQuery(POST)	Execution of a query aggregating data by a given Date type field.
✅️	fieldMetricQuery(POST)	Execution of queries returning the number of documents associated with a given field and its value.
✅️	groupByQuery(POST)	Method enabling execution of aggregate queries - with the Group by clause.
✅️	searchByQuery(POST)	Basic method of searching for cases based on a query to the Lucene index.

Attention

The dateMetricQuery(POST), fieldMetricQuery(POST), groupByQuery(POST) methods are services using specially constructed data collectors stored in the Lucene index. It is worth knowing that the data aggregation operations performed only apply to fields whose values are stored in the Lucene index.

Additionally, the service contains methods that allow for the analysis of the performance of a given query:

	Method(HTTP Method)	Description
⬜	explainPlanGroupByQuery(POST)	The request returns the execution plan of the Group by aggregation query. It allows for the analysis of the performance of the query without its execution.
⬜	explainPlanSearchByQuery(POST)	The request returns the execution plan of the search query. It allows for the analysis of the performance of the query without its execution.

dateMetricQuery

A service that allows for the aggregation of data depending on the date range defined in the request.

Use case

Using the service to present data aggregation on a timeline. An example would be a presentation on a chart (the charts below show the number of "closed" and "open" cases in different time periods:

image2022-6-21_19-11-23
source: [Grafana] Panel(https://grafana.com/))

image2022-6-21_19-13-4
source: [Grafana] Panel(https://grafana.com/))

Method request parameters

Below are the request parameters sent to the service:

context


Parameter name	context
Type	`Context`
Required?	Yes

The request context. For more on context, see SOAP/REST Service Request Context.

Context object example

{
  "appName": "mercury-ws-app",
  "appVersion": "0.0.1",
  "userName": "ttesteusz",
  "comment": "Zmiana nazwiska panieńskiego matki, na życzenie klienta",
  "maxResults": 1000,
  "queryTimeout": 100000,
  "locale": "pl_PL",
  "timeZone": "Europe/Warsaw",
  "userFullName": "Tadeusz Testeusz",
  "eager4omdBuilder": "true",
  "trustedData": true,
  "currentRole": "Dyrektor",
  "userRoles": [
    "Dyrektor",
    "CKBPM-Team",
    "mrc-admin",
    "mrc-user"
  ],
  "sourceOfRequest": "dev-bpm855-003.DEV",
  "formats": {
    "date.format.long": "yyyy-MM-dd HH-mm-ss"
  },
  "decodeResult": "DATE_ONLY",
  "decodeRequest": "DATE_AND_LOB",
  "ignoreCaseHeaderInResponse": false,
  "cacheUsage": "TO_USE",
  "httpResponseCacheUsage": "NONE",
  "requestProperties": {}
}

dateFieldsNames


Parameter name	dateFieldsNames
Type	`String[]`
Required?	Yes

List of field names with search type DateField (see description Lucene index field types), which will be the basis for aggregating data by dates. You can get the list of fields using the CaseIndexerFieldsManagerRest service by running the getFieldsBySearchType(POST) method.

Example

[
  "mrc_createDate"
]

duration


Parameter name	duration
Type	`String`
Required?	Yes

Data aggregation unit. The field can take one of the following values:

PER_DAY - aggregation will be performed "per day" - the result presents the number of cases found aggregated based on the date of the day (from a given day).
PER_HOUR - aggregation will be performed "per hour" - the result presents the number of cases found aggregated based on the date and time (from a given hour).
PER_MINUTE - aggregation will be performed "per hour" - the result presents the number of found cases aggregated based on date, hour and minute (within a minute).

Use case

Defining the range is convenient due to the clarity of the returned result. Use appropriate ranges depending on the size of the aggregated data set:

If we want to present aggregation from for cases from the last few hours, then we define the PER_MINUTE range.
If we want to present aggregation from for cases from the last few days, then we define the PER_HOUR range.
If we want to present aggregation from for cases from the last few months, years, then we define the PER_DAY range.

Example

"PER_HOUR"

query


Parameter name	query
Type	`String`
Required?	No

Optional, additional Lucene query that allows you to narrow down the aggregated data to issues that meet specific conditions. See the Search queries section for a description of how to construct queries.

Example of a search criterion by any issue status

"mrc_status: *"

All case' objects that have the mrc_status field in the Lucene index.

sortAscending


Parameter name	sortAscending
Type	`Boolean`
Required?	Yes

Flag defining the direction of sorting by date. Accepts values:

true - then the result will be sorted by date ascending
false - then the result will be sorted by date descending

Example of ascending sorting condition

true

additionalDateRange


Parameter name	additionalDateRange
Type	`String`
Required?	Yes

Additional narrowing query based on the Date type field. The query criterion must be defined as a range, i.e. "from to". Criterion written as a range of milliseconds representing the date¹.

tip

It is worth remembering that the best query effect will be obtained by adding a condition for the field that was indicated as the basis for aggregation in the dateFieldsName request parameter.

Example of a criterion for limiting results to cases created in the range from 1999-12-31 to 2019-06-31 21:26:34.143

"mrc_createDate:[946681200000 TO 1564608394143]"

page


Parameter name	page
Type	`MrcPage`
Required?	Yes

Definition of the result page object to fetch. Indicates which search result page to fetch.

Example, first page of 10 results

{
  "size": 10,
  "number": 1
}

Method request example

JSON request example

{
  "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": false,
    "cacheUsage": "REFRESH",
    "httpResponseCacheUsage": "TO_USE",
    "defaultLuceneSortClause": null,
    "viewDefinition": null
  },
  "dateFieldsNames": [
    "mrc_createDate"
  ],
  "duration": "PER_HOUR",
  "query": "mrc_status: *",
  "sortAscending": true,     
  "additionalDateRange":  "mrc_createDate:[946681200000 TO 1564608394143]",
  "page": {
    "size": 60,
    "number": 1
  }
}

Service Response

The response of a service that has successfully run is an ExcelData object, which is described in the article PagedResult as a list of retrieved data.

A complete example of presenting the response object in JSON (first page of the whole, hourly statistics of case creation and completion).

{
  "result":    [
        {
      "A": "2019-02-01 10",
      "B": "1",
      "C": "0",
      "rowIndx": "1"
    },
        {
      "A": "2019-02-01 09",
      "B": "6",
      "C": "0",
      "rowIndx": "2"
    },
        {
      "A": "2019-01-25 13",
      "B": "22796",
      "C": "0",
      "rowIndx": "3"
    },
        {
      "A": "2019-01-24 21",
      "B": "649",
      "C": "0",
      "rowIndx": "4"
    },
        {
      "A": "2019-01-23 08",
      "B": "7",
      "C": "0",
      "rowIndx": "5"
    },
        {
      "A": "2019-01-21 18",
      "B": "1",
      "C": "0",
      "rowIndx": "6"
    },
        {
      "A": "2019-01-21 15",
      "B": "11",
      "C": "0",
      "rowIndx": "7"
    },
        {
      "A": "2019-01-12 04",
      "B": "1",
      "C": "0",
      "rowIndx": "8"
    },
        {
      "A": "2019-01-12 03",
      "B": "23809",
      "C": "0",
      "rowIndx": "9"
    },
        {
      "A": "2019-01-10 15",
      "B": "0",
      "C": "4",
      "rowIndx": "10"
    },
        {
      "A": "2019-01-10 14",
      "B": "0",
      "C": "2",
      "rowIndx": "11"
    },
        {
      "A": "2019-01-10 13",
      "B": "0",
      "C": "2",
      "rowIndx": "12"
    },
        {
      "A": "2019-01-10 12",
      "B": "0",
      "C": "4",
      "rowIndx": "13"
    },
        {
      "A": "2019-01-10 11",
      "B": "0",
      "C": "2",
      "rowIndx": "14"
    },
        {
      "A": "2019-01-10 10",
      "B": "0",
      "C": "2",
      "rowIndx": "15"
    },
        {
      "A": "2019-01-10 07",
      "B": "0",
      "C": "2",
      "rowIndx": "16"
    },
        {
      "A": "2019-01-10 05",
      "B": "0",
      "C": "6",
      "rowIndx": "17"
    },
        {
      "A": "2019-01-09 20",
      "B": "0",
      "C": "2",
      "rowIndx": "18"
    },
        {
      "A": "2019-01-09 16",
      "B": "0",
      "C": "2",
      "rowIndx": "19"
    },
        {
      "A": "2019-01-09 15",
      "B": "0",
      "C": "4",
      "rowIndx": "20"
    }
  ],
  "message": "FRAGMENT",
  "resultSize": 134,
  "currentPageInfo":    {
    "size": 20,
    "number": 1
  },
  "firstPageInfo":    {
    "size": 20,
    "number": 1
  },
  "previousPageInfo":    {
    "size": 20,
    "number": 1
  },
  "nextPageInfo":    {
    "size": 20,
    "number": 2
  },
  "lastPageInfo":    {
    "size": 40,
    "number": 7
  },
  "header":    {
    "A": "Date",
    "B": "mrc_createDate",
    "C": "mrc_endDate"
  },
  "columnTypes":    {
    "A": "STRING",
    "B": "LONG",
    "C": "LONG"
  }
}

If for some reason the service fails, the response accepts the following output parameters:

errorCode - Error code. When the operation is successful, it takes the value null . Information about the data contained in the value of this parameter can be obtained by reading the article What does the value of the "errorCode" field mean?
errorMessage - Error message. When the operation is successful, it takes the value null.

fieldMetricQuery

A service that allows you to download a list of expressions (values) appearing in a given case field along with the number of documents in which the expression appears.

Use case

As an SEO tool (search engine optimization) - activities aimed at optimizing, increasing the visibility of websites, products in search engines, i.e. a mechanism that can be used to promote the most popular values (keywords) of a given field appearing in the HgDB database, e.g. the most popular cities, or perhaps the most frequently serviced customers. An example of its use can also be Index:Polish - Most popular words 1-1000 version of Jerzy Kazojcia (which does not mean that this list was obtained using this service).

Note

The numerical values obtained as a result of the service are statistical in nature, i.e. they are obtained based on Lucene index statistics. They do not take into account the fact that the document was removed from the Lucene index.

Method request parameters

context


Parameter name	context
Type	`Context`
Required?	Yes

Request context. For more on context, see SOAP/REST Service Request Context.

Context Object Example

{
  "appName": "mercury-ws-app",
  "appVersion": "1.0",
  "comment": null,
  "userName": "anonymous",
  "userFullName": null,
  "locale": null,
  "timeZone": null,
  "maxResults": 1,
  "currentRole": "anonymous",
  "userRoles": null,
  "sourceOfRequest": null,
  "maxDepthResult": 1,
  "decodeResult": "DECODE_DATE_AND_LOB",
  "ignoreCaseHeaderInResponse": false,
}

fieldName


Parameter name	fieldName
Type	`String`
Required?	Yes

The name of the Lucene index field for which the value metric will be retrieved.

Example of 'Memo' field statistics

"mrc_luceneDocumentMemo"

loadTop


Parameter name	loadTop
Type	`Boolean`
Required?	Yes

A flag that determines whether the expressions with the highest occurrence should be found. It takes the values true or false . Setting it to true implies a longer service execution time.

Example

true

filterClause


Parameter name	filterClause
Type	`String`
Required?	No

Additional criterion for filtering values. You can use a "wildcard" (wildcard symbol), e.g. to retrieve all values starting with the letter a, we use a* as a criterion.

Example of a search criterion by any status of issues

"mrc_status: *"

All case' objects that have the field mrc_status in the Lucene index.

sortAscending


Parameter name	sortAscending
Type	`Boolean`
Required?	Yes

Flag defining the direction of sorting by date. Accepts values:

true - then the result will be sorted by date ascending
false - then the result will be sorted by date descending

Example of ascending sorting condition

true

page


Parameter name	page
Type	`MrcPage`
Required?	Yes

Definition of the result page object to fetch. Indicates which search result page to fetch.

Example, first page of 10 results

{
  "size": 10,
  "number": 1
}

Method request example

JSON request example

{
  "context": {
    "appName": "mercury-ws-app",
    "appVersion": "1.0",
    "userName": "anonymous",
    "comment": null,
    "maxResults": 20,
    "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": false,
    "cacheUsage": "TO_USE",
    "httpResponseCacheUsage": "NONE",
    "defaultLuceneSortClause": null,
    "viewDefinition": null
  },
  "fieldName": "typeTypeCodeValue",
  "loadTop": true,
  "filterClause": "fsm*",
  "sortAscending": false,
  "page": {
    "size": 60,
    "number": 1
  }
}

Method response parameters

The response of a service that has successfully completed is an ExcelData object, which is described in the PagedResult article as a list of retrieved data.

The header and columnTypes response parameters always take the same values:
"header":    {
  "A": "value",
  "B": "filterClause",
  "C": "count"
},
"columnTypes":    {
  "A": "STRING",
  "B": "STRING",
  "C": "LONG"
}

A complete example of presenting the response object in JSON

{
  "result":    [
        {
      "A": "by",
      "B": null,
      "C": "1060718",
      "rowIndx": "1"
    },
        {
      "A": "form",
      "B": null,
      "C": "1060718",
      "rowIndx": "2"
    },
        {
      "A": "generated",
      "B": null,
      "C": "1060718",
      "rowIndx": "3"
    },
        {
      "A": "request",
      "B": null,
      "C": "1060718",
      "rowIndx": "4"
    },
        {
      "A": "mercury",
      "B": null,
      "C": "699048",
      "rowIndx": "5"
    },
        {
      "A": "terytstreet",
      "B": null,
      "C": "590773",
      "rowIndx": "6"
    },
        {
      "A": "a",
      "B": null,
      "C": "504888",
      "rowIndx": "7"
    },
        {
      "A": "ulica",
      "B": null,
      "C": "474959",
      "rowIndx": "8"
    },
        {
      "A": "ul",
      "B": null,
      "C": "299273",
      "rowIndx": "9"
    },
        {
      "A": "slawas",
      "B": null,
      "C": "247994",
      "rowIndx": "10"
    },
        {
      "A": "01",
      "B": null,
      "C": "201715",
      "rowIndx": "11"
    },
        {
      "A": "nieokreślona",
      "B": null,
      "C": "200600",
      "rowIndx": "12"
    },
        {
      "A": "99998",
      "B": null,
      "C": "200469",
      "rowIndx": "13"
    },
        {
      "A": "true",
      "B": null,
      "C": "178552",
      "rowIndx": "14"
    },
        {
      "A": "false",
      "B": null,
      "C": "152821",
      "rowIndx": "15"
    },
        {
      "A": "04",
      "B": null,
      "C": "152131",
      "rowIndx": "16"
    },
        {
      "A": "02",
      "B": null,
      "C": "143505",
      "rowIndx": "17"
    },
        {
      "A": "06",
      "B": null,
      "C": "138035",
      "rowIndx": "18"
    },
        {
      "A": "14",
      "B": null,
      "C": "127182",
      "rowIndx": "19"
    },
        {
      "A": "brak",
      "B": null,
      "C": "118129",
      "rowIndx": "20"
    }
  ],
  "message": "ALL",
  "resultSize": 20,
  "currentPageInfo":    {
    "size": 20,
    "number": 1
  },
  "firstPageInfo":    {
    "size": 20,
    "number": 1
  },
  "previousPageInfo":    {
    "size": 20,
    "number": 1
  },
  "nextPageInfo":    {
    "size": 20,
    "number": 1
  },
  "lastPageInfo":    {
    "size": 20,
    "number": 1
  },
  "header":    {
    "A": "value",
    "B": "filterClause",
    "C": "count"
  },
  "columnTypes":    {
    "A": "STRING",
    "B": "STRING",
    "C": "LONG"
  }
}

If for some reason the service fails, the response accepts the following output parameters:

errorCode - Error code. If the operation is successful, it takes the value null . Information about the data contained in the value of this parameter can be obtained by reading the article What does the value of the "errorCode" field mean?
errorMessage - Error message. If the operation is successful, it takes the value null.

groupByQuery

The service implementation allows for the execution of requests that group (aggregate) data based on the Group By clause.

Use case

The service can be used for all kinds of Business Intelligence (BI) data analyses.

Method request parameters

context


Parameter name	context
Type	`Context`
Required?	Yes

The request context. For more on context, see SOAP/REST Service Request Context.

Context object example

{
  "appName": "mercury-ws-app",
  "appVersion": "1.0",
  "comment": null,
  "userName": "anonymous",
  "userFullName": null,
  "locale": null,
  "timeZone": null,
  "maxResults": 1,
  "currentRole": "anonymous",
  "userRoles": null,
  "sourceOfRequest": null,
  "maxDepthResult": 1,
  "decodeResult": "DECODE_DATE_AND_LOB",
  "ignoreCaseHeaderInResponse": false,
}

query


Parameter name	query
Type	`String`
Required?	No

Example of a search criterion by any issue status

"mrc_status: *"

All case' objects that have the field mrc_status in the Lucene index.

groupByClause


Parameter name	query
Type	`String`
Required?	Yes

Group By clause aggregating data stored in the Lucene index. See the Group By clause section for a description of how to construct aggregation queries.

Example of counting created cases during the day

"trunc(mrc_createDate, DD) as createDatePerMonth, count(1) as count"

filterClause


Parameter name	filterClause
Type	`String`
Required?	No

Additional criterion for filtering values. You can use wildcard, e.g. to get all values starting with the letter a, we will give a* as a criterion.

Example of a search criterion by any case status

"mrc_status: *"

All case' objects that have the field mrc_status in the Lucene index.

additionalDateRange


Parameter name	additionalDateRange
Type	`String`
Required?	Yes

Additional narrowing query based on the Date type field. The query criterion must be defined as a range, i.e. "from to". The criterion is written as a range of milliseconds representing the date¹.

tip

It is worth remembering that the best query effect will be obtained by adding a condition for the field that was indicated as the basis for aggregation in the dateFieldsName request parameter.

Example criterion for limiting results to cases created in the range from 1999-12-31 to 2019-06-31 21:26:34.143

"mrc_createDate:[946681200000 TO 1564608394143]"

page


Parameter name	page
Type	`MrcPage`
Required?	Yes

Definition of the result page object to fetch. Indicates which search result page to fetch.

Example, first page of 10 results

{
  "size": 10,
  "number": 1
}

resultTypeName


Parameter name	resultTypeName
Type	`String`
Required?	No

The name of the type to represent the grouping result.

Warning

The type (case type definition) with the given name must be defined in the HgDB database.

Example of undefined value

null

resultPkPropertyName


Parameter name	resultPkPropertyName
Type	`String`
Required?	No

Name of the field with a unique value pointing to the row/list item in the result. Optional parameter. If not defined, the field will take the value "rowId".

Example of undefined value

null

Example of method request

Example of JSON request

{
  "context": {
    "appName": "mercury-ws-app",
    "appVersion": "1.0",
    "userName": "anonymous",
    "comment": null,
    "maxResults": 20,
    "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": false,
    "cacheUsage": "TO_USE",
    "httpResponseCacheUsage": "NONE",
    "defaultLuceneSortClause": null,
    "viewDefinition": null
  },
  "query": "status:A",
  "groupByClause": "trunc(mrc_createDate, DD) as createDatePerMonth, count(1) as count",
  "filterClause": null,
  "additionalDateRange": "createDate:[1515279600000 TO 1515366000000]",
  "page": {
    "size": 60,
    "number": 1
  },
  "resultTypeName": null,
  "resultPkPropertyName": null
}

More examples of using the groupByQuery(POST) method can be found in Data aggregation examples.

Method response parameters

errorCode - Error code. When the operation is successful, it takes the value null . Information about the data contained in the value of this parameter can be obtained from the article What does the value of the "errorCode" field mean?
errorMessage - Error message. When the operation is successful, it takes the value null.
queryStats - Query execution statistics in the form of a [QueryStats] object(/docs/API/PagedResult/#querystats).

Example of aggregate query execution statistics
{
  "allExecTime": 1339,
  "mainPrepareTime": 11,
  "collValidationTime": 1,
  "mainCollExecTime": 0,
  "mainCollExecCount": 47281,
  "subCollExecTime": 4,
  "subCollExecCount": 8,
  "subExecLeve1Time": 1337,
  "subExecLeve2Time": 0,
  "subExecLeve3Time": 0,
  "subExecLeve4Time": 0,
  "initResultSetTime": 6,
  "resultSetSize": 3
}

dto - The result of the service operation in the form of a PagedResult object. The list element is an object in the form of a case definition (see the articles on data representation: Case as any object and Case as a universal MRC object). This object does not represent the actual case stored in the HgDB database.

Note

The case object contains the mrcCaseHeader field of type CaseHeader, which has two important fields: typeCode and pkPropertyName. These fields take the value of the aggregate query as a result:

typeCode - takes the generated value of QueryResult_<random_value> e.g. QueryResult_d5fd73da if the input argument of the service resultTypeName is not set. Otherwise, it takes the value of the input argument resultTypeName (the result will be transformed to the given type).
pkPropertyName - takes the value of "rowId" if the input argument of the service resultPkPropertyName is not set. Otherwise, it takes the value of the input argument resultPkPropertyName.

Full example of presenting the response object in JSON form

{
  "errorCode": "",
  "errorMessage": "",
  "queryStats":    {
    "allExecTime": 1339,
    "mainPrepareTime": 11,
    "collValidationTime": 1,
    "mainCollExecTime": 0,
    "mainCollExecCount": 47281,
    "subCollExecTime": 4,
    "subCollExecCount": 8,
    "subExecLeve1Time": 1337,
    "subExecLeve2Time": 0,
    "subExecLeve3Time": 0,
    "subExecLeve4Time": 0,
    "initResultSetTime": 6,
    "resultSetSize": 3
  },
  "dto":    {
    "resultSize": 3,
    "result":       [
      {
        "mrcCaseHeader":             {
            "typeCode": "QueryResult_d5fd73da",
            "dirty": false,
            "pkPropertyName": "rowId"
        },
        "rowId": "ef634915-90d7-437f-8415-fd96759c9774",
        "id": "2017.q1",
        "max": 132044.34,
        "sum": 374730.02,
        "avg": 124910.0066666667,
        "min": 120344.34
      },
      {
        "mrcCaseHeader":             {
            "typeCode": "QueryResult_d5fd73da",
            "dirty": false,
            "pkPropertyName": "rowId"
        },
        "rowId": "bfa12932-d8c8-40ba-bdf1-50dbaf109490",
        "id": "2018.q2",
        "max": 155344.34,
        "sum": 294188.68,
        "avg": 147094.34,
        "min": 138844.34
      },
      {
        "mrcCaseHeader":             {
            "typeCode": "QueryResult_d5fd73da",
            "dirty": false,
            "pkPropertyName": "rowId"
        },
        "rowId": "b248b739-4d1c-429f-a883-e0a7892fa30a",
        "id": "2018.q1",
        "max": 132344.34,
        "sum": 375033.02,
        "avg": 125011.0066666667,
        "min": 120344.34
      }
    ],
    "message": "ALL",
    "executionTime": 1523,
    "currentPageInfo":       {
      "size": 60,
      "number": 1
    },
    "firstPageInfo":       {
      "size": 60,
      "number": 1
    },
    "previousPageInfo": {},
    "nextPageInfo": {},
    "lastPageInfo":       {
      "size": 60,
      "number": 1
    },
    "allPages": [      {
        "size": 60,
        "number": 1
    }],
    "pagingParams":       {
      "offset": 0,
      "cursorOfPage": 0,
      "maxPageSize": 60,
      "maxCount": 100000,
      "pageSize": 60,
      "page":          {
        "size": 60,
        "number": 1
      },
      "valid": "true",
      "isReadOnly": "false"
    }
  }
}

sarchByQuery

The service implementation allows for data search requests in the HgDB database using Lucene queries. Searches can be performed by any Lucene index field defined in the HgDB database.

Method request parameters

context


Parameter name	context
Type	`Context`
Required?	Yes

The request context. You can find more about the context in the article SOAP/REST service request context.

Example of a context object

{
  "userName": "anonymous",
  "userFullName": null,
  "locale": null,
  "timeZone": null,
  "maxResults": 1,
  "currentRole": "anonymous",
  "userRoles": null,
  "sourceOfRequest": null,
  "maxDepthResult": 1,
  "decodeResult": "DECODE_DATE_AND_LOB",
  "ignoreCaseHeaderInResponse": false,
}

query


Parameter name	query
Type	`String`
Required?	Yes

A search query for the Lucene index. See the Search queries section for a description of how to construct queries.

Example query for search on mrc_Case_id field for two ranges of its values

"mrc_Case_id:[806000 TO 806525] mrc_Case_id:[706525 TO 709000]"

page


Parameter name	page
Type	`MrcPage`
Required?	Yes

Definition of the result page object to be fetched. Indicates which search result page to fetch.

Example, first page of 10 results

{
  "size": 10,
  "number": 1
}

sortClause


Parameter name	sortClause
Type	`String`
Required?	No

Sorting clause of the search result defined as concatenation of the field name with the direction acronym (ASC - ascending, DESC - descending).

Warning

There is a limitation of defining the result sorting direction to one field.

Example of ascending sorting by a field named grParticipantFullname

"grParticipantFullname ASC"

additionalDateRange


Parameter name	additionalDateRange
Type	`String`
Required?	No

Additional narrowing query based on a field of type Date. The query criterion must be defined as a range, i.e. "from to". The criterion is written as a range of milliseconds representing the date¹.

Example of a criterion to limit results to cases created in the range from 1999-12-31 to 2019-06-31 21:26:34.143

"mrc_createDate:[946681200000 TO 1564608394143]"

resultTypeName


Parameter name	resultTypeName
Type	`String`
Required?	No

The name of the type that the grouping result should represent.

Attention

The type (case type definition) with the given name must be defined in the HgDB database.

Example where all search results will be transformed to case type ElixAddress

"ElixAddress"

Method request example

JSON request example

{
  "context": {
    "appName": "mercury-ws-app",
    "appVersion": "1.0",
    "userName": "admin",
    "maxResults": 10000,
    "queryTimeout": 2147483647,
    "locale": "pl_PL",
    "timeZone": "Europe/Warsaw",
    "eager4omdBuilder": "true",
    "trustedData": false,
    "currentRole": "mrc-user",
    "userRoles": ["mrc-user","mrc-useradmin"],
    "sourceOfRequest": "USER_DEV.localhost",
    "directRequest": false,
    "formats": {
      "date.format.long": "dd-MM-yyyy HH:mm:ss XXX"
    },
    "ignoreAlternateFields": true,
    "decodeResult": "DATE_AND_LOB",
    "maxDepthResult": 1,
    "decodeRequest": "DATE_AND_LOB",
    "ignoreCaseHeaderInResponse": true,
    "cacheUsage": "REFRESH",
    "httpResponseCacheUsage": "NONE"
  },
  "query": "mrc_Case_id:[806000 TO 806525] mrc_Case_id:[706525 TO 709000]",
  "page": {
    "size": 10,
    "number": 1
  },
  "sortClause": "mrc_Case_id DESC",
  "additionalDateRange":  "mrc_createDate:[946681200000 TO 1564608394143]",
  "resultTypeName": "ElixAddress"
}

Method response parameters

The response accepts the following output parameters:

errorCode - Error code. When the operation is successful, it takes the value null . Information about the data contained in the value of this parameter can be obtained by reading the article What does the value of the "errorCode" field mean?
errorMessage - Error message. When the operation is successful, it takes the value null.
queryStats - Query execution statistics in the form of a [QueryStats] object(/docs/API/PagedResult/#querystats).

Example of aggregate query execution statistics
{
  "allExecTime": 1339,
  "mainPrepareTime": 11,
  "collValidationTime": 1,
  "mainCollExecTime": 0,
  "mainCollExecCount": 47281,
  "subCollExecTime": 4,
  "subCollExecCount": 8,
  "subExecLeve1Time": 1337,
  "subExecLeve2Time": 0,
  "subExecLeve3Time": 0,
  "subExecLeve4Time": 0,
  "initResultSetTime": 6,
  "resultSetSize": 3
}

dto - The result of the service operation in the form of a PagedResult object. The list elements are represented by the type defined as the input argument of the resultTypeName method - this allows for unification of data in rows and allows for presentation in a table. If the resultTypeName parameter is not set (i.e. takes the value null), then the case objects take the form in which they are stored (as is).

Full example of presentation of the response object in JSON

{
  "errorCode": "",
  "errorMessage": "",
  "queryStats":    {
    "allExecTime": 39,
    "mainPrepareTime": 44,
    "collValidationTime": 0,
    "mainCollExecTime": 0,
    "mainCollExecCount": 2,
    "subCollExecTime": 0,
    "subCollExecCount": 0,
    "subExecLeve1Time": 39,
    "subExecLeve2Time": 0,
    "subExecLeve3Time": 0,
    "subExecLeve4Time": 0,
    "initResultSetTime": 0,
    "resultSetSize": 2
  },
  "dto":    {
    "resultSize": 2,
    "result":       [
            {
      "mrcCaseHeader":             {
          "caseId": 808300,
          "groupId": 301039,
          "typeId": 5014,
          "typeCode": "Month",
          "status": "A",
          "previousVersionId": 808298,
          "rootVersionId": 808298,
          "priceValue": 0,
          "storeCount": 1,
          "storeId": 2507,
          "createDate": "21-01-2019 18:51:05 +01:00",
          "createdBy": "slawas",
          "lastModifyDate": "28-04-2021 12:47:21 +02:00",
          "lastModifiedBy": "slawas",
          "modifyComment": "SOAP request",
          "createdByRoleName": "mrc-user",
          "lastModifiedByRoleName": "CKBPM-Team",
          "className": "Month",
          "objectID": "?.Month",
          "rootVersionContextID": "?",
          "version": "1015949",
          "dirty": false,
          "pkPropertyName": "id"
      },
      "id": 2018.04,
      "displayName": "kwiecień'2018",
      "revenue": 127564.34,
      "costs": 155344.34,
      "vat": 73432.23
    },
            {
      "mrcCaseHeader":             {
          "caseId": 808289,
          "groupId": 301037,
          "typeId": 5012,
          "typeCode": "Quarter",
          "status": "A",
          "rootVersionId": 808289,
          "priceValue": 0,
          "storeCount": 1,
          "storeId": 2507,
          "createDate": "21-01-2019 15:05:54 +01:00",
          "createdBy": "slawas",
          "lastModifyDate": "21-01-2019 15:05:54 +01:00",
          "lastModifiedBy": "slawas",
          "modifyComment": "SOAP request",
          "createdByRoleName": "CKBPM-Team",
          "lastModifiedByRoleName": "CKBPM-Team",
          "className": "Quarter",
          "objectID": "?.Quarter",
          "rootVersionContextID": "?",
          "version": "50917",
          "dirty": false,
          "pkPropertyName": "id"
      },
      "status": "Closed",
      "id": "2018.Q1",
      "name": "Kwartał 1'2018",
      "months":             [
          {
            "mrcCaseHeader":                   {
              "caseId": 808792,
              "groupId": 301037,
              "typeId": 5014,
              "typeCode": "Month",
              "status": "A",
              "previousVersionId": 808292,
              "rootVersionId": 808292,
              "priceValue": 0,
              "storeCount": 1,
              "storeId": 2507,
              "createDate": "23-01-2019 08:55:57 +01:00",
              "createdBy": "slawas",
              "lastModifyDate": "23-01-2019 08:55:57 +01:00",
              "lastModifiedBy": "slawas",
              "modifyComment": "SOAP request",
              "createdByRoleName": "CKBPM-Team",
              "lastModifiedByRoleName": "CKBPM-Team",
              "subCaseReferenceId": 3712,
              "className": "Month",
              "objectID": "?.Month",
              "rootVersionContextID": "?",
              "version": "51411",
              "dirty": false,
              "pkPropertyName": "id"
            },
            "id": 2018.03,
            "displayName": "marzec'2018",
            "revenue": 120564.34,
            "costs": 120344.34,
            "vat": 23432.23
          },
          {
            "mrcCaseHeader":                   {
              "caseId": 808795,
              "groupId": 301037,
              "typeId": 5014,
              "typeCode": "Month",
              "status": "A",
              "previousVersionId": 808290,
              "rootVersionId": 808290,
              "priceValue": 0,
              "storeCount": 1,
              "storeId": 2507,
              "createDate": "23-01-2019 08:55:57 +01:00",
              "createdBy": "slawas",
              "lastModifyDate": "23-01-2019 08:55:57 +01:00",
              "lastModifiedBy": "slawas",
              "modifyComment": "SOAP request",
              "createdByRoleName": "CKBPM-Team",
              "lastModifiedByRoleName": "CKBPM-Team",
              "subCaseReferenceId": 3710,
              "className": "Month",
              "objectID": "?.Month",
              "rootVersionContextID": "?",
              "version": "51414",
              "dirty": false,
              "pkPropertyName": "id"
            },
            "id": 2018.01,
            "displayName": "styczeń'2018",
            "revenue": 124564.34,
            "costs": 122344.34,
            "vat": 23432.23
          },
          {
            "mrcCaseHeader":                   {
              "caseId": 808796,
              "groupId": 301037,
              "typeId": 5014,
              "typeCode": "Month",
              "status": "A",
              "previousVersionId": 808291,
              "rootVersionId": 808291,
              "priceValue": 0,
              "storeCount": 1,
              "storeId": 2507,
              "createDate": "23-01-2019 08:55:57 +01:00",
              "createdBy": "slawas",
              "lastModifyDate": "23-01-2019 08:55:57 +01:00",
              "lastModifiedBy": "slawas",
              "modifyComment": "SOAP request",
              "createdByRoleName": "CKBPM-Team",
              "lastModifiedByRoleName": "CKBPM-Team",
              "subCaseReferenceId": 3711,
              "className": "Month",
              "objectID": "?.Month",
              "rootVersionContextID": "?",
              "version": "51415",
              "dirty": false,
              "pkPropertyName": "id"
            },
            "id": 2018.02,
            "displayName": "luty'2018",
            "revenue": 134564.34,
            "costs": 132344.34,
            "vat": 53432.23
          }
        ]
      }
    ],
    "message": "ALL",
    "executionTime": 361,
    "currentPageInfo":       {
      "size": 60,
      "number": 1
    },
    "firstPageInfo":       {
      "size": 60,
      "number": 1
    },
    "previousPageInfo": {},
    "nextPageInfo": {},
    "lastPageInfo":       {
      "size": 60,
      "number": 1
    },
    "allPages": [      {
      "size": 60,
      "number": 1
    }],
    "pagingParams":       {
      "offset": 0,
      "cursorOfPage": 0,
      "maxPageSize": 60,
      "maxCount": 10000,
      "pageSize": 60,
      "page":          {
        "size": 60,
        "number": 1
      },
      "valid": "true",
      "isReadOnly": "false"
    }
  }
}

explainPlanGroupByQuery

Page under construction

The page is under construction and does not contain all the information yet. Please be patient.

Complete the method description

explainPlanSearchByQuery

Page under construction

The page is under construction and does not contain all the information yet. Please be patient.

Complete the method description

Example page for fast date decoding from/to milliseconds: https://currentmillis.com/ ↩ ↩² ↩³

dateMetricQuery​

Method request parameters​

context​

dateFieldsNames​

duration​

query​

sortAscending​

additionalDateRange​

page​

Method request example​

Service Response​

fieldMetricQuery​

Method request parameters​

context​

fieldName​

loadTop​

filterClause​

sortAscending​

page​

Method request example​

Method response parameters​

groupByQuery​

Method request parameters​

context​

query​

groupByClause​

filterClause​

additionalDateRange​

page​

resultTypeName​

resultPkPropertyName​

Example of method request​

Method response parameters​

sarchByQuery​

Method request parameters​

context​

query​

page​

sortClause​

additionalDateRange​

resultTypeName​

Method request example​

Method response parameters​

explainPlanGroupByQuery​

explainPlanSearchByQuery​

Footnotes​

dateMetricQuery

Method request parameters

context

dateFieldsNames

duration

query

sortAscending

additionalDateRange

page

Method request example

Service Response

fieldMetricQuery

Method request parameters

context

fieldName

loadTop

filterClause

sortAscending

page

Method request example

Method response parameters

groupByQuery

Method request parameters

context

query

groupByClause

filterClause

additionalDateRange

page

resultTypeName

resultPkPropertyName

Example of method request

Method response parameters

sarchByQuery

Method request parameters

context

query

page

sortClause

additionalDateRange

resultTypeName

Method request example

Method response parameters

explainPlanGroupByQuery

explainPlanSearchByQuery

Footnotes