Using Advanced Filter in RESTful Web Services

JSON strings provide more flexibility to create more complex filter conditions in REST services. The JSON string is sent in the request body of the POST method, as the content type, set as, Content-Type=application/x-www-form-urlencoded.

The JSON filter condition is specified as a name-value parameter, using the key word, “filter” in the POST method body. The filter cannot be used in the header. If there is no filter in the HTTP POST request, specify, filter=EMPTY_FILTER. Filters are case sensitive.

The delimiter for IN clauses can be configured in the onedata.properties file in the onedata.webservice.rest.inclause.delimiter=; parameter. The default delimiter is a semicolon (;), for example, IN clause => (COL1, COL2) IN ((1,2) ; (3,4) ; (5,6)).

For more information about configuring OneData properties, see Administering webMethods OneData.

You cannot use REST parameters in the request body. To add additional parameters to the filter, you must specify them in the URL. For more information about REST parameters, see REST URL Parameters.

The following is an example of OneData response to a GET request with the Accept header as application/xml, content-type as application/x-www-form-urlencoded, and the following filters:

filter={
"criterion" : [{
"criterion" : [{
"name" : "EMP_ID",
"operator" : "IN",
value" : "3"
}
]
}
}
}

<EMP>
<datarow>
<EMP_ID>3</EMP_ID>
<EMP_NAME>EMP3</EMP_NAME>
<CONSTRAINT_COUNTRY_XML>IND</CONSTRAINT_COUNTRY_XML>
<CONSTRAINT_STATE>KARNATAKA</CONSTRAINT_STATE>
</datarow>
</EMP>

The following is an example of OneData response to a GET request with the Accept header as application/json, content-type as application/x-www-form-urlencoded, and the following filters:

filter={
"criterion" : [{
"criterion" : [{
"name" : "EMP_ID",
"operator" : "IN",
"value" : "3"
}
]
}
]
}

{
"EMP_7595": [
{
"EMP_ID": "3",
"EMP_NAME": "EMP3",
"CONSTRAINT_COUNTRY_XML": "IND",
"CONSTRAINT_STATE": "KARNATAKA"
}
]
}

To create a filter that has both and and or operators and to create nested conditions, use a JSON string as in the following example. To retrieve (ID=1 and NAME=AAA) OR (ID=2 and NAME=BBB), use the string:

filter = { "criterion" : [ { "criterion" : [ { "name" : "ID",
"operator" : "= ",
"value" : "1"
},
{ "name" : "NAME"
"operator" : "= ",
"value" : "AAA"
}
],
"operator" : "and"
},
{ "criterion" : [ { "name" : "ID",
"operator" : "=",
"value" : "2"
},
{ "name" : "NAME",
"operator" : "=",
"value" : "BBB"
}
],
"operator" : "and"
}
],
"operator" : "OR"
}

The following JSON string is an example of a conceptual object with a simple and filter. The filter is for the parent object, GEO_COUNTRY with child objects GEO_CITY and GEO_STATE. The filter returns records where the city name is AAA and the state name is BBB.

(
(
GEO_COUNTRY.GEO_STATE.GEO_CITY.GEO_CITY_NM=AAA
and
GEO_COUNTRY.GEO_STATE.GEO_STATE_NM=BBB
)
)
Corresponding JSON String:
{ "criterion" :
[
{
"criterion" :
[
{
"name" : "GEO_COUNTRY.GEO_STATE.GEO_CITY.GEO_CITY_NM",
"operator" : "=",
"value" : "AAA"
},
{
"name" : "GEO_COUNTRY.GEO_STATE.GEO_STATE_NM",
"operator" : "=",
"value" : "BBB"
}
],
"operator" : "and"
}
],
"operator" : ""
}

The following JSON string is an example of a data object with an and filter. The filter is for the data object, GEO_COUNTRY to return records where the ID equals 1 and the country name is AAA.

(
( GEO_COUNTRY_ID=1 and GEO_COUNTRY_NM=AAA
)
)
Corresponding JSON string:
{
"criterion" :
[
{
"criterion" :
[
{
"name" : "GEO_COUNTRY_ID" ,
"operator" : "=" ,
"value" : "1 "
} ,
{
"name" : "GEO_COUNTRY_NM" ,
"operator" : "=" ,
"value" : "AAA "
}
] ,
"operator" : "and "
}
] ,
"operator" : ""}

Data Object JSON String Example of an IN Clause in a Single Column with IS_Null AND LIKE Conditions

The following JSON string is an example of a filter in a data object with an IN clause for IS_NULL and LIKE conditions. The filter returns records in the GEO_COUNTRY object where the ID is 1, 2, 3, or 4, and the description is null and the country name ends with the letter a. In this example, the delimiter to separate column values in an IN clause is a semicolon, and is the default delimiter.

{ "criterion" :
[
{
"criterion" :
[
{
"name" : "GEO_COUNTRY_ID",
"operator" : "IN",
"value" : "(1; 2; 3; 4)"
},
{
"name" : "GEO_COUNTRY_DESC",
"operator" : "IS_NULL",
"value" : ""
},
{
"name" : "GEO_COUNTRY_NM",
"operator" : "=",
"value" : "$a"
}
],
"operator" : "and"
}
],
"operator" : ""}

The following JSON string is an example of a filter on a data object with an IN clause in multiple columns. The filter returns records in the GEO_COUNTRY object where the ID is 1, the country name is AAA, and the country description is AAA, or where the ID is 2, the country name is BBB, and the country description is BBB. In this example, the delimiter to separate column values in the IN clause is a semicolon, and is the default delimiter.

{ "criterion" :
   [
     {
       "criterion" :
         [
           {
             "name" : "(GEO_COUNTRY_ID, GEO_COUNTRY_NM, GEO_COUNTRY_DESC )",
             "operator" : "IN",
             "value" : "((1, AAA, AAA ); (2 , BBB, BBB ))"
           }
         ],
       "operator" : ""
     }
   ],
"operator" : ""
}

The following JSON string is an example of a filter on a data object with a nested filter. The filter returns records in the GEO_COUNTRY object where the country name starts with the letter a (required) and either the country ID is the value 1 or 2 or the country description is null. In this example, the delimiter to separate column values in the IN clause is a semicolon, and is the default delimiter.

{"criterion" :
[
{
"criterion" :
[
{
"name" : "GEO_COUNTRY_NM",
"operator" : "LIKE",
"value" : "A$"
}
],
"complexCriterion" :
[
{
"criterion" :
[
{
"name" : "GEO_COUNTRY_ID",
"operator" : "IN",
"value" : "(1 ; 2)"
},
{
"name" : "GEO_COUNTRY_DESC",
"operator" : "IS_NULL",
"value" : ""
}
],
"operator" : "or"
}
],
"operator" : "and"
}
],
"operator" : ""
}

The following table describes the syntax to use for record values based on the data type of the column.

Data Type of Column

Syntax

Date

In JSON strings, you can use date filters in the format Column Name > Current Date %2B/-X. Here:

Column Name is the column name in table.

Current Date is the current date of the database.

%2B/- is the operator %2B (representing +) or -.

X is any integer.

Example:

CREATE_DT > CURRENTDATE-2 returns records created two days ago from the current date.

DOB > CURRENTDATE%2B 3 returns users whose date of birth is three days from the current date.