Filtering and URI parameters

The Model file supports a number of query operators that can be used to filter results. When specifying the operators in column definition or URI, the filtering is pushed down to the data source, instead of being handled by the driver. This results in more efficient processing of queries and improved performance. You can specify one or more operators in the column definition using the set of Model file properties in the "Query operator syntax" table.

"<column_name>":{
   "#type":"<data_type>",
   "<operator>":"<uri_parameter>",
   "#default":"<default_parameter>",
   "#virtual":true
}

where:

data_type

specifies the data type to which the column is mapped.

Note: If the data type is a date, time, timestamp, you can determine the format used by specfiying a Java SimpleDateFormat string after a comma. See "Date, time, and timestamp formats" for details.

operator

specifies the property that corresponds to the query operator that you want to used to filter results. This value can be #eq, #lt, #gt, #le, #ge, #ne, or #in. See "Query operator syntax" table for details.

uri_property

specifies the name of the URI property to be filtered by the operator.

default_param

(optional) specifies the default parameter when the URI property to be filtered is a parameter. Some REST services require certain parameters in order to operate. Typically, this would require including a WHERE <parameter>=<value> in a SQL statement. However, when specifying the default parameter, the driver will push down this value when it’s not included in the statement.

Examples

The following demonstrates an entry using filters for the orderdate column.

{
    "Orders":{
        #path:"[
            "/orders/{orderid}",
            "/customer/{custid}/orders",
            "/orders"
        ],
        "orderid":"Varchar(256)",
        "custid":"Varchar(256)",
        "orderdate":{
           "#type":"Date",
           "#eq":"date",
           "#gt":"after",
           "#lt":"before"
        }
    }         
}       

The following demonstrates example queries to use against the preceding entry along with corresponding example URIs that can be issued as an alternative to specifying filters in the column definition.

• The following query returns results for all the orders that occurred on 2020-01-01:

  SELECT * FROM ORDERS WHERE ORDERDATE = '2020-01-01' 

  Instead of using the column definition, you can also push down filtering for this query using the following URI:

  https://www.example.com/ORDERS?DATE=2020-01-1

• The following query returns all the orders that occurred after 2020-01-01:

  SELECT * FROM ORDERS WHERE ORDERDATE > '2020-01-01' 

  Instead of using the column definition, you can also push down filtering for this query using the following URI:

  https://www.example.com/ORDERS?AFTER=2020-01-01

• The following query returns results for all the orders that occurred before 2020-01-01:

  SELECT * FROM ORDERS WHERE ORDEREDATE < '2020-01-01'

  Instead of using the column definition, you can also push down filtering for this query using the following URI:

  https://www.example.com/ORDERS?BEFORE=2020-01-01