Query API

Introduction

The Query Application Programming Interface (API) enables users to execute queries - and get the corresponding results - using a RESTful webservice (HTTP API) that is available for all Oracle Health Insurance Components. The API provides a full Query Domain Specific Language (DSL) based on JSON/XML.

The URL to the query API is structured as follows:

http://[hostName]:[portNumber]/[api-context-root]/generic/{collection resource}?[query parameters]

"collection resource" defines the starting point of the query. The query definition is restricted to those data elements that can be accessed from the resource. It can either be the resource itself, or one of its linked and/or sub resources
The [query parameters] specify the query specifics, i.e., the search criteria, the desired search results, as well as the sorting and pagination of the results.

Query Parameters based search

The following table lists the parameters that are used to build a query. Multiple query parameters can be concatenated using the '&':

No	Parameter	Description	Example
1	q	This query parameter holds the search criteria. Multiple criteria are concatenated by the period character. The possibilities of the query DSL are explained in the next sections.	q=a1.eq(2).and.a2.lt(4).and.a3.in('v1','v2','v3')
2	offset	This parameter controls from which row number the results start. The first offset MUST be 0. The default value is 0. If offset is greater than the actual number of records, an empty list is returned.	offset=10 returns the results from the 11th row.
3	limit	This query parameter specifies the page size when the search results are paginated. The default value is 50, the maximum value is 200. If you need to retrieve more records you have to do multiple queries using different offsets.	limit=200, returns row number [offset] until row number [offset + limit - 1]. If offset = 10, returns row number 10 - 209.
4	orderBy	This query parameter holds the sorting criteria. Multiple sorting criteria can be given delimited by the comma character. Acceptable sort types are ASC and DESC. If the type is left unspecified the results are sorted ascending. The order in which the criteria are listed determines the order in which they are applied. Sorting allows 'nulls first' and 'nulls last' ordering.	orderBy=a:asc,b:desc nulls first
5	totalResults	This query parameter directs the Query API to return a count of the records for the search being executed irrespective of limit (page size). The default value of this parameter is "false".	totalResults=true
6	groupBy	This query parameter holds the group by criteria. Multiple grouping criteria can be specified.	groupBy=code,firstName
7	aggregate	This query parameter holds the aggregate criteria. The grammar for aggregate criteria is:<AggregateFunction(<AttributeName):<AliasName. Supported aggregate functions are listed in the table below. AttributeName can be a joined a attribute also. Alias Name is mandatory to be specified. Multiple aggregate criteria can be specified. Please note that ordering on those aggregate functions is not supported.	aggregate=sum(totalResults):sumTotalResults,avg(salary):averageSalary
	Concatenation	Multiple query parameters can be concatenated using the &	<hostname/claims?q=person.code.eq('C1625085-01')&orderBy=entryDate:desc&limit=2

Parameter

Description

Example

This query parameter holds the search criteria.

Multiple criteria are concatenated by the period character.

The possibilities of the query DSL are explained in the next sections.

q=a1.eq(2).and.a2.lt(4).and.a3.in('v1','v2','v3')

offset

This parameter controls from which row number the results start. The first offset MUST be 0. The default value is 0. If offset is greater than the actual number of records, an empty list is returned.

offset=10 returns the results from the 11th row.

limit

This query parameter specifies the page size when the search results are paginated. The default value is 50, the maximum value is 200. If you need to retrieve more records you have to do multiple queries using different offsets.

limit=200, returns row number [offset] until row number [offset + limit - 1]. If offset = 10, returns row number 10 - 209.

orderBy

This query parameter holds the sorting criteria.

Multiple sorting criteria can be given delimited by the comma character.

Acceptable sort types are ASC and DESC. If the type is left unspecified the results are sorted ascending.

The order in which the criteria are listed determines the order in which they are applied.

Sorting allows 'nulls first' and 'nulls last' ordering.

orderBy=a:asc,b:desc nulls first

totalResults

This query parameter directs the Query API to return a count of the records for the search being executed irrespective of limit (page size). The default value of this parameter is "false".

totalResults=true

groupBy

This query parameter holds the group by criteria. Multiple grouping criteria can be specified.

groupBy=code,firstName

aggregate

This query parameter holds the aggregate criteria. The grammar for aggregate criteria is:<AggregateFunction(<AttributeName):<AliasName. Supported aggregate functions are listed in the table below. AttributeName can be a joined a attribute also. Alias Name is mandatory to be specified. Multiple aggregate criteria can be specified. Please note that ordering on those aggregate functions is not supported.

aggregate=sum(totalResults):sumTotalResults,avg(salary):averageSalary

Concatenation

Multiple query parameters can be concatenated using the &

<hostname/claims?q=person.code.eq('C1625085-01')&orderBy=entryDate:desc&limit=2

Aggregate Function	Description
sum	Calculates the sum of the attribute over the group by criteria
avg	Calculates the average of the attribute over the group by criteria
count	Calculates the count of the attribute over the group by criteria. It is also possible to ask for the count of lines grouped over a criteria without specifying any attribute. e.g groupBy=lastName&aggregate=count():totalCount
var	Calculates the variance of the attribute over the group by criteria
std	Calculates the standard deviation of the attribute over the group by criteria
max	Calculates the maximum of the attribute over the group by criteria
min	Calculates the minimum of the attribute over the group by criteria

