SAS REST APIs: Filtering

Filtering is the application of a Boolean condition against a collection of resources in order to subset the collection to ony those resources for which the condition is true. (For those familiar with SQL, filtering is analogous to a SQL query with a WHERE clause.) SAS REST APIs support two forms of filtering: basic filtering and a filter query parameter. The two forms may be combined.

Consider, for example, a collection of report resources. Given the following members:

{
  "id": "4eb3b675-e107-4857-a8f4-51aa555ac7e7",
  "name": "Production Report",
  "description": "Production forecasts by region and market segment.",
  "createdBy": "dale",
  "creationTimeStamp": "2017-04-19T14:54:04.705Z",
  "modifiedBy": "dale",
  "modifiedTimeStamp": "2017-04-19T14:55:11.643Z",
  "links": [ ... ],
  "imageUris": {
    "icon": "/reports/icons/report.gif"
  },
  "version": 1
}

Basic filtering

Basic filtering allows selecting resources by matching one or more members of the resource to values passed as query parameters. For example, to find all reports created by the user dale, a basic filter is:

   GET /reports/reports?createdBy=dale

The names of members in the resource representation (such as description, name, createdBy, modifiedBy etc) are used as the name of the corresponding query parameters. The general form is

   GET /reports/reports?memberName0=value0&...&memberNamen=valuen

Basic filtering only supports exact matches of members or simple set containment. Note that quotes are not used around either the names or the string values when using basic filtering.

Some APIs support nested resources, so you can use dot notation to select a nested member, such as imageUris.icon to filter based on the icon member of the imageUris object in the above example resource.

When multiple basic query parameters are used, there is an implicit and operation. That is, all the basic filter values must match in order for a resource to be included in the response. For example, a request

   GET /reports/reports?createdBy=dale&name=Production%20Report

Some members are not well-suited to exact matching, such as the modifiedTimeStamp which is a date-time value with sub-second precision: ?modifiedTimeStamp=2017-04-19T14:55:11.643Z. For such requirements, you should use the filter parameter described below to select resources based on ranges, comparisons, and other selection criteria.

Basic set containment filtering

    GET /reports/reports?createdBy=dale|elaine|jules

    GET /reports/reports?createdBy=dale%7Celaine%7Cjules

Filtering with the filter query parameter

The filter query parameter provides a flexible way to subset the resources from collections by combining comparison and other functions.

   ?filter=filterExpression

• combine multiple expressions with logical operations (and, or, and, not)
• make comparisons using relational operators (eq, ne, lt, le, gt, ge)
• use other functions such as contains, startsWith, in, match, etc.

The simple (and somewhat useless) filter ?filter=true evaluates to true for all items in the collection — this is the default behavior if no filter is provided.

Figure 1: Tree representation of filter expression and(or(e,not(b)),gt(C,0))

The filter query parameter names a filter expression which uses a very simple expression notation. The filter expression syntax expresses the same condition as a combination of function calls:

 and(or(e,not(b)),gt(C,0))

Filter expression syntax

The syntax for filter expressions:

expression := literal
expression := name
expression := function
function := identifier '(' [ arguments ] ')'
arguments := expression [ ',' arguments ]
literal := 'true' | 'false'
literal := number
literal := date
literal := time
literal := date-time
literal := string
string := "'" characters "'"
string := '"' characters '"'
name := identifier [ '.' name ]

For example, the basic query used above,

   GET /reports/reports?createdBy=dale&name=Production%20Report

   GET /reports/reports?filter=and(eq(createdBy,'dale'),eq(name,'Production%20Report'))

Filter expressions

There are three types of expressions that may be used in the filterExpression a filter query:

1. literals
2. names
3. functions

Filter literals

Literals (also known as constants) are fixed values, such as

• the string 'dale',
• the string 'Production Report',
• Boolean literals true and false,
• numbers such as 100 or -5.75.
• date, time, and date-time literals

Literals are typically used as arguments to functions.

String literals are sequences of zero or more characters enclosed in quotes. In filter query parameters, strings may be enclosed in either single or double quotes. The string literal 'dale' is the same as the string literal "dale"; only the contents of the string is significant. If the string contains quote characters, use one of two representations:

