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.