Query API
Introduction
The Query Application Programming Interface (API) enables users to query Oracle Health Insurance applications, using a RESTful webservice. The API provides a full Query Domain Specific Language (DSL) based on JSON/XML.
This integration point accepts a POST method with a payload containing the query parameters on the following URL:
http://[hostName]:[portNumber]/[api-context-root]/generic/claims/search
The payload of the POST request is structured as follows:
{
"resource" : {
"q" : "<query string>",
"offset" : "<page number>",
"limit" : "<page size>",
"orderBy": "<sort criteria>",
"totalResults": <total result count>
"groupBy": "<group by criteria>",
"aggregate": "<aggregate criteria>"
}
}
The resource
defines the starting point of the query. The query
definition is restricted to those data elements that are accessed from the resource
.
The parameters that make up the query are described in the following table:
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. To retrieve more records, 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 are 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 is 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 attribute also. Alias Name is mandatory to be specified. Multiple aggregate criteria is specified. Please note that ordering on those aggregate functions is not supported. |
aggregate=sum(totalResults):sumTotalResults,avg(salary):averageSalary |
The aggregate
parameter supports the following functions:
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. for example, 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 following table shows how the Query API, described on this page, maps to the database SQL:
Structured Query Language | Query API equivalent |
---|---|
SELECT |
fields |
FROM |
resource name |
WHERE |
q |
ORDER BY |
orderBy |
LIMIT |
limit/offset |
GROUP BY |
groupBy |
AGGREGATE |
aggregate |
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
The grammar is enforced and verified by a query statement parser.
The query string includes a series of expressions, concatenated with the AND and OR operators. The following payload is an example of a complex search expression on the claims
resource.
{
"resource" : {
"q" : "startDate.gte('2015-01-01').and.(endDate.eq(null).or.endDate.lte('2015-12-12'))
}
}
Each expression denotes an operation on an object model field name. The following operations are supported.
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 must 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 must 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") |
All operators that handle character strings, such as the like
and likeic
operators, are insensitive to diacritics.
Example Queries
Field names are 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, the collection resource and the query string would be:
{
"resource" : {
"q" : "brand.code.eq('ABC')"
}
}
An outer join on a field name is specified concatenating (+)
to the field name. Consider the following example:
{
"resource" : {
"q" : "person.bloodGroup(+).code.eq('A')"
}
}
You may combine multiple expressions into a single complex expression. Expressions are grouped by round brackets and are connected through either an OR or an AND operator. Consider the following examples of complex search expressions:
{
"resource" : {
"q" : "(endDate.eq(null).or.endDate.lt(2015-12-15)).and.code.eq('ABC')"
}
}
This query selects all policy enrollment products with an end date that is either not specified or before the 15th of December 2015, and with a code that has value "ABC".
{
"resource" : {
"q" : "code.eq('9999999999').and.flexCodeSystem.code.eq('US_PROVIDER')"
}
}
This query selects all providers with code "9999999999" and flexCodeSystem "US_PROVIDER"
{
"resource" : {
"q" : "brand.code.eq('TOP').and.OICPaid.eq(12)"
}
}
This query selects all claims with brand code "TOP" and dynamic field OICPaid with value 12
{
"resource" : {
"q" : "code.like('CLA%')"
}
}
This query selects all claims where code starts with the characters "CLA".
{
"resource" : {
"q" : "code.nin('CLA_2_1','CLA_2_2')"
}
}
This query selects all claims, except those with the code value 'CLAI_2_1' or "CLA_2_2".
{ "resource" : {
"limit" : "2",
"q" : "code.like('CLA%')",
"orderBy":"code"
}
}
This query selects all claims where code starts with the characters "CLA". The results are orderd by code and are returned two at the time.
The following is an example of a possible response:
{
"resource" : {
"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 only contains the attributes grouped on and the value of the aggregate functions that are asked with their alias names.
The following example shows a query summing the total covered amount per member.
{
"resource" : {
"groupBy" : "servicedMember.code",
"aggregate" : "sum(totalCoveredAmount_amount):coveredAmount"
}
}
The following payload is an example of a possible response:
{
"resource" : {
"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"
}
]
}
}
}
}
Oracle Insurance Gateway
The Oracle Insurance Gateway uses the Query DSL described on this page to extract information out of any Oracle Health Insurance application.
In order to allow for configurations to substitute parameters in the 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.