1. To include single quotes (') in a string literal, use double quotes to quote the string: "Dale Smith's ID is 'dale'".
   To include double quotes (") in a string literal, use single quotes to quote the string: 'Dale chose the ID "dale".'.
2. Alternatively, you can uniformly double each embedded quote character:

      'IT assigned the user ID ''dale'' to Dale Smith.'
      "IT assigned the user ID 'dale' to Dale Smith."
   

   are identical and have the value

      IT assigned the user ID 'dale' to Dale Smith.
   

Names

Names in filter expressions refer to members of the items in the collection, such as the members name, createdBy, modifiedBy, or imageUris.icon from the example report resource above. Names are typically used as arguments to functions, such as comparing the members to literal values, as described below. An example is the expression

   eq(createdBy,'dale')

Date, time, and date-time literals are other name filters.

1. A date literal uses the form yyyy-MM-dd such as 2017-07-27.
2. A time literal uses the form HH:mm:ss and HH:mm:ss.SSSZ where the hours HH range from 00 to 24 and the fractional seconds and time zone are optional. The time zone code Z means UTC (GMT) time zone. Time zone offsets if +HH:mm and -HH:mm are also allowed instead of Z.
3. A date-time literal uses the form yyyy-MM-ddTHH:mm:ss.SSSZ where the fractional seconds and time zone are optional; for example, 2017-06-28T03:18:53.0717Z

Filter function expressions

Functions operate on one or more values which form the arguments to the function. A function expression has the form

function := identifier '(' [ arguments ] ')'
arguments := expression [ ',' arguments ]

Below is the base set of functions that may be used in filter expressions.

Logical functions

Logical functions combine other boolean expressions.

• and(e0, e1 [,..., en]) true if and only if all expressions are true; at least two expressions required. This uses short-circuit semantics (does not evaluate remaining ei, ..., en expressions arguments if e_i is false).
• or(e0, e1, [... en]) true if and only if one or more expressions are true; at least two expressions required. or uses short-circuit semantics (does not evaluate remaining expressions if any e_i is true).
• not(e) true if and only if expression e is false
• isNull(e) true if and only if e is null or unset

Relational functions

Relational functions compare two or more expressions. Most functions accept two or more arguments, in which case the relation must hold for consecutive values; i.e. lt(a,b,c,d) is equivalent to and(lt(a,b),lt(b,c),lt(c,d)) (that is, a < b && b < c && c < d.

This syntax cannot express combinations of relational operators, such as minInclusive ≤ x < maxExclusive; one must use and(le(minInclusive,x),lt(x,maxExclusive)).

• eq(e0, e1 [,..., en]) true if and only if all expressions are equal.
• ne(e0, e1) true if and only if the e₀ is not equal to e₁
• lt(e0, e1 [,..., en]) true if and only if each expression e_i is less than ei+1
• le(e0, e1 [,..., en]) true if and only if each expression e_i is less than or equal to ei+1
• gt(e0, e1 [,..., en]) true if and only if each expression e_i is greater than ei+1
• ge(e0, e1 [,..., en]) true if and only if each expression e_i is greater than or equal to ei+1
• in(e, v0, v1 [,..., vn]) Set containment, true if and only if the value e is equal to at least one of the vi values.

Relational functions may be combined with date, time, and date-time literals. To find all reports created between January and March 2017 (using GMT)

   GET /reports/reports?filter=le(2017-01-01T00:00:00Z,creationTimeStamp,2017-03-31T24:00:00Z)

String functions

Functions which manipulate string values

• match(e,regularExpression) true if and only if the string e matches a regular expression. The regularExpression is a quoted string literal or other expression that yields a string pattern.
• matchAll(regularExpression, exp1,...,expn) true if all expressions match the regular expression. Uses short-circuit semantics (do not evaluate remaining expressions if any preceding expression does not match).
• matchAny(regularExpression, exp1,...,expn) true if any expressions match the regular expression.
• match(map,name,regularExpression) true if and only if the member map is a Map or Properties object which contains an item (key,value) such that and(match(key,name),match(value,regularExpression) is true
• contains(e,substring) true if and only if the string e contains the substring, or if e is a list/set/collection, true if and only if an element of e equals substring
• startsWith(e,prefix) true if and only if the string e starts with prefix
• endsWith(e,suffix) true if and only if the string e ends with the suffix
• blank(e) true if and only if the string a consists of only whitespace
• length(e) the integer length of the string expression e
• substr(e,start,len) the substring of e, starting at start (zero-based index?) and including len chars. Negative index start counts backwards from the end. If len is omitted, the substring will extend to the end of the string.
• upCase(s) convert all character in s to upper case.
• downCase(s) convert all character in s to lower case.

Locale-sensitive collation

Several of the above functions require locale-sensitive collation, where the locale is derived by the Accept-Language header passed by the client.

• $primary (normal case-insensitive comparison)
• $secondary (only different accent characters are significant)
• $tertiary (normal case-sensitive comparison)
• $quaternary (punctuation is considered)
• $identical (case, accents, and punctuation differences are significant)

If the first parameter is not one of these special identifiers, the default is $identical. These reserved identifiers are ignored when comparing numeric and boolean values.

Examples of locale-sensitive functions, using $tertiary as an example:

• eq($tertiary, a, b,...) (equal)
• ne($tertiary, a, b) (not equal)
• lt($tertiary, a, b,...) (less than)
• le($tertiary, a, b,...) (less than or equal)
• gt($tertiary, a, b,...) (greater than)
• ge($tertiary, a, b,...) (greater than or equal)
• in($tertiary, e, v0, v1 [,..., vn])
• contains($tertiary, e, substring)
• startsWith($tertiary, e, prefix)
• endsWith($tertiary, e, suffix)

Combining basic filtering and the filter query parameter

An API request may combine basic filtering and the filter query parameter. When both are used, the request combines them by adding an implicit and function to the existing filter query parameter. For example, for

    GET .../resource?name=dale&format=ruled&filter=or(bounded,lt(shipDate,2017-07-27))

    GET .../resource?filter=and(eq(name,'dale'),eq(format,'ruled'),or(bounded,lt(shipDate,2017-07-27)))

Note that the resulting filter expression is always false if the basic filter and the filter specify mutually exclusive values:

    GET .../resource?name=dale&format=ruled&filter=eq(name,'jack')

    GET .../resource?filter=and(eq(name,'dale'),eq(format,'ruled'),eq(name,'robert')