Predicates are data structures that allow complex queries to be passed through DTS towards various Connectors in order to extract records or create streams. All record requests through DTS will internally resolve to Predicates, though the direct interface may sometimes wrap the request in simpler parameters for ease of use.
Predicates are also used by DTS to define fundamental Collection Filters and to design relationships within Aggregates.h
Structure
DTS Predicates can be found in various endpoints either as Java Objects or JSON or XML data structures. We will explore their structure in JSON format for simplicity:
{
"operatorName": "",
"attributeName": "",
"attributeValue": "",
"negated": false,
"leftPredicate": {
// another predicate
},
"rightPredicate": {
// another predicate
}
}
operatorName |
The name of the operator to be used either between attributeName and attributeValue, or between leftPredicate and rightPredicate, depending on the operator. |
attributeName |
The name of the attribute that the Predicate will create a clause for |
attributeValue |
The comparative value for attributeName |
negated |
Whether the Predicate should be negated |
leftPredicate |
Used in compound Predicates. Specifies the first predicate that should be combined using operatorName. |
rightPredicate |
Used in compound Predicates. Specifies the second predicate that should be combined using operatorName. |
The types of all of the Predicate's fields is String, with the exception of "negated", which is a boolean
To pass a number-type attributeValue, into the predicate, simply use the standard String representation (e.g.: 34 -> "34", 100.29 -> "100.29", etc.)
Certain expressions can also be passed through the attributeName and attributeValue fields, in order to provide another layer of query customization
The exact limits of this feature depend on the targeted system. You can find a general description of it below and individual implementation limits in the "Limitations" section of each Connector category.
Operators
The operators, specified using the operatorName field of the Predicate, control the actual function of the Predicate. The following operators are known to DTS:
operatorName |
Function |
Details |
eq |
Equals |
Creates a clause which checks if attributeName equals attributeValue |
like |
Matches |
Creates a clause which checks if attributeName matches attributeValue (may be a regex or some other kind of matching pattern, depending on the targeted system) |
gt |
Greater Than |
Creates a clause which checks if attributeName is greater than attributeValue |
gteq |
Greater Or Equal Than |
Creates a clause which checks if attributeName is greater than or equals attributeValue |
lt |
Lesser Than |
Creates a clause which checks if attributeName is lesser than attributeValue |
lteq |
Lesser or Equal Than |
Creates a clause which checks if attributeName is lesser than or equals attributeValue |
and |
And |
Combines leftPredicate and rightPredicate using an AND operator |
or |
Or |
Combines leftPredicate and rightPredicate using an OR operator |
The negated field value determines whether the entire result of the Predicate should be negated, thus making the creation of complementary operators possible:
eq + negated = not-Equals
like + negated = not-Matches
and + negated = NAND
or + negated = NOR
Passing Expressions
Examples
{ "operatorName": "eq", "attributeName": "SURNAME", "attributeValue": "Smith", "negated": false } |
This Predicate will create a clause that will be true for all records in the target collection where the value of the SURNAME field is exactly equal to "Smith" |
{ "operatorName": "like", "attributeName": "SURNAME", "attributeValue": "%son", "negated": false } |
This Predicate will create a clause that will be true for all records in the target collection where the value of the SURNAME field ends with "son" (e.g. "Johnson", "Robertson", etc.)
|
{ "operatorName": "lteq", "attributeName": "AGE", "attributeValue": "34", "negated": false } |
This Predicate will create a clause that will be true for all records in the target collection where the value of the AGE field is less than or equal to 34 |
{ "operatorName": "eq", "attributeName": "CITY", "attributeValue": "New York", "negated": true } |
This Predicate will create a clause that will be true for all records in the target collection where the value of the CITY field is not equal to "New York" |
{ "operatorName": "and", "negated": false, "leftPredicate": { "operatorName": "eq", "attributeName": "SURNAME", "attributeValue": "Smith", "negated": false }, "rightPredicate": { "operatorName": "lteq", "attributeName": "AGE", "attributeValue": "34", "negated": false } } |
This Predicate will create a clause that will be true for all records in the target collection where the value of the SURNAME field is exactly equal to "Smith" and the value of the AGE field is less than or equal to 34 |
{ "operatorName": "and", "negated": false, "leftPredicate": { "operatorName": "eq", "attributeName": "CITY", "attributeValue": "New York", "negated": true }, "rightPredicate": { "operatorName": "like", "attributeName": "TO_CHAR(DOB)", "attributeValue": "%-12-%", "negated": false } } |
This Predicate will create a clause that will be true for all records in the target collection where the value of the CITY field is not equal to "New York" and the value of the DOB field after transforming it to a String, contains "-12-" within it (i.e. people that aren't from New York, but were born in December).
|