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, that is, 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 |
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, for example, to prevent "SQL injection".
Note that
-
The query string is a combination of simple or complex expressions, concatenated with the AND or OR operators:
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 applications 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') |
-
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:
brand.code.eq('ABC')
-
They can also specify if there is an outer join on the fieldName by using (+):
// 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 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.