2.12 Query Language
The SData query language is used to express filtering conditions with the where query parameter. Here are a few examples of where conditions:
http://www.example.com/sdata/myApp/myContract/-/salesOrders?where=billingAddress.countryCode eq 'UK'
and date ge @2008-01-01@
http://www.example.com/sdata/myApp/myContract/-/customers?where=left(name,1) between 'A' and 'N'
http://www.example.com/sdata/myApp/myContract/-/customers?where=name like '%BANK%'
http://www.example.com/sdata/myApp/myContract/-/customers?where=creditLimit - balance ge 1000.0
Operators
The query language supports the following operators:
| Class | Operator | Meaning | Basic | Priority value | Example |
|---|---|---|---|---|---|
| Member | x.y | member access | 1 | billingAddress.country | |
| Unary | -x | unary minus | 2 | - discount | |
| not x | negation | 2 | not disabled | ||
| Multiplicative | x mul y | multiplication | 3 | price mul 1.07 | |
| x div y | division | 3 | price div 2 | ||
| x mod y | modulus | 3 | index mod 2 | ||
| Additive | x + y | addition | 4 | price + tax | |
| x - y | substraction | 4 | price - discount | ||
| Comparison | x eq y | equal | Yes | 5 | countryCode eq 'GB' |
| x ne y | not equal | Yes | 5 | countryCode ne 'GB' | |
| x lt y | less than | Yes | 5 | price lt 100.0 | |
| x le y | less than or equal | Yes | 5 | price le 100.0 | |
| x gt y | greater than | Yes | 5 | price gt 100.0 | |
| x ge y | greater than or equal | Yes | 5 | price ge 100.0 | |
| x between y and z | between | 5 | price between 100.0 and 500.0 | ||
| x in (y, z) | contained in | 5 | countryCode in ('GB', 'US') | ||
| x like y | like | 5 | name like '%BANK%' | ||
| Logical | x and y | logical and | Yes | 6 | countryCode eq 'GB' and amount gt 1000.0 |
| x or y | logical or | Yes | 7 | countryCode eq 'GB' or countryCode eq 'US' |
The operators with the lowest priority value evaluate first. Within a given class, the operators associate from left to right, except for unary operators that associate right to left.
The operators flagged with Basic = Yes in the table are the minimum that all SData providers support. Advanced SData providers should support the whole table. See conformance levels below.
Parentheses
Parentheses can be used to override the priority value or association rules. The following table gives some examples:
| Expression | Parsed as | Value |
|---|---|---|
| 2 mul 5 + 3 mul 2 | (2 mul 5) + (3 mul 2) | 16 |
| 2 mul (5 + 3) mul 2 | (2 mul (5 + 3)) mul 2 | 32 |
| 1 eq 1 or 1 eq 2 and 1 eq 3 | (1 eq 1) or ((1 eq 2) and (1 eq 3)) | true |
| (1 eq 1 or 1 eq 2) and 1 eq 3 | ((1 eq 1) or (1 eq 2)) and (1 eq 3) | false |
Literals
Expressions can contain literals - explicit values for basic types. The following table describes the syntax and gives examples:
| Example | Data type | Comments |
|---|---|---|
| 17 | integer | |
| 17.0 | decimal | Decimal separator must be a dot. |
| 'GB' | string | |
| "GB" | string | |
| "Maxim's" | string | Single quote does not need to be specially marked inside double quotes and vice versa. |
| 'Maxim''s' | string | Quoting character can be specially marked by doubling it. |
| @2008-05-19@ | date | RFC 3339 "full date" format. |
| @2008-05-19T18:41:00@ | timestamp | Interpreted as local time. It assumes consumer and provider are in same timezone. |
| @2008-05-19T18:41:00+02:00@ | timestamp | Time in GMT+2 timezone. |
| @2008-05-19T16:41:00Z@ | timestamp | GMT time. |
Functions
The SData query language also supports functions. The following table gives a list of the functions that you may use to build queries (firstName=”John” and lastName=”Doe” in the example):
| Class | Function | Description | Example | Result |
|---|---|---|---|---|
| String Functions | concat(_str1_, _str2_, ...) | Combines strings | concat(firstName, " ", lastName) | John Doe |
| left(_str_, _len_) | Returns leftmost _len_ characters from _str_ Returns _str_ if _str_ has less than _len_ characters | left(firstName, 1) | J | |
| right(_str_, _len_) | Returns rightmost _len_ characters from _str_ Returns _str_ if _str_ has less than _len_ characters | right(firstName, 3) | ohn | |
| substring(_str_, _start_, _len_) | Returns substring starting at index _start_ and containing _len_ characters _start_ is 1-based (like in SQL) | substring(firstName, 3, 2) | hn | |
| lower(_str_) | Converts _str_ to lower case | lower(firstName) | john | |
| upper(_str_) | Converts _str_ to upper case | upper(firstName) | JOHN | |
| replace(_str_, _pat_, _repl_) | Replaces occurences of _pat_ by _repl_ in _str_ | replace(firstName, "oh", "ea") | Jean | |
| length(_str_) | Returns the length of str | length(firstName) | 4 | |
| locate(_pat_, _str_) | Returns the index of the first occurence of _pat_ in _str _Result is 1-based (like in SQL) | locate("oh", firstName) | 2 | |
| lpad(_str_, _len[_, _pad]_) | Pads _str_ to the left with _pad_ to obtain a length of _len. _Default _pad_ is space (" ") | lpad(firstName, 6, "*") | **John | |
| rpad(_str_, _len[_, _pad]_) | Pads _str_ to the right with _pad_ to obtain a length of _len. _Default _pad_ is space (" ") | rpad(firstName, 6, "*") | John** | |
| trim(str) | Removes leading and trailing spaces from _str_ | trim(" hello world ") | hello world | |
| ascii(_str_) | Returns ascii code from leftmost character of _str_ | ascii(firstName) | 74 | |
| char(_code_) | Converts ascii code to single char string | char(74) | J | |
| Numeric Functions | abs(_x_) | Returns the absolute value of _x_ | abs(-3) | 3 |
| sign(_x_) | Returns the sign of _x_ as -1, 0 or 1 | sign(-3) | -1 | |
| round(_x_[, _d_]) | Rounds _x_ to _d_ decimals Rounds to nearest integer if _d_ is not specified | round(2.576, 2) | 2.58 | |
| trunc(_x_[, _d_]) | Truncates _x_ to _d_ decimals Truncates to integer if _d_ is not specified | trunc(2.576, 2) | 2.57 | |
| floor(x) | Returns largest integer <= _x_ | floor(2.576) | 2 | |
| ceil(_x_) | Returns smallest integer >= _x_ | ceil(2.576) | 3 | |
| pow(_x_, _y_) | Return _x_ to the power of _y_ | pow(5, 3) | 125 | |
| Date Functions | currentDate() currentTime() currentTimestamp() | Returns the current date/time/timestamp | currentDate | @2008-05-21@ |
| year(_dt_) month(_dt_) day(_dt_) hour(_dt_) minute(_dt_) second(_dt_) millisecond(dt) tzHour(_dt_) tzMinute(_dt_) | Extract specified component from _dt_. _dt_ may be a date, time or _timestamp_ | year(currentDate()) | 2008 | |
| dateAdd(_date_, _d_) dateSub(_date_, _d_) | Adds/subtracts _d_ days to _date_ | dateAdd(@2008-05-21@, 5) | @2008-05-26@ | |
| timestampAdd(_ts_, _ms_) timestampSub(_ts_, _ms_) | Adds/subtracts _ms_ milliseconds to _ts_ | timestampAdd(@2008-05-21T00:00:00Z@, 5000) | @2008-05-21T00:00:05Z@ |
Protocol Filter Variables
The following protocol filter variables may be used in a query:
| Variable | Type | Description |
|---|---|---|
| $uuid | UUID | the UUID of the resource (the value of its sdata:uuid attribute) |
| $key | string | the internal key of the resource (the value of its sdata:key attribute) |
| $published | datetime | the creation timestamp of the resource (the value of the <published> element of its Atom entry) |
| $updated | datetime | the last modification timestamp of the resource (the value of the <updated> element of its Atom entry) |
| $title | string | the title of the resource (the value of the <title> element of its Atom entry) |
More generally, any sdata:xxx attribute and any <xxx> Atom entry element may be used in a query, as $xxx. The table above only gives the most commonly used.
A given provider MAY only support a subset of these variables, or none of them. If a provider supports some of these variables, it MUST advertise the list of variables that it supports with an sme:protocolFilters attribute its resource kind schema elements. For example, if a provider supports $uuid and $updated on the salesOrder resource kind, its schema must contain:
<xs:element name="salesOrder" type="salesOrder--type" ... sme:protocolFilters="$uuid,$updated" ...>
SData imposes some rules on queries that contain both protocol filter variables and normal resource properties. Such queries must be written as Q1 and Q2 where Q1 only contains protocol filter variables and Q2 only contains normal resource properties. So, the following query is valid:
$updated gt @2011-01-07T09:32:07@ and totalAmount gt 1000.00
But the following is not valid:
$updated gt @2011-01-07T09:32:07@ or totalAmount gt 1000.00
Protocol filter variables can be used in where parameters as well as in selectors. Here are two examples:
http://www.example.com/sdata/myApp/myContract/-/salesOrders?where=$updated gt @2011-01-07T09:32:07 http://www.example.com/sdata/myApp/myContract/-/salesOrders($uuid eq '58b0-...')
The first URL returns a feed containing all the sales orders modified since the specified timestamp. The second one returns a single entry containing the sales order with the specified UUID.
Conformance levels
SData defines three conformance levels for the query language, as defined in the following table:
| Conformance Level | Implements | Comments |
|---|---|---|
| Basic | Operators flagged with Basic = Yes in the earlier table. Parenthesis grouping. Literals. | All providers must implement this level. |
| Intermediate | Basic + subset of remaining operators + subset of functions. | Consumer should only use Basic level + documented subset. |
| Complete | Basic + all remaining operators + all functions. | Consumer can safely use full language. |
SData providers MUST support the basic conformance level. They MAY support the other levels.
Scope
The scope for a query is the right-most Resource Kind in the URL, hence in the following example:
http://www.example.com/sdata/myApp/myContract/-/customers('C001')/salesOrders?where=date ge @2008-01-01@
the date clause applies to the salesOrder Resource Kind and not the customers Resource Kind.