Filter Expressions

A filter expression narrows a result set. The same language is used in two places:

• the where query parameter, which filters the assets a list request returns;
• inside [ … ] on an attribute path, which reduces the set traversed at that point in the path.

filter_expression  = term , { ( "&" | ";" | "|" ) , term } ;

term               = "(" , filter_expression , ")"
                   | attribute_name , [ binary_operator , ( value_list | variable ) ]
                   | ( "+" | "-" ) , attribute_name ;

binary_operator    = "=" | "!=" | "<" | "<=" | ">" | ">=" | "~=" ;

value_list         = value , { "," , value } ;
value              = "'" , ? any character except ' ? , "'"
                   | '"' , ? any character except " ? , '"' ;

variable           = "$" | "$" , NAME ;

warning

There is no filter query parameter on rest-1.v1

The grammar names the root rule for this language filter2_token, which has led readers to look for filter=. The wire parameter is where — Filter2 is an internal name that never appears on the wire.

query.v1 — the JSON/YAML API — is different: it has both a filter key (this language) and a where key (a plain equality mapping). See the comparison table.

The Simplest Case

GET /rest-1.v1/Data/Story?sel=Name,ToDo&where=ToDo='0'

Note the quotes around 0. The shipped example calls this out explicitly: "The number 0 in the where query parameter is surrounded by single-quote marks." Values are always quoted, whatever their underlying type — the grammar has no unquoted numeric or boolean literal.

Comparison Operators

Operator	Meaning
=	Equal to
!=	Not equal to
<	Less than
<=	Less than or equal to
>	Greater than
>=	Greater than or equal to
~=	Full-text search — not a substring match, see below

~= is the least-known of the seven, absent from every other description of this syntax, and the most likely to surprise you:

?where=Name~='Ticket'

danger

~= is full-text search, not LIKE

It compiles to a SQL Server full-text predicate — literally Contains(<column>, <term>) — against the full-text index, not to a LIKE '%…%' scan. The consequences are not subtle:

• It matches whole words, not substrings. Name~='Cog' does not match a Story called Cogent Refactor. A substring that is not a complete word will not be found.
• Searching an exact title often returns nothing. This is the most common complaint about it. A title made up of short or common words can be reduced to nothing usable once the index's stopword list and word breakers have been applied, so the asset you are looking at on screen does not come back.
• It depends on the full-text index. If the index is missing, disabled or has not caught up with recent writes, results are incomplete rather than wrong-but- explainable. A newly created asset may not be findable immediately.
• The term you send is rewritten into a valid full-text condition before it is executed. What runs is not always character-for-character what you supplied.
• Through a multi-value relation, spell it as a sub-filter. MentionedInExpressions[Content~='release'] matches; MentionedInExpressions.Content~='release' returns nothing, silently, no matter how long the index has had. The dotted spelling through a multi-value relation is compiled as an exact = comparison on the joined value, so a full-text term never matches a whole comment. (Exact = works in both spellings; single-value relations such as Owner.Name~='jim' are fine.) The sub-filter spelling is also the only way to negate a full-text term, since there is no !~=: -MentionedInExpressions[Content~='release'] is "no mentioning comment contains release".

The Value Is a Search Expression, Not a Literal

This is the part that catches people, and it follows from ~= being full-text: what you put on the right-hand side is parsed as a full-text search condition, not treated as the text to look for.

So AND, OR, NOT, NEAR, parentheses, double quotes and asterisks are all operators. A title that happens to contain any of them is not searched for literally — it is interpreted.

Before execution the value passes through a normalizer, and its defaults are the source of most surprises:

Behaviour	Effect
Inflectional stemming	Terms are wrapped in FORMSOF(INFLECTIONAL), so 'running' also matches run and ran. Matching is broader than the literal you typed.
Phrase stemming is per-instance	Controlled by an instance configuration setting, so identical queries can return different results on two instances.
* is a prefix wildcard only	'Cog*' matches words starting with Cog. An inner asterisk is trimmed at the first one — 'Cog*ent' becomes 'Cog*', a silently broader result. A leading asterisk is dropped to nothing, so '*Cog' matches nothing — there is no suffix or infix wildcard.
Unbalanced parentheses are patched	Not rejected — quietly repaired into something valid.
Unbalanced quotes are closed for you	The condition is closed at the end, and a lone inner quote is assumed to be intentional.
Invalid NEAR becomes AND	A malformed proximity search silently degrades into a conjunction.

