Supported Query Parameters
Let's go through this topic that explains the supported query operators and their operand syntaxes, aliases and attributes for query parameters, and query URI examples.
Supported Operators
This table describes the supported operators, their respective example expressions, supported data types, and descriptions.
Operator | Example Expression | Operand Supported Data Types | Description |
---|---|---|---|
after | publishDate after '2011-11-01T06:00:00' |
Integer, Long, Date, DateTime, DateTimeZone | The gt operator is a synonym. |
and | reputationPoints lt 200 and dateAdded lt '2015-01-01' |
An Expression or logical combination of Expressions | See more info in the Combined Expressions section. |
between | reputationPoints between 100 and 200 |
Integer, Long, Date, DateTime, DateTimeZone | The ge_le operator is synonym. For this operator, the limits are inclusive. In the example, the result will be [100,101,...,199,200]. |
before | dateAdded before '2011-11-01' |
Integer, Long, Date, DateTime, DateTimeZone | The lt operator is a synonym. |
eq | adminUser eq true |
Boolean, String, Integer, Long, Date, DateTime, DateTimeZone | Interpreted as "equal to". |
ge | dateModified ge '2011-11-01T00:00:01PDT' |
Integer, Long, Date, DateTime, DateTimeZone | Interpreted as "greater than or equal to". The onOrAfter operator is a synonym. |
ge_le | reputationPoints ge_le 100 and 200 |
Integer, Long, Date, DateTime, DateTimeZone | Interpreted as "greater than or equal to and less than or equal to". The between operator is synonym. For this operator, the limits are inclusive. In the example, the result will be [100,101,...,199,200]. |
gt_le | reputationPoints gt_le 100 and 200 |
Integer, Long, Date, DateTime, DateTimeZone | Interpreted as "greater than and less than or equal to". For this operator, the lower limit is exclusive, while the upper limit is inclusive. In the example, the result will be [101,102,...,199,200]. |
ge_lt | reputationPoints ge_lt 100 and 200 |
Integer, Long, Date, DateTime, DateTimeZone | Interpreted as "greater than or equal to and less than". For this operator, the lower limit is inclusive, while the upper limit is exclusive. In the example, the result will be [100,101,..,198,199]. |
gt | publishDate gt '2011-11-01T06:00:00' |
Integer, Long, Date, DateTime, DateTimeZone | Interpreted as "greater than". The after operator is a synonym. |
gt_lt | reputationPoints gt_lt 100 and 200 |
Integer, Long, Date, DateTime, DateTimeZone | Interpreted as "greater than and less than". For this operator, both the lower and upper limits are exclusive. In the example, the result will be [101,102,..,198,199]. |
in | categories.referenceKey in ('ASIA', 'EUROPE') |
String, Integer, Long | This operator supports multiple values grouped by parentheses. |
inWithNoChildren | categories.referenceKey inWithNoChildren ('ASIA', 'EUROPE') |
String | This operator is the multi-valued version of the withNoChildren operator. This operator is only supported for Category objects with query parameters recordId and referenceKey. |
le | reputationPoints le 100 |
Integer, Long, Date, DateTime, DateTimeZone | Interpreted as "less than or equal to". The onOrBefore operator is a synonym. |
likeAny | firstName likeAny ('Pete*', '*Pa*', 'Jo*n') |
String | The wild card character is '*', as '%' is the wild card character in SQL statements. This operator can be used to achieve starts-with, ends-with and contains string expressions. |
lt | dateAdded lt '2011-11-01' |
Integer, Long, Date, DateTime, DateTimeZone | Interpreted as "less than". The before operator is a synonym. |
matchAll | categories.referenceKey matchAll ('ASIA', 'EUROPE') |
String | This operator is only supported with query parameters that represent a to-many relationship to the resource with attributes recordId and referenceKey. |
matchAllWithNoChildren | categories.referenceKey matchAllWithNoChildren ('ASIA', 'EUROPE') |
String | This operator is only supported for query parameters that represent a to-many relationship from the resource to Category objects and can only be used with attributes recordId and referenceKey. |
onOrAfter | dateModified onOrAfter '2011-11-01T00:00:01PDT' |
Date, DateTime, DateTimeZone | The ge operator is a synonym. |
onOrBefore | dateModified onOrBefore '2011-11-01T00:00:01PDT' |
Date, DateTime, DateTimeZone | The le operator is a synonym. |
or | reputationPoints lt 200 or dateAdded lt '2015-01-01' |
An Expression or logical combination of Expressions | See more info in the Combined Expressions section. |
withNoChildren | categories.referenceKey withNoChildren 'EUROPE' |
String | Interpreted as "equal to with no children". This operator is only supported for Category objects with query parameters recordId and referenceKey. |
Note:
The special query operators described here are valid only for Knowledge Management REST APIs described in this document.Unsupported Operators
This table describes unsupported operators for the query expressions and their respective descriptions.
Operator | Description |
---|---|
Relational | This includes the following symbols: <, >, =, ==, !=, >=, <=, &, and |. The following operators are supported as an alternative: lt, gt, eq, ge, le, and, or. |
Not | This includes "not", "ne" (not equal to), and "pr" (present or is not null). |
String | This includes "co" (contains), "sw" (starts with), and "ew" (ends with). The operator likeAny is supported and achieves the same results with the proper placement of the wild card character. |
SQL clauses | While the query may resemble a SQL where clause, it is not an implementation of SQL and thus the following clauses are not supported: JOIN, GROUP BY, FROM, WHERE etc. |
Operand Syntax
This table describes the data type and syntax description of the supported operands.
Operand Data Type | Syntax Description |
---|---|
Boolean Literals |
|
Date, DateTime, DateTimeZone |
|
Null Values and Null Strings |
|
Numbers (Integer and Long Literals) |
|
Quoted Strings |
|
Query Parameter Aliases
This table describes the query parameters, aliases of the query parameters and their respective examples.
Query Parameter | Alias | Query Parameter Example | Alias Example |
recordId |
id |
recordId eq '2LKAJDFO8234OIJ3O24P18439837409819' |
id eq '2LKAJDFO8234OIJ3O24P18439837409819' |
referenceKey |
refKey |
referenceKey in ('FAQ', 'SOLUTIONS') |
refKey in ('FAQ', 'SOLUTIONS') |
Query Parameter Attributes
A query parameter that represents a relationship to the resource can have only first level attributes in the expression. Keep in mind that there can be at most one dot "." for any query parameter expression. For example:
GET /content?q=lastModifier.firstName eq 'John' and lastModifier.lastName eq 'Smith'
To illustrate valid versus invalid expressions containing attributes of a relationship query parameter, let's use the Content object, which has a relationship to the User object, through the lastModifier attribute, and a relationship to the Locale object, through the locale attribute. Also the User object has a relationship to the Locale object, through the contentLocales attribute, and the Locale object has a relationship to the Language object, through the language attribute.
Invalid Query
GET /content?q=lastModifier.contentLocales.localeCode in ('en_US', 'en_GB')
Explanation
In the invalid query, we traversed a second level attribute of the lastModifier query parameter, by going through the contentLocales to filter by localeCode. Filtering by second level attributes of query parameters is not supported.
Valid Query
GET /content?q=lastModifier.firstName eq 'John' and lastModifier.lastName eq 'Smith'
Invalid Query
GET /content?q=locale.language.languageCode eq 'en'
Explanation
In the invalid query, we traversed a second level attribute of the locale query parameter, by going through its language to filter by languageCode. Filtering by second level attributes of query parameters is not supported.
Valid Query
GET /content?q=locale.localeCode in ('en_US', 'en_GB')
Combined Expressions
One or more expressions can be combined by using the AND operator and the OR operator and by grouping the expressions with parentheses. Keep in mind the query parsing rules regarding complex expressions.
and
One or more expressions can be combined using the logical and operator.
GET /content?q=dateModified gt '2014-01-01' and dateAdded after '2013-01-01' and categories.dateAdded after '2011-01-01'
or
One or more expressions can be combined using the logical or operator.
GET /content?q=dateModified gt '2014-01-01' or dateAdded after '2013-01-01' or categories.dateAdded after '2011-01-01'
Grouping
GET /content?q=(dateModified gt '2014-01-01' or dateAdded after '2013-01-01') and categories.dateAdded after '2011-01-01'
GET /content?q=(dateModified gt '2014-01-01' or dateAdded after '2013-01-01') and (categories.dateAdded after '2011-01-01' and categories.refKey in ('ASIA', 'EUROPE'))
Rules of Thumb
-
Avoid using nested grouped expressions and nested logical operators within an expression.
Invalid Query
GET /content?q=(categories.dateAdded after '2015-01-01' or (categories.refKey in ('EUROPE', 'ASIA') and categories.sortOrder gt 4)) and title likeAny ('*Oracle*')
Explanation
In this query, the combined expressions using the categories query parameter contains a sub-grouping of expressions, which is not supported.
Valid Query
GET /content?q=(categories.dateAdded after '2015-01-01' and categories.refKey in ('EUROPE', 'ASIA') and categories.sortOrder gt 4) and title likeAny ('*Oracle*')
-
Avoid applying multiple filters on the same query parameter or on the same attribute of a query parameter that represents a relationship to the resource.
Invalid Query
GET /content?q=(categories.refKey eq 'EUROPE' and categories.refKey eq 'ASIA') and publishDate ge '2015-01-01' and publishDate le '2015-03-31'
Explanation
In the invalid query, the refKey attribute of the categories query parameter is used in two expressions. In addition, the publishDate query parameter is also used in two expressions. You need to reduce the expression to its simplest form.
Valid Query
GET /content?q=categories.refKey matchAll ('EUROPE', 'ASIA') and publishDate between '2015-01-01' and '2015-03-31'
-
Avoid using both the AND and OR logical operators on attributes of the same query parameter that represents a relationship to the resource.
Invalid Query
GET /content?q=lastModifier.dateAdded between '2014-01-01' and '2015-01-01' or lastModifier.login eq 'jsmith' and lastModifier.firstName eq 'John' or lastModifier.lastName eq 'Smith'
Explanation
In this invalid query, the expressions using attributes of the lastModifier query parameter have been combined using both the AND and OR logical operators, which is not supported.
Valid Query
GET /content?q=lastModifier.dateAdded between '2014-01-01' and '2015-01-01' or lastModifier.login eq 'jsmith' or lastModifier.firstName eq 'John' or lastModifier.lastName eq 'Smith'
-
Try to group all expressions on the same query parameter that represents a relationship to the resource.
Invalid Query
GET /content?q=lastModifier.dateAdded between '2014-01-01' and '2015-01-01' and title likeAny ('*Oracle*') and lastModifier.login eq 'jsmith' or lastModifier.firstName eq 'John' or lastModifier.lastName eq 'Smith'
Explanation
In this invalid query, the expression using the title query parameter is combined in between expressions using attributes of the lastModifier query parameter.
Valid Query
GET /content?q=title likeAny ('*Oracle*') and (lastModifier.dateAdded between '2014-01-01' and '2015-01-01' or lastModifier.login eq 'jsmith' or lastModifier.firstName eq 'John' or lastModifier.lastName eq 'Smith')
Query Parsing Rules
-
Combine all expressions using direct query parameters using either the AND or OR operators, but not both. Direct query parameters do not include first level attributes of query parameters that represent a relationship to the resource.
Invalid Query
GET /content?q=((dateAdded ge "2014/10/01" and dateAdded le "2014/12/31") or (dateModified ge "2015/01/01" and dateModified le "2015/03/31")) and title likeAny '*Oracle*'
Explanation
In this invalid query, the grouped dateAdded and dateModified query parameter expressions are combined using the OR operator and then combined to the title query parameter expression using the AND operator which is not supported.
Valid Query
GET /content?q=((dateAdded ge "2014/10/01" and dateAdded le "2014/12/31") and (dateModified ge "2015/01/01" and dateModified le "2015/03/31")) and title likeAny '*Oracle*'
-
All expressions using query parameters that represent a relationship to the resource should be grouped together. In addition, all expressions for query parameters that represent different relationships to the resource should always be combined using the AND operator.
Invalid Query
GET /content?q=(categories.refKey in ('EUROPE', 'ASIA') and contentType.refKey in ('FAQ', 'SOLUTIONS')) or (categories.refKey in ('NORTH_AMERICA', 'SOUTH_AMERICA') and contentType.refKey eq 'FAQ')
Explanation
In this invalid query, the categories query parameter expressions are not grouped together. Likewise the contentType query parameter expressions are not grouped together. In addition, the categories and contentType expressions are not combined using the AND operator.
Valid Query
GET /content?q=categories.refKey in ('EUROPE', 'ASIA', 'NORTH_AMERICA', 'SOUTH_AMERICA') and contentType.refKey in ('FAQ', 'SOLUTIONS')
-
Combine all expressions using first level attributes of query parameters that represent a relationship using the AND or OR operators, but not both.
Invalid Query
GET /content?q=(lastModifier.dateAdded between '2014-01-01' and '2015-01-01' or lastModifier.login eq 'jsmith') and (lastModifier.firstName eq 'John' or lastModifier.lastName eq 'Smith')
Explanation
In this invalid query, the lastModifier query parameter expressions are combined in different groups by the AND and OR operators.
Valid Query
(lastModifier.dateAdded between '2014-01-01' and '2015-01-01' or lastModifier.login eq 'jsmith' or lastModifier.firstName eq 'John' or lastModifier.lastName eq 'Smith')
-
At most one expression using the match all operators (matchAll or matchAllWithNoChildren) for a given query parameter that represents a relationship can be used in a single query.
Invalid Query
GET /content?q=(categories.id matchAll ('2LKAJDFO8234OIJ3O24P18439837409819', 'AMPD2J43FO82DFS3ODSRF123PAKFJ29823', 'ZKDFJ34S023JF0SL2340ZLC01923MFIO1F') or categories.refKey matchAllWithNoChildren ('EUROPE', 'ASIA')) and userGroups.refKey matchAll ('SILVER','GOLD')
Explanation
In this invalid query, there are two expressions using the match all operators for the categories query parameter.
Valid Query
GET /content?q=categories.refKey matchAllWithNoChildren ('EUROPE', 'ASIA') and userGroups.refKey matchAll ('SILVER','GOLD')
-
The contentState request parameter cannot be used with query requests (this applies to the Content resource only). Instead, use the filterMode.contentState query parameter for the content resource to filter the content according to their state (PUBLISHED, LATEST, or LATESTVALID).
Invlaid Request
GET /content?q=title likeAny ('*Oracle*')&contentState=PUBLISHED
Explanation
In this invalid request, the contentState request parameter is not supported for query requests. Instead use the query paramater filterMode.contentState.
Valid Request
GET /content?q=title likeAny ('*Oracle*') and filterMode.contentState eq 'PUBLISHED'