List filter syntax and usage

This guide describes the list filter syntax and how to filter various resource types.

Some API methods can accept a filter to limit the resources returned in the response.

Summary

This section provides a quick overview of the list filter syntax structure.

A filter is a string containing an expression. An expression is a Boolean combination of comparisons:

expression = ["NOT"] comparison { ("AND" | "OR") ["NOT"] comparison }
expression = ( expression )

A comparison matches a resource field with a value. You can use all the common comparison operators.
```
comparison = name OP value
OP = "<=" | "<" | ">=" | ">"  | "!=" | "=" | ":"
```
The has operator, a colon (:), can be used on strings and repeated fields. See the Has operator section for details.
You can use the following types of values in filters:
- Numbers
- Strings
- Parenthesized expressions
```
value = number| string | "*" | "(" expression ")"
```
Strings can represent the following:
- Arbitrary text
- Booleans
- Enum values
- Timestamps

Boolean expressions

expression = ["NOT"|"-"] comparison {["AND" | "OR"] ["NOT"|"-"] comparison}

Operations are done in the following order:

NOT
OR
AND

For example, the following expressions are equivalent:

a OR NOT b AND NOT c OR d

(a OR (NOT b)) AND ((NOT c) OR d)

You can omit the AND operator between comparisons. For example, the following filters are the same:

c=d AND e=f

c=d e=f

You can use the hyphen (-) as an alternative for NOT. There cannot be a space between the hyphen (-) and the following comparison. For example, the following filters are the same:

NOT e=f

-e=f

Comparisons

This section describes "name OP value" comparisons like the following:

comparison = name OP value

where

OP = "<=" | "<" | ">=" | ">" | "!=" | "=" | ":"
name = identifier { "." identifier }
identifier = unquoted_text
value = number | string | "*" | "(" expression ")"

The left side of a comparison is the path name of an API resource field. The name consists of a series of resource identifiers connected by period (.). Each field identifier is followed by the next level of names for that field. For example, consider a resource having a complex field item that has another complex field tool, that has a field named shape. In a filter for this resource, you would refer to shape with the name item.tool.shape.

The right side is typically a scalar value that is converted to the field's type and compared against it. See the Value Literal types section for more details.

The right side of a comparison can also be expressed as a parenthesized Boolean combination of literal values and/or boolean expressions that contains only literal values (preceded with or without NOT). The left side name and the comparison operator are applied to each of the values. For example, the following filters are the same:

deal.name = ("test 1" OR "test 2")

deal.name = "test 1" OR deal.name = "test 2"

Here's another, more complex example of two equivalent filters:

deal.name = ("test 1" OR "test 2" AND (NOT "test3" OR "test4"))

(deal.name = "test 1" OR deal.name = "test 2") AND ( (NOT deal.name = "test3") OR deal.name = "test4")

Value Literal types

The right side value of a Comparison operator can be categorized into Number and String literals.

Number

This section describes the representation of numeric literals.

Type	Definition	Examples
Double	Any number that contains a decimal point, with or without a sign ("-") is treated as a Double.	1234.567 -789.0123
Integer	Any number that doesn’t have a decimal point, with or without a sign ("-") is treated as an integer.	1234 -789

String

This section describes the types that you can write as a string literal in the filter syntax.

Type	Definition	Examples
Boolean	`TRUE` or `FALSE` in any letter case.	`TRUE` `True` `"true"`
Enum	The name of an enumeration type literal. Enums are case-sensitive.	`FINALIZED` isn't the same as `Finalized`
String	Any string that contains UTF-8 encoded or 7-bit ASCII text. Embedded quotation marks must be escaped with a backslash. Unquoted strings with whitespace are treated as implicit `AND` among all the words after splitting the string by whitespace.	`name = "test \"double quotes\""` `name=(ABC DEF)` is equivalent to `name=ABC AND name=DEF`
Timestamp	A string in the ISO8601 standard format.	`"2014-10-02T15:01:23.045Z"`

Comparison operators

Here are the comparison operators:

Less than or equal to: "<="
Less than: "<"
Greater than or equal to: ">="
Greater than: ">"
Not equal to: "!="
Equal to: "="
Has: ":"

These operators apply to Double, Integer, Boolean, Enum and Timestamp value types.

Has operator

You can use the HAS operator (:) for special operations on the following fields:

Substrings

When the HAS operator is used for comparing values on a string column to a string, the operator will act as a substring operation. For example, name:"abcd" returns all instances where name is a string containing "abcd".

Existence checking

When you use the HAS operator with the special character *, the HAS operator checks for non-null values. For example, name:* returns all instances where name isn't null, missing, or undefined.

When you use the HAS operator with non-string values, it behaves the same as the EQUALS (=) operator. For example, isCompleted:true behaves in the same way as isCompleted = true.

Repeated Fields

You can use the HAS (:) operator to filter on a repeated API resource field, as long as the following are true:

There's only one repeated component along the field identifier path
The last identifier of the field path is of scalar type

Filtering on nested repeated fields isn't supported.

Here's an example:

item has a colors field, which contains string values like "red", "blue", and "yellow".

item.colors:("red") returns all items that have the value "red" in the colors field.
item.colors:("red" "yellow") returns all items that have both "red" and "yellow" in the colors field.
item.colors:("red" OR "yellow") returns all items that have "red" or "yellow" in the colors field.

item also has a repeated tools field that is a complex object with a scalar field shape, whose values can be "square" or "round".

item.tools.shape:("square") returns all items that have "square" shaped tools.
item.tools.shape:("square" "round") returns all items that have both a "square" shaped tool and a "round" shaped tool.
item.tools.shape:("square" OR "round") returns all items that have a "square" shape tool or a "round" shaped tool.

Unpopulated nested fields

Nested fields are sub-fields of root-level fields, for example shape in item.tools.shape is a nested field of items.tools.

Root-level fields default to false. Nested fields are unpopulated by default.

Objects with unpopulated nested fields aren't returned by negative filters (!=).

Here's an example:

item.tools has a size enum whose value can be set to "SMALL", "MEDIUM", or "LARGE".

If you have the following items:

{
  "name": "item1",
  "tools": {
    "size": "MEDIUM"
  }
},
{
  "name": "item2",
  "tools": {
    "size": "LARGE"
  }
},
{
  "name": "item3"
}

A call to items.list with the negative filter "tools.size != SMALL" returns the following:

{
  "items": [
    {
      "name": "item1",
      "tools": {
        "size": "MEDIUM"
      }
    },
    {
      "name": "item2",
      "tools": {
        "size": "LARGE"
      }
    }
  ]
}

Since item.tools.size hasn't been set for item3, the negative filter doesn't return the item3 object.

Examples

Example	Description
`externalDealId = "123456789"`	`externalDealId` that has a string value "123456789".
`advertiserId:93641 advertiserId = 93641`	`advertiserId` that has an integer value 93641.
`isSetupComplete = true isSetupComplete:TRUE isSetupComplete = (True)`	`isSetupComplete` is equal to TRUE.
`updateTime > "2018-02-14T11:09:19.378Z"`	`updateTime` is later than 02/14/2018 11:09:19.378 UTC
`displayName = "proposal" AND proposalRevision = 3 displayName = "proposal" proposalRevision = 3`	`displayName` string has an identical value of "proposal" AND proposalRevision is equal to 3.
`displayName = "proposal" OR proposalRevision = 3`	`displayName` has a string value of "proposal" OR the proposalRevision is equal to 3.
`NOT displayName = "proposal" displayName != "proposal"`	`displayName` is not equal to "proposal".
`proposalState = (PROPOSED OR BUYER_ACCEPTED) proposalState = PROPOSED OR proposalState = BUYER_ACCEPTED`	`proposalState` has an enum value that is either equal to PROPOSED OR BUYER_ACCEPTED.
`proposalState = (PROPOSED AND BUYER_ACCEPTED) proposalState = (PROPOSED BUYER_ACCEPTED) proposalState = PROPOSED AND proposalState = BUYER_ACCEPTED proposalState = PROPOSED proposalState = BUYER_ACCEPTED`	`proposalState` has an enum value that is equal to PROPOSED AND BUYER_ACCEPTED
`dealName = Test Deal`	`INVALID` expression
`dealName = "Test Deal"`	`dealName` is equal to "Test Deal".
`dealName = (Test Deal)`	`dealName` is equal to "Test" and also equal to "Deal".
`dealName = ("Test1" OR "Test2") dealName = "Test1" OR dealName = "Test2"`	`dealName` is either equal to "Test1" or equal to "Test2".
`dealName:*`	`dealName is not null.`
`dealName:"test" dealName:test`	`dealName` contains the substring "test".
`dealName:("A B") dealName:"A B"`	`dealName` contains the substring "A B".
`dealName:(A B) dealName:"A" AND dealName:"B"`	`dealName` contains the substring "A" and the substring "B".
`dealName:("A" OR "B" AND "C") dealName:("A" OR "B" "C") dealName:"A" OR dealName:"B" AND dealName:"C" dealName:"A" OR dealName:"B" dealName:"C" (dealName:"A" OR dealName:"B") AND dealName:"C" (dealName:"A" OR dealName:"B") dealName:"C"`	`dealName` contains either substring "A" OR "B" AND also contains substring "C"
`dealName:("A B" C) dealName:"A B" AND dealName:"C"`	`dealName` contains substring "A B" and also contains substring "C".
`dealName:("A B" OR C D)`	`dealName` contains either the substring "A B" or "C", and also contains substring "D".
`dealName:(NOT "A" B) NOT dealName:"A" AND dealName:"B" (NOT dealName:"A") AND dealName:"B" (NOT dealName:"A") dealName:"B"`	`dealName` does not contain any substring "A" and also contains substring "B".
`dealName:(NOT "A" OR "B") NOT dealName:"A" OR dealName:"B" (NOT dealName:"A") OR dealName:"B"`	`dealName` does not contain any substring "A" or contains substring "B".