Nothing in that list produces an error. Every malformed or unexpected input is repaired into a valid condition and executed, so a ~= filter always "works" — it just may not be the search you wrote.

Stop Words Are the Other Half of the Problem

~= runs against the same SQL Server full-text index the product's own search uses, so it inherits that index's stop word list — common words and single characters that are never indexed. A search term made only of stop words matches nothing, and there is no error to tell you why.

The effect is easiest to see in these real examples, which come from the product documentation and apply equally to ~=:

Term	Why it returns nothing
Iteration 1	1 is a single-character term
Book A	A is a single-character term
Page 18 of 42	of is a stop word
1.2.3 ABC	three single-character terms — treated as 1 AND 2 AND 3 AND ABC

This is the concrete reason an exact title often fails. Titles are full of short tokens, version numbers and connecting words, and once those are removed there may be nothing left to match on.

On hosted instances the SQL Server default list applies and cannot be changed. On on-premise installations the list is the customer's to alter.

The full list, and the search behaviour it drives, are published in the product documentation: Search stop words and Search Agility.

Practical advice

If you are searching for something a user typed, and you want it found rather than interpreted, use find instead — see below. Reserve ~= for cases where you actually want word-level search over indexed text and understand the normalization above.

If You Want a Substring Match, Use find

find and findin are the substring tool. find compiles to a genuine LIKE '%value%', optionally case-insensitive, so it matches partial words the way most people expect ~= to:

	~=	find / findin
Compiles to	Contains(col, term) — full-text	col LIKE '%value%'
Matches	whole words	any substring
'Cog' finds Cogent Refactor	no	yes
Needs a full-text index	yes	no
Scope	one named attribute	a set of attributes, defaulting to Name and ShortName
Cost	index lookup	table scan — slower on large sets

So the two are not variations on a theme. Choose ~= when you want word-level matching over indexed text and can live with stopwords; choose find when you want "does this string appear anywhere in the value".

State Attributes Do Not Compare Like Other Values

AssetState is a State-typed attribute, and comparisons against a named state do not behave like comparisons against an ordinary value. This is worth understanding before writing any filter on AssetState, because the surprising case is a common one.

States are stored as bytes and each named state is a range, not a point:

State	Byte	Range the name covers
Future	0	below 64
Active	64	64 to 127
Closed	128	128 to 191
Dead	192	192 to 254
Deleted	255	exactly 255

So AssetState='Closed' does not compile to = 128. It compiles to >= 128 AND < 192 — the whole Closed band. That part is intuitive enough.

danger

!= on a state is not a negation

AssetState!='Closed' compiles to < 128, not to "anything outside the Closed band".

That is narrower than it reads. It excludes Closed — and it also excludes Dead and Deleted, which are not Closed. The filter means "before Closed", not "not Closed".

Filter	Compiles to	Reads as	Actually means
AssetState!='Future'	>= 64	not Future	not Future ✓
AssetState!='Active'	< 64 OR >= 128	not Active	not Active ✓
AssetState!='Closed'	< 128	not Closed	before Closed — also excludes Dead and Deleted
AssetState!='Dead'	< 192	not Dead	before Dead — also excludes Deleted
AssetState!='Deleted'	< 255	not Deleted	not Deleted ✓

Note that !='Active' is a true negation while !='Closed' is not, so you cannot infer the behaviour of one case from another.

In practice !='Closed' is usually what people want, because they mean "still open" and Dead assets are not interesting. The trap is when you genuinely need "everything except Closed" — for that, say so explicitly with an OR of the states you want, rather than relying on !=.

Named and numeric forms behave alike

A number that matches a named state takes the named state's range: AssetState!='128' is < 128, exactly like AssetState!='Closed'. Any other number, such as 200 for a template, is compared as a plain byte.

Boolean Operators

Operator	Meaning
& and ;	AND
|	OR
( )	Grouping

& and ; are exact synonyms. Both parse to AND. ; exists because & separates parameters in a query string, so an unescaped & inside a where value ends the parameter. Prefer ; in hand-written URLs — it needs no escaping and reads unambiguously:

?where=AssetState!='Closed';Estimate>'3'

If you build URLs programmatically with proper parameter encoding, & inside the value is encoded as %26 and either form works.

The grammar gives AND and OR the same precedence and left-associates them (filter_expression is a flat repetition, not a precedence cascade). Do not rely on & binding tighter than | the way it would in C or SQL. Parenthesise whenever you mix them:

?where=(Status.Name='Ready'|Status.Name='Development');AssetState!='Dead'

Value Lists

A comparison may take a comma-separated list of values, which acts as an implicit in-list:

?where=Status.Name='Done','Accepted'

Single and double quotes are both accepted, and they are interchangeable — use double quotes when the value contains an apostrophe:

?where=Name="Bob's story"

The grammar defines a quoted string as an opening quote, any run of characters that are not that quote character, and a closing quote. There is no escape sequence. A value cannot contain the quote character that delimits it, and there is no way to write a value containing both ' and ". That is a genuine expressive limit, not an omission from this page.

Combined with a negated operator, a value list means "none of these":

?where=AssetState!='Closed','Dead'

The IsDeleted OR-Form Hack

One value-list idiom is worth calling out because it changes which assets the server returns rather than just narrowing them:

?where=IsDeleted='True','False'

Ordinarily the server excludes deleted assets automatically. Asserting both values defeats that exclusion, so deleted assets come back too. Core's own comment calls this "the OG IsDeleted filter hack" and names query.v1 and api/asset integrations as its users, so you will encounter it in existing integration code even though it is not the documented mechanism.

Two things to know before you rely on it:

• IsDeleted selects back as the string "True", not a boolean. Compare it as a string, and do not expect a JSON true when you select it.
• The supported way to include deleted assets is the deleted parameter. Prefer it in new code; recognise the hack when reading old code.

Unary Existence

A + or - prefixed to an attribute path asserts that the attribute has, or does not have, a value:

Form	Meaning
+Attr	The attribute is present / the set is non-empty
-Attr	The attribute is absent / the set is empty

GET /rest-1.v1/Data/Scope/0?sel=Workitems[-Owners;ToDo>'0']

That reads "workitems in the root project that have no owner and still have work remaining".

This is the only way to express a null test — there is no ='' null literal. -Owners on a multi-value relation means the relation is empty; -Description on a scalar means it has no value.

Do not confuse the unary operators with the sort direction operators, which use the same two characters in a different parameter. sort=-Estimate is descending order; where=-Estimate is "has no estimate".

Bare Attribute Names

The grammar permits a term that is just an attribute path with no operator at all — attribute_name , [ binary_operator … ] makes the comparison optional. It is not documented what a bare term evaluates to, and no shipped example uses one. Treat it as unspecified and write +Attr when you mean an existence test.

It matters for one reason: it is why the unencoded-# mistake can produce a successful response rather than an error. An unencoded # is a URI fragment delimiter, so the client truncates the URL there and never transmits the rest — ?where=Parent#.Name='Website' arrives as ?where=Parent, which parses as a bare term and does not fail. Truncate one character later and you get a 400 instead. See the danger note on attribute paths.

Variables

A filter value may be a variable instead of a literal list:

Form	Meaning
$NAME	The value assigned to NAME by the with parameter
$	The context asset — "self"

Variables save repeating a long list, and they are the only way to write a self-referential comparison. Assignment is covered on Filter context:

GET /rest-1.v1/Data/Epic
    ?where=AssetState!='Closed';Scope=$Scope;-SuperAndUp[Scope=$Scope]
    &with=$Scope=Scope:0,Scope:1,Scope:2

$ alone refers to the asset the expression is being evaluated against, which lets a nested inline filter compare an inner traversal back to the outer subject. The capstone example on the attribute-paths page is built around this.

Attribute Paths Inside Filters

The left-hand side of a term is a full attribute path, so everything on the attribute paths page is available here too — dot traversal, downcast, now-only and nested inline filters:

?where=Workitems:Story[AssetState!='Closed'].Category.Name='UI'
?where=Parent%23.Name='Website'

Aggregations are grammatically legal on the left-hand side of a comparison as well, since attribute_name includes the optional .@ suffix. No shipped example uses one there, so treat ?where=Workitems.@Count>'5' as unverified.

Errors

A where value that does not parse, or that names an attribute that does not exist on the asset type, produces 400 Bad Request with the message Invalid WHERE parameter and a nested Exception element giving the detail. See HTTP status codes.

Note that where is only honoured on list requests and on history requests. On a single-asset request such as /Data/Story/1055 the parameter is ignored without complaint — see which parameters apply to which request shape.

Related Topics

• Attribute paths
• Filter context
• Sort and paging
• Grammar reference