Aggregate Function

Description

sum

Calculates the sum of the attribute over the group by criteria

avg

Calculates the average of the attribute over the group by criteria

count

Calculates the count of the attribute over the group by criteria. It is also possible to ask for the count of lines grouped over a criteria without specifying any attribute. e.g groupBy=lastName&aggregate=count():totalCount

var

Calculates the variance of the attribute over the group by criteria

std

Calculates the standard deviation of the attribute over the group by criteria

max

Calculates the maximum of the attribute over the group by criteria

min

Calculates the minimum of the attribute over the group by criteria

The query DSL maps to the database query language SQL:

Query DSL Grammar

The grammar for the query string is defined as follows:

 q := expression
 expression := [[(]simple_expression | complex_expression[)]] -- round brackets used for grouping
 simple_expression := field_name.operator(value, value, ...)
 field_name := attribute[(+)]?[.field_name]* -- for joins
 operator:= eq | eqic | neq | lt | lte | gt | gte | in | bet | like | likeic | nin
 complex_expression := expression.complex_operator.expression
 complex_operator := and | or

For security reasons, the grammar is enforced and verified by a query statement parser, e.g. to prevent "SQL injection".

Note that

The query string is a combination of simple or complex expressions, concatenated with the AND or OR operators:
```
[source,text]
----
startDate.gte('2015-01-01').and.(endDate.eq(null).or.endDate.lte('2015-12-12'))
```
```
----
```
Simple expressions are denoted by simple operations on the object model field names. All Oracle Health Insurance Components include a user interface page, called 'View Objects' that enables a user to traverse the object model and retrieve the appropriate field names.
Simple operations supported are:

Operator	Description	Example
eq	Checks for equality, if the argument passed in is null, will check for isNull	name.eq('king')
eqic	Checks for equality ignoring the case.	name.eqic("king")
neq	Checks for non equality, if the argument passed in is null, will check for isNotNull	name.neq('john')
lt	Checks for less than	startDate.lt('2015-11-11')
lte	Checks for less than equal to	length.lte(30)
gt	Checks for greater than	length.gt(30)
gte	Checks for greater than equal to	startDate.gte('2015-11-11')
in	Checks the value is one of the given values.	code.in('AB','CD','XY')
bet	Checks if the value is between the passed in arguments.	age.bet(6,10)
like	Applies the like operation, supporting % and _ as wildcards. Note: the % has special meaning in URL encoding. The % sign should be entered as %25. It is also possible to pass in a second argument to specify an escape character, which can be put in front of a wildcard character to indicate that it should be treated literally, and not as a wildcard.	code.like("J%") finds codes starting with J code.like("b_g") finds "big" and "bug" code.like("G/%","/") finds codes starting with G where _ is taken literally The last example in a url with the special characters replaced by their % code: code.like(%22G/_%25%22,%22/%22)
likeic	Same as like, except it will now ignore the case.	name.likeic("j%") or name.likeic('g/_%','/')
nin	Checks for not in.	code.nin('AB','CD','XY')

Operator

Description

Example

Checks for equality, if the argument passed in is null, will check for isNull

name.eq('king')

eqic

Checks for equality ignoring the case.

name.eqic("king")

neq

Checks for non equality, if the argument passed in is null, will check for isNotNull

name.neq('john')

Checks for less than

startDate.lt('2015-11-11')

lte

Checks for less than equal to

length.lte(30)

Checks for greater than

length.gt(30)

gte

Checks for greater than equal to

startDate.gte('2015-11-11')

Checks the value is one of the given values.

code.in('AB','CD','XY')

bet

Checks if the value is between the passed in arguments.

age.bet(6,10)

Applies the like operation, supporting % and _ as wildcards. Note: the % has special meaning in URL encoding. The % sign should be entered as %25. It is also possible to pass in a second argument to specify an escape character, which can be put in front of a wildcard character to indicate that it should be treated literally, and not as a wildcard.

code.like("J%") finds codes starting with J

code.like("b_g") finds "big" and "bug"

code.like("G/%","/") finds codes starting with G where _ is taken literally

The last example in a url with the special characters replaced by their % code: code.like(%22G/_%25%22,%22/%22)

likeic

Same as like, except it will now ignore the case.

name.likeic("j%") or name.likeic('g/_%','/')

nin

Checks for not in.

code.nin('AB','CD','XY')

Field names can be simple direct attributes on the source, or can refer to the joined attributes using "." notation. For example to search on a claim where brand code is ABC:
```
[source,text]
----
brand.code.eq('ABC')
```
```
----
```

They can also specify if there is an outer join on the fieldName by using (+):

[source,text]
----
// Normal join
person.bloodGroup.code.eq('A')
// outer join
person.bloodGroup(+).code.eq('A')

----

