Search & Paging Syntax
Data retrieval services typically include standard parameters for searching, sorting, and pagination.
Pagination Parameters
Pagination allows data to be retrieved in sets of a specified size and provides additional data about the result set.
Parameter | Description |
---|---|
_limit |
Integer >= 0 and <= 25 indicating how many results to return at once. The default is 10. For keys with the replication role it may be >= 0 and <= 1000. |
_pagination |
|
_page |
Indicates the page number of the result set to return, where 1 is the first page. This value cannot exceed 100000 . |
_skip |
Return results starting with the next item past this index. The value must range between 0 and 2500000 , and when using this parameter, the Pagination hash will contain the CurrentOffset attribute in place of CurrentPage . |
_skiptoken |
This parameter is used as an offset to allow paging without missing data. The first request should have an empty value. Then responses will contain a SkipToken attribute to use in subsequent requests. |
When the Pagination
hash is enabled, the response will include the following:
"Pagination": {
"TotalRows": 78,
"PageSize": 25,
"TotalPages": 4,
"CurrentPage": 1
}
Attribute | Description |
---|---|
TotalRows |
The total number of rows in the response. |
PageSize |
The number of rows that are returned in a single page. |
TotalPages |
The total number of pages in the response. |
CurrentPage |
The page number of the current response. |
CurrentOffset |
The offset of the current response. Only available when using the _skip parameter. |
Sort Parameters
Sorting is typically available on resources that allow searching.
Parameter | Description |
---|---|
_orderby |
Indicates the order in which to return records in the result set. Each service has its own default order. |
Any searchable field can be present in the _orderby
parameter, with some exceptions (e.g. longer text fields such as PublicRemarks
for listings). To specify "Ascending" or "Descending" order, prefix the field used in the _orderby
parameter with the+
and -
characters, respectively.
Ordering listing results descending by ListPrice
_orderby=-ListPrice
Ordering listing results by multiple attributes:
_orderby=-ListPrice,+BathsTotal
Search Parameters
Request parameters below are common to data retrieval services supporting searching.
Parameter | Description |
---|---|
_filter |
Of the format <field> <operator> <condition> [<connector> <field> <operator> <condition>] |
The components of a filter string are as follows:
connector
: used to indicate AND, OR, or NOT operators between multiple fields.
Connector | Description | Example |
---|---|---|
And |
Logical conjunction | PropertyType Eq 'A' And BathsTotal Eq 3.0 |
Or |
Logical disjunction | PropertyType Eq 'A' Or BathsTotal Eq 3.0 |
Not |
Logical negation on a single field or as a connector between fields | PropertyType Eq 'A' Not BathsTotal Eq 3.0 |
field
: a valid field name for the resource being searched, e.g.ListPrice
for listing standard field data, or"Contract Information"."Area"
for listing custom field data.operator
: One of the following:
Operator | Description | Applies To These Data Types | Example |
---|---|---|---|
Eq |
Equal. | All | BathsTotal Eq 4.0 |
Ne |
Not equal. | All | City Ne 'Fargo' |
Bt |
Between. | Date, Datetime, Integer, and Decimal | BathsTotal Bt 3.0,4.0 |
Gt |
Greater than. | Date, Datetime, Integer, and Decimal |
BathsTotal Gt 2.0 |
Ge |
Greater than or equal to. | Date, Datetime, Integer, and Decimal |
BathsTotal Ge 2.0 |
Lt |
Less than. | Date, Datetime, Integer, and Decimal |
BathsTotal Lt 4.0 |
Le |
Less than or equal to. | Date, Datetime, Integer, and Decimal |
BathsTotal Le 4.0 |
_filter
supports one level of parameter nesting with parentheses.
Valid query with one level of nesting:
Field1 Eq 'Value1' And (Field2 Eq 'Value2' Or Field3 Eq 'Value3')
Invalid query with two levels of nesting:
Field1 Eq 'Value1' And (Field2 Eq 'Value2' Or (Field3 Eq 'Value3' And Field4 Eq 'Value4'))
Data Types
All data values are one of the following defined data types.
Data Type | Format | Example |
---|---|---|
Date | YYYY-MM-DD | 2008-10-31 |
Datetime | YYYY-MM-DDThh:mm:ss | 2003-04-01T19:01:02Z |
Time | hh:mm:ss (seconds are optional) | 12:00:00 |
Integer | A number no decimal component | 142 |
Decimal | A number with X (0-N) decimal places | 142.01 |
Character | An alphanumeric string, padded with single quotes | 'Lorem O\'Little Ipsum' |
Boolean | true or false |
true |
NULL | NULL |
NULL |
Date
Example: 2008-10-31
The Date
type follows ISO 8601.
Datetime
Example: 2003-04-01T19:01:02Z
The Datetime
type follows ISO 8601.
Time
Example: 19:01:02
The Time
type specifies the time element of a Datetime
type but is not specific to a date.
Integer
Example: 142
The Integer
data type specifies a number with no decimal component.
Decimal
Example: 142.01
The Decimal
data type specifies a number with a decimal component.
Character
Example: 'Lorem O\'Little Ipsum'
The Character
data type specifies a string, and is always padded with single quotes.
Wildcards
The Character
data type supports the following two wildcard operators:
Operator | Description | Example |
---|---|---|
* |
Matches zero or more occurrences of any character. | 'F*rgo' regex: /F.*rgo/ |
? |
Matches zero or one occurrences of any character. | 'F?rgo' regex: /F.?rgo/ |
Case-Insensitive Searching
When a wildcard is present, the search criterion will be applied as a case-insensitive match; without a wildcard, it will be case-sensitive.
Note the following restrictions on using wildcards:
- A condition may not exist solely of wildcards.
- A condition may not include more than three wildcard operators.
- Wildcards may only be used with the
Eq
andNe
operators.
Escaping Special Characters
Any special character (wildcards or a single quote) can be escaped with the backslash character:
'Lorem O\'Little Ipsum'
Boolean
Example: true
Boolean
data types are always either true
or false
.
NULL
Example: NULL
NULL
describes the absence of a value. There are only two use cases for searching using the NULL
type:
- For the absense of a value, e.g.
City Eq NULL
- For the precense of a value, e.g.
City Ne NULL
Functions
Functions may be used in place of data values for supported data types to provide more flexible searches.
Name | Supported Data Type | Description | Example |
---|---|---|---|
toupper | Character | Transform all charaters in the provided string to upper case. | toupper(City) Eq toupper('Fargo') |
tolower | Character | Transform all charaters in the provided string to lower case. | tolower(City) Eq tolower('Fargo') |
startswith | Character | Matches records whose fields start with the provided string | City Eq startswith('fargo') |
endswith | Character | Matches records whose fields end with the provided string | City Eq endswith('fargo') |
contains | Character | Matches records whose fields contain the provided string | City Eq contains('fargo') |
now | Date and Datetime | Use the current date and time to search on a field. | OriginalEntryTimestamp Lt now() |
days | Date and Datetime | Search on a field within a range of days from the current date. | OriginalEntryTimestamp Ge days(-7) |
weekdays | Date and Datetime | Search on a field within a range of weekdays (Monday through Friday) from the current date. Weekends will be excluded from the number of days. | OriginalEntryTimestamp Ge weekdays(-5) |
months | Date and Datetime | Search on a field within a range of months from the current date. | OriginalEntryTimestamp Ge months(-6) |
years | Date and Datetime | Search on a field within a range of years from the current date. | OriginalEntryTimestamp Ge years(-2) |
date | Datetime fields | Search fields for a certain date regardless of the day. | date(OriginalEntryTimestamp) Ge 2016-01-01 |
time | Datetime fields | Search fields for a certain time regardless of the day. | time(OriginalEntryTimestamp) Ge 12:00 |
year | Date and Datetime fields | Search fields by a given year. | year(OriginalEntryTimestamp) Ge 2012 |
month | Date and Datetime fields | Search fields by a given month. | month(OriginalEntryTimestamp) Ge 10 |
day | Date and Datetime fields | Search fields by a given day of the month. | day(OriginalEntryTimestamp) Ge 30 |
dayofweek | Date and Datetime fields | Search fields by a given day of the week using the ISO 8601 value for weekday number. | dayofweek(OriginalEntryTimestamp) Eq 7 |
dayofyear | Date and Datetime fields | Search fields by a given day of the year. | dayofyear(OriginalEntryTimestamp) Le 90 |
hour | Datetime fields | Search fields for a given hour of the day (0-23), disregarding the date. | hour(OriginalEntryTimestamp) Ge 12 |
minute | Datetime fields | Search fields for a given minute of the hour, disregarding the date. | minute(OriginalEntryTimestamp) Ge 58 |
second | Datetime fields | Search fields for a given second of the minute, disregarding the date and hour. | second(OriginalEntryTimestamp) Ge 45 |
fractionalseconds | Datetime fields | Search fields by the fraction of a second they occurred, disregarding the date, hour, minute and so forth. | fractionalseconds(OriginalEntryTimestamp) Ge 0.1 |
range |
MapCoordinate only
|
Inclusively matches all
strings between the given two parameters.
Equivalent to MyField Bt 'M01','M03' .
|
MapCoordinate Eq range('M01','M03') |
SparkQL Errors
To help with debugging errors when searching, a SparkQLErrors
attribute
is included in responses that fail due to an invalid _filter
parameter.
See the response description documentation
for more details.