Filter expressions

Introduction

Filter expressions are used to find data objects in the database. Their basic principle is to define a comparison. A comparison consists of a path, an operator and a value, where

  • path: points to a property of the entity. Traversing of the object tree is supported and is indicated by a single dot "." (e.g. customer.name)

  • operator: indicates the operation that is applied to the comparison

  • value: specifies the value to which the comparison is applied. It is highly dependent on the type of the property and operation

Note: A comparison is always surrounded by single back ticks. They can be grouped by parenthesis and joined using logical operators.

Defining the path

In general, the path to an object is always relative to the Entity which is going to be queried.

Traversing within the object tree can be done via concatenating the names of the properties with a single dot ".". Despite the modelled properties, there are always two artificial properties available:

  • _type: The type describes the type of the entity. Its value is given as fully qualified entity name, e.g. core: MyEntity

  • _id: The id describes the internal id of the entity

For the property type Currency there are also two suffixes available (amount, currency), which allow the specification whether the amount value or the currency code of the property should be compared. The suffix is concatenated with #. The suffixes are constant strings.

For the property type Localized Text, there is also a suffix available. This value is concatenated by using # followed by a string, that points out to the locale value, that should be compared. Example: #en

Operators

Operators can be expressed through their symbols as well as through their alias.

For combining different filter criteria, the logical operators AND and OR can be used.

NameSymbolAliasDescription
EQ===eq=Checks for equality between the value of the given property and the specified value.
NEQ!==neq=Checks for inequality between the value of the given property and the specified value.
TEXT_STARTS_WITH^*=tsw=Checks if the value of the given property starts with the specified value. The check is not done case-sensitive.
TEXT_ENDS_WITH*$=tew=Checks if the value of the given property ends with the specified value. The check is not done case-sensitive.
TEXT_CONTAINS**=tco=Checks if the value of the given property contains the specified value. The check is not done case-sensitive.
LT<=lt=Checks if the value of the given property is lower than the specified value.
LTE<==lte=Checks if the value of the given property is lower or equal than the specified value.
GTE> ==gte=Checks if the value of the given property is greater or equal than the specified value.
GT> =gt=Checks if the value of the given property is greater than the specified value.
SUBCLASS=*=sc=Checks if the value of the given property is a subclass of the specified value.
IN =in=Checks if the value of the given property is part of the specified array of strings. This check is case-sensitive.
CONTAINS =co=Checks if there exists one object in the list of objects at the given property, which fits to the given filter expression.
NEARBY =nb=Checks if the value of the given property is located in the specified location. The location is expressed by
Note: Not all operators are available for all types of property.

Supported operators per property type

According to the type of the property, that is denoted in the path of a comparison node, there are different operators, that are supported.

Property TypeAllowed OperatorsNotes
Text, Text/E-mail,Text/URLEQ, NEQ, TEXT_STARTS_WITH, TEXT_ENDS_WITH, TEXT_CONTAINS, INThere is no notion of a wildcard sign available for any of the values
Integer, DecimalEQ, NEQ, LT, LTE, GTE, GT
BooleanEQ, NEQ
CurrencyEQ, NEQCurrency cannot be searched without specifying a suffix (#amount resp. #currency)
Selection ElementEQ, NEQ, INThe key of the selection element must be given as comparison value
Date, TimestampEQ, NEQ, LT, LTE, GTE, GTThe value is passed as a string in format of ISO 8601 (YYYY-MM-DDThh:mm:ss.sssZ). The precision for the search is determined by the precision of the given value
Geo PointNB
Localized TextEQ, NEQ, TEXT_STARTS_WITH, TEXT_ENDS_WITH, TEXT_CONTAINSThe value can only be searched, if the locale of the property is specified by suffixing the property path with #locale, where the locale is given in java-Locale format, e.g. #en or #en_GB
Reference (single)No operators are available
Local Entity (list), External Reference (list), Reference (list)CONTAINSList values can be queried by specifying a CONTAINS operator. The given value itself is again a comparison. If there exists at least one object in the list that is matching the filter expression, it evaluates to true
_type (artificial property)EQ, NEQ, IN, SUBCLASSSearches for the entity type
_id (artificial property)EQ, NEQ, INSearches for the (internal) id of the entity

Defining values

To compare a given property's value via some operator, a value must be specified. According to the operator and the type of the property, different types of supplying values are allowed.

  • String-Value: A simple string, that may contain arbitrary characters. The value needs to be quoted by double quotes ". For passing the character " to the value, this character needs to be escaped by putting a backslash "" in front of the character. For passing the character \ to the value, this character needs to be written as \. Example: " my simple arbitrary string value, that even contains a " special escaped char."

  • String-Array-Value: List of strings in array-notation separated by a comma ",". Example:

  • Number-Value: A number that may be prefixed by a signum and also contain a decimal value, which is denoted by a single dot. Also, the scientific representation of numbers via e is allowed. Example: -23.14

  • Boolean-Value: A boolean value that can be true or false

  • Null-Value: Filtering for properties with no value is indicated by the (not quoted) keyword null

  • Location-Radius-Value: The value is a combination of the decimals: latitude, longitude and range. It will be accepted in the following format: . If the range is negative, this means that it is considered as a minimum distance. All three elements must be given. The range is the radius in meter around the given geoPoint in which the result must be

    • Example: // all elements within a range of 5000 m of the point

    • Example: // invalid value since longitude cannot be negative

    • Example: // minimum distance of 300 m of the given point

Overview of allowed operators, property types and values

Property TypeOperatorValue TypeExample
Text, Text/E-mail, Text/URLEQString-ValuemyStringProperty=="abc"
EQNull-ValuemyStringProperty == null
NEQString-Value, Null-ValuemyStringProperty != "abc"
INString-Array-ValuemyStringProperty =in=
TEXT_STARTS_WITHString-ValuemyStringProperty ^* "abc"
TEXT_ENDS_WITHString-ValuemyStringProperty =tew= "abc"
TEXT_ENDS_WITHString-ValuemyStringProperty ** "abc"
Integer, DecimalEQNumber-Value, Null-ValuemyIntegerProperty == 3
NEQNumber-Value, Null-ValuemyDecimalProperty != 3.12
LTNumber-ValuemyDecimalProperty < 4.0e+5
LTENumber-ValuemyDecimalProperty <= 4.0
GTENumber-ValuemyIntegerProperty >= 4
GTNumber-ValuemyIntegerProperty > 4
Currency (amount)EQNumber-ValuemyCurrencyProperty#amount == 12.22
NEQNumber-ValuesmyCurrencyProperty#amount != 12.23
LTNumber-ValuemyCurrencyProperty#amount < 4.0e+5
LTENumber-ValuemyCurrencyProperty#amount <= 4.0
GTENumber-ValuemyCurrencyProperty#amount >= 4
GTNumber-ValuemyCurrencyProperty#amount > 4
Currency (currency)EQNumber-ValuemyCurrencyProperty#currency == EUR"
NEQNumber-ValuemyCurrencyProperty#currency != "USD"
BooleanEQBoolean-Value, Null-ValuemyBooleanProperty==true
NEQBoolean-Value, Null-ValuemyBooleanProperty =neq= false
Selection elementEQString-Value, Null-ValuemySelectionElementProperty == "keyOfFirstElement"
NEQString-Value, Null-ValuemySelectionElementProperty == "keyOfFirstElement"
INString-Array-ValuemySelectionElementProperty =in=
Date, TimestampEQString-Value (formatted as date), Null-ValuemyDateProperty == "2020-03-28"
NEQString-Value (formatted as date), Null-ValuemyTimestampProperty != "2020-03-28T13:24:11"
LTString-Value (formatted as date)myDateProperty < "2020-03-28"
LTEString-Value (formatted as date)myDateProperty <= "2020-03-28"
GTEString-Value (formatted as date)myDateProperty >= "2020-03-28"
GTString-Value (formatted as date)myDateProperty > "2020-03-28"
Geo PointNBLocation-Radius-ValuemyGeoPointProperty ==
Localized TextEQString-ValuemyLocalizedProperty#en == "abc"
NEQString-ValuemyLocalizedProperty#en_US != "abc"
TEXT_STARTS_WITHString-ValuemyLocalizedProperty#ca_FR =tsw= "abc"
TEXT_ENDS_WITHString-ValuemyLocalizedProperty#de =tew= "abc"
TEXT_ENDS_WITHString-ValuemyLocalizedProperty#en ** "abc"
Local Entity (single), External Reference (single) Only usable within object tree traversionmyLocalEntity.myStringProperty == "abc"
Local Entity (list)CONTAINSFilter-Expressions-ValuemyLocalEntityList =co= (myStringPropertyOfLocalEntity == "abc")
External Reference (list), Reference (list)CONTAINSFilter-Expressions-ValuemyExternalReferenceList =co= (myStringPropertyOfExternalEntity == "abc")
_type (artificial property)EQString-Value (specifying the fully qualified type of the entity)myLocalEntity._type == "core:MyLocalEntity"
NEQString-Value (specifying the fully qualified type of the entity)_type == "core:MyRootEntity"
INString-Array-Value (specifying the fully qualified type of the entity)myLocalEntity._type =in=
SUBCLASSString-Value (specifying the fully qualified type of the entity)type =sc= "core:MyAbstractEntity"
_id (artificial property)EQString-ValuemyLocalEntity._id == "5b87e25d-035d-40aa-9209-b0c04b004686"
NEQString-Value_id != "5b87e25d-035d-40aa-9209-b0c04b004686"
INString-Array-ValuemyLocalEntity._id =in=

Grammar

Please find below a formal (simplified) description of the grammar, that is used for filters.

ROOT: NODE | '(' NODE ')';

NODE: LOGICAL | COMPARISON;
NODE_IN_BRACKET: LOGICAL_IN_BRACKET | COMPARISON_IN_BRACKET;

COMPARISON: COMPARISON_IN_BRACKET | COMPARISON_PLAIN;
COMPARISON_IN_BRACKET: '(' COMPARISON_PLAIN ')';
COMPARISON_PLAIN: PATH OPERATOR VALUE;

LOGICAL: LOGICAL_IN_BRACKET | LOGICAL_PLAIN;
LOGICAL_IN_BRACKET: '(' LOGICAL_PLAIN ')';
LOGICAL_PLAIN: NODE_IN_BRACKET LOGICAL_OPERATOR NODE_IN_BRACKET;

LOGICAL_OPERATOR: 'AND' | 'OR';
OPERATOR: '==' | '=eq=' | '!=' | '=neq=' | '<' | '=lt=' | '<=' | '=lte=' | '>=' | '=gt=' | '>' | '=gte=' | '=*' | '=sc=' | '=in=' | '=nb=' |'=co=' ;

PATH: PROPERTY_NAME_SUFFIX | PROPERTY_NAME_ARTPROP| PROPERTY_NAME_PATH | PROPERTY_ART_PROP;
PROPERTY_NAME_SIMPLE: [a-z]+ (_[A-Za-z0-9])+;
PROPERTY_NAME_PATH: PROPERTY_NAME_SIMPLE ('.' + PROPERTY_NAME_SIMPLE)*;
PROPERTY_NAME_ARTPROP: PROPERTY_NAME_PATH '.' PROPERTY_ART_PROP;
PROPERTY_NAME_SUFFIX: PROPERTY_NAME_PATH '#' PROPERTY_SUFFIX;
PROPERTY_SUFFIX: 'amount' | 'currency' | LOCALE;
PROPERTY_ART_PROP: '_id' | '_type';
LOCALE: [a-z][a-z] ('_' [A-Z][A-Z])?;

VALUE: STRINGVALUE | NUMBERVALUE | BOOLEANVALUE | NULLVALUE | NODEVALUE | GEOVALUE | STRINGARRAYVALUE;

STRINGVALUE: STRING_TOKEN;
STRING_TOKEN: '"' STRING_PRIMITIVE '"';
STRING_PRIMITIVE: CHAR_PRIMITIVE*;
CHAR_PRIMITIVE: CHAR_ESCAPE | CHAR_NORMAL;
CHAR_ESCAPE: '\"' | '\\';
CHAR_NORMAL: [^\"][^\\];

STRINGARRAYVALUE: '[' STRINGARRAYVALUE_ELEMENTS ']'
STRINGARRAYVALUE_ELEMENTS: STRING_TOKEN (',' STRING_TOKEN)*

NUMBERVALUE: NUMBER_PRIMITIVE;
NUMBER_PRIMITIVE: ('-')? [0-9]+ ('.' [0-9]+)? (('e'|'E')['+'|'-'](0-9)+)?

BOOLEANVALUE: 'true' | 'false';
NULLVALUE: 'null';

NODEVALUE: NODE_IN_BRACKET
GEOVALUE: '[' + NUMBER_PRIMITIVE + ',' + NUMBER_PRIMITIVE + ';' + NUMBER_PRIMITIVE +']';
Note: Please note that despite the syntactical rules outlined in the grammar, there are also semantic rules checked in addition.

Example usage within repository

// Access the repository of the specific root entity and choose find
// Find takes an object argument and a filter argument. The filter argument is represented as a string.

const rootEntityInstance = this.repo.nsacrnm.RootEntityIdentifier.find(FindConfigurationObj, Filter);

//Example
const rootEntityInstance = this.repo.nsacrnm.RootEntityIdentifier.find(
    { includeSubentities: true,
      limit: "2,20",
      sortBy: "customerID,DESC" },
     `name == "${name}"`
);

Example usage with the filter factory

The Filter Factory provides support to construct the filter string to be used while querying the Datastore Api.

There are 4 methods available in the filter factory:

comparison

This method has 2 signatures,

  • with 3 arguments for property type localized text where first argument is locale string. For example, to create the filter with locale string 'en_US'

    this.factory.filter.namespace.entity.myLocalizedProperty.comparison('en_US', '!=', 'abc'); // resulting string,  myLocalizedProperty#en_US != "abc"`
  • with 2 arguments for all other property type except localized text and entity list

    this.factory.filter.namespace.entity.myTextProperty.comparison('==', 'test'); // resulting string,  myTextProperty == "test"
    this.factory.filter.namespace.entity.myCurrencyProperty.amount.comparison('>=', '100'); // resulting string,  myCurrencyProperty#amount >= "100"

_and

This method returns filter string by adding logical operator AND to given condition.

this.factory.filter._and(condition1, condition2)

_or

This method returns filter string by adding logical operator OR to given condition.

this.factory.filter._or(condition1, condition2)

contains

This method is only exposed for entity list type of properties such as, local entity list, reference list and external list.

this.factory.filter.namespace.entity.myEntityListProperty.contains.myTextProperty.comparison('=eq=', 'test');

The filter factory provides type safe guidance to build and use the filter strings to make queries to the database instance.

This example shows usage of the filter factory methods:

const condition1 = this.factory.filter.namespace.entity.textProperty1.comparison('=in=', ['text1', 'text2']);
const condition2 = this.factory.filter.namespace.entity.booleanProperty1.comparison('==', true);

const filterExpression = this.factory.filter._and(condition1, condition2);
// can use OR using similar syntax this.factory.filter._or(cond1, cond2)
// filterExpression string: "((textProperty1=in=['text1', 'text2']) AND (booleanProperty1==true))"

const filteredInstance = await this.factory.namespace.RootEntity1.find({ includeSubEntities: true }, filterExpression);
Note: this.factory.filter will be available in Services, Commands and Operations (since this.repo method is also available in them). this.factory.filter will only be available for Root Entities but not for Entity or External Entity since this.repo does not exist in these types of entities.

Complex examples

Modelled entities:

cc:CreditCard <<root entity>>
- cardText: text
- cardType: selection element [MASTER, VISA, AMEX]
- owner: external reference to cc:Customer
- transactions: list of local entites of type cc:Transaction

  cc:Transaction <<abstract entity>>
- transactionName: localized text
- transactionTimestamp: timestamp

  cc:DefaultTransaction extends cc:Transaction
- details: text

  cc:GoldTransaction extends cc:Transaction
- rating: selection element

  cc:Customer <<external entity>>
- custName: text
- custNumber: integer

Query for credit cards with cardType MASTER or VISA:

const cards = await this.repo.cc.CreditCard.find(
    { includeSubEntities: true },
        `(cardType=in=["MASTER", "VISA"])`
);

Query for credit cards that belong to owner with custNumber 167671:

const custNumber = 167671;
const cards = await this.repo.cc.CreditCard.find(
    { includeSubEntities: false },
    `owner.custNumber == "${custNumber}"`
);

Query for credit cards, which do not have a cardText or which type is not AMEX:

const cards = await this.repo.cc.CreditCard.find(
    { includeSubEntities: true },
    `((cardText == null) OR (cardType != "AMEX"))`
);

Query for credit cards, which do have a transaction within the last 10 days:

const deadline = new Date();
deadline.setUTCDate(deadline.getUTCDate() - 10);
const cards = await this.repo.cc.CreditCard.find(
    { includeSubEntities: true },
    `(transactions =co= (transactionTimestamp >= ${deadline.toISOString()}))`
);

Query for credit card with the internal id 5b87e25d-035d-40aa-9209-b0c04b004686:

const card = await this.repo.cc.CreditCard.find(
    { includeSubEntities: true },
    `_id == "5b87e25d-035d-40aa-9209-b0c04b004686"`
);

Query for credit cards which have one transaction that contains "abc" (english) as transactionName and where the owner is "Black Cat":

const cards = await this.repo.cc.CreditCard.find(
    { includeSubEntities: true },
    `((owner.custName == "Black Cat") AND (transactions =co= (transactionName#en =tco= "abc")))`
);

Query for credit cards which have a transaction that is subclass of GoldTransaction:

const cards = await this.repo.cc.CreditCard.find(
    { includeSubEntities: true },
    `(transactions =co= (_type =sc= "cc:GoldTransaction"))`
);

Filter factory examples

Basic Implementation:

const filter1 = this.factory.filter.cc.CreditCard.cardName.comparison('=co=', ['James']);
const filter2 = this.factory.filter.cc.CreditCard.cardActiveFlag.comparison('==', true);

_id &_type as property of Credit Card:

const filter3 = this.factory.filter.cc.CreditCard._id.comparison('==', '18fecac3-9044-4e8b-a767-f427c3901e55');
const filter4 = this.factory.filter.cc.CreditCard._type.comparison('==', 'cc_CreditCard');

For currency property type:

const filter5 = this.factory.filter.cc.CreditCard.currency1.amount.comparison('==', '100');
const filter6 = this.factory.filter.cc.CreditCard.currency1.currency.comparison('==', 'USD');

For localized texts, here description property is of type localized text:

const filter7 = this.factory.filter.cc.CreditCard.description.comparison('ca_FR', '==', 'Canada');

Address as local Entity(single) or reference (single) or external reference (single):

const filter8 = this.factory.filter.cc.CreditCard.address.pincode.comparison('==', '93051');
Note: Currently only one level of local entity with its properties mentioned above is supported

Transactions as local entity List or reference list or external reference list:

The method contains which represents =co= operator is only available for these type of properties

const filter9 = this.factory.filter.cc.CreditCard.transactions.contains.transactionTimestamp.comparison('>=', new Date());
// resulting filter string: (transactions =co= (transactions.transactionTimestamp >= new Date()))