Complex operations are a combination of simple expressions. Grouping of expressions is possible by using round brackets. For example, a search on a relation whose end date is either null or less than 15Dec2015 and code is 'ABC', looks like:
```
[source,text]
----
(endDate.eq(null).or.endDate.lt(2015-12-15)).and.code.eq('ABC')
```
```
----
```

Examples:

search all claims where brand.code='TOP'

 http://[hostName]:[portNumber]/[api-context-root]/generic/claims?q=brand.code.eq('TOP')

search all provider with code = '9999999999' and flexCodeSystem.code.eq('US_PROVIDER')

 http://[hostName]:[portNumber]/[api-context-root]/generic/providers?q=code.eq('9999999999').
                        and.flexCodeSystem.code.eq('US_PROVIDER')

search all claims with brand.code='TOP' and dynamic field OICPaid = 12

 http://[hostName]:[portNumber]/[api-context-root]/generic/claims?q=brand.code.eq('TOP').
                and.OICPaid.eq(12)

search all claims where code like 'CLA%'

http://[hostName]:[portNumber]/[api-context-root]/generic/claims?q=code.like('CLA%25')

search all claims where code not in ('CLAI_2_1','CLA_2_2')

http://[hostName]:[portNumber]/[api-context-root]/generic/claims?q=code.nin('CLA_2_1','CLA_2_2')

search all claims where brand.code='TOP' and claimStatusHistoryList.status.eq('ENTRY')

http://[localhost]:[portNumber]/[api-context-root]/generic/claims?q=brand.code.eq('TOP').and.claimStatusHistoryList.status.eq('ENTRY')

POST Request for search

The query string may be limited in length due to server or browser (Internet Explorer!) constraints. Queries that require more characters can be executed by sending the query as payload, using POST method. The query is posted using JSON as follows:

{
  "resource" : {
        "q"      : "<query string",
        "offset" : "<page number",
        "limit"  : "<page size",
        "orderBy": "<sort criteria",
        "groupBy": "<group by criteria",
        "aggregate": "<aggregate criteria"
  }
}

Consider an example where you want to search for all claims that have a claim.code like 'CLA%' with two results per page. The result consists only of the claim codes.

http://[hostName]:[portNumber]/[api-context-root]/generic/claims/search

{
   "resource"  : {
         "limit"  : "2",
         "q"      : "code.like('CLA%')",
         "orderBy":"code"
   }
}

The query could return the following payload (assuming ContentType=application/vnd.oracle.insurance.resource+json):

{
   "limit": "2",
   "hasMore": true,
   "count": "2",
   "items": [
      {
        "id": 48404,
        "code": "CLA_2_2",
        "objectVersionNumber": 3
      },
      {
        "id": 48439,
        "code": "CLA_2_20",
        "objectVersionNumber": 1
      }
    ],
    "links": {
      "link": [
        {
          "httpMethod": "POST",
          "rel": "self",
          "href": "http://[hostName]:[portNumber]/[api-context-root]/generic/claims/search",
          "searchResource": {
            "resources": {
              "resource": []
            },
            "resource": {
              "limit"  : "2",
              "orderBy": "code",
              "q"      : "code.like('CLA%')",
              "name"   : "claims",
            }
          }
        },
        {
          "httpMethod": "POST",
          "rel": "next",
          "href": "http://[hostName]:[portNumber]/[api-context-root]/generic/claims/search?limit=2&offset=2",
          "searchResource": {
            "resource" : {
              "limit"  : "2",
              "orderBy": "code",
              "q"      : "code.like('CLA%')",
              "name"   : "claims",
              "offset" : "2",
            }
          }
        }
      ]
    }
  }
}

For aggregate queries, the response payload will only contain the attributes grouped on and the value of the aggregate functions that are asked with their alias names.

Example: http://[hostName]:[portNumber]/[api-context-root]/generic/claims?groupBy=servicedMember.code&aggregate=sum(totalCoveredAmount_amount):coveredAmount

{
   "limit": "50",
   "hasMore": true,
   "count": "50",
   "items": [
      {
        "servicedMember.code": "CLA_2_2",
        "coveredAmount": 30000
      },
      {
        "servicedMember.code": "CLA_2_20",
        "coveredAmount": 400000
      }
    ],
    "links": {
      "link": [
        {
          "httpMethod": "GET",
          "rel": "self",
          "href": "http://[hostName]:[portNumber]/[api-context-root]/generic/claims?groupBy=code&aggregate=sum(totalCoveredAmount_amount):coveredAmount"

        },
        {
          "httpMethod": "GET",
          "rel": "next",
          "href": "http://[hostName]:[portNumber]/[api-context-root]/generic/claims?groupBy=code&aggregate=sum(totalCoveredAmount_amount):coveredAmount&limit=50&offset=50"
        }
      ]
    }
  }
}

Parameterized Values

Query API is utilized for collecting data for Oracle Health Insurance Integration Gateway through data collection step type Query and Extract. The configuration for these are done before the integration are actually invoked. In order to allow for configurations to substitute parameters in teh query expression, Query API also allows for parameterized values in the query expressions, like startDate.eq({startDate}). The value in the curly brackets is substituted with the integration invocation’s runtime parameters. If it cannot be substituted, the process fails.