Boolean Operators

AVAILABLE API VERSION
API v56.0 and later

To filter the result set, a query combines several predicates. Boolean operators can help you expand or narrow your query within the where filter. Use the OR function to combine filters. Use the AND function to intersect filters. The NOT function inverts a filter.

The filter types have fields for the AND, OR, and NOT functions. AND and OR take in a list, while NOT takes in a single filter to invert. When combined, the filters allow for arbitrary boolean expressions.

1input Account_Filter {
2  and: [Account_Filter]
3  or:  [Account_Filter]
4  not: Account_Filter
5}

Each filter within the list of filters is joined by the named operator for AND or OR functions.

AND Expressions

Let’s look at a list of filters with the and operator.

1where: {
2  and: [ {filter1}, {filter2} ]
3}

Each filter is joined with the AND SOQL operator.

1WHERE (filter1) AND (filter2)

Here’s an example of the previous list of filters.

1where: {
2  and: [{
3    AnnualRevenue: {
4      gt: 100000
5    }
6},
7  {
8    AnnualRevenue: {
9      lt: 1000000
10    }
11  }]
12}

Similarly, consider the following filters.

1where: {
2  and: [filter1, filter2, filter3]
3}

The implicit filter with the and operator is similar to a SOQL statement that looks like this.

1WHERE (filter1 AND filter2 AND filter3)

Whenever two operators have a value on the filter type, the query combines them with an implicit AND operator.

1Opportunity(
2  where: {
3    NextStep: {eq: "Need estimate"}
4    CloseDate: {
5      CALENDAR_MONTH: {
6        value: {
7          eq: 10
8        }
9      }
10    }
11  }
12)

This query is similar to the following SOQL statement. It assumes that Id and Name are included in a SELECT clause.

1FROM Opportunity
2WHERE NextStep='Need estimate' AND CALENDAR_MONTH(CloseDate)=10

Similarly, the following query contains multiple operators eq and gt with a value on the filter type.

1Account(
2  where: {
3    AnnualRevenue: {gt: 1000000}
4    Ownership: {eq: "Public" }
5    ShippingCity: {
6      eq: "Tokyo"
7    }
8  }
9)

The previous query is similar to the following SOQL statement. It assumes that Id and Name are included in a SELECT clause.

1SELECT Id, Name
2FROM Account
3WHERE AnnualRevenue>1000000 AND Ownership='Public' AND ShippingCity='Tokyo'

OR Expressions

Similarly, a filter with the or operator looks like this.

1where: {
2  or: [filter1, filter2, filter3]
3}

The OR function is similar to the following SOQL statement.

1WHERE (filter1 OR filter2 OR filter3)

Let’s look at how to use the or filter type.

1Account(
2  where: {
3    or: [
4        { AnnualRevenue: {gt: 10000000} }
5        { Ownership: {eq: "Public"} }
6    ]
7  }
8)

The previous query is similar to this SOQL statement with the OR operator. It assumes that Id and Name are included in a SELECT clause.

1SELECT Id, Name
2FROM Account
3WHERE AnnualRevenue>10000000 OR Ownership='Public'

NOT Expressions

The NOT function inverts the predicate within the filter.

1Account(
2  where: {
3    not: {
4      ShippingCity: {
5        eq: { "Tokyo" }
6      }
7    }
8  }
9)

The previous query is similar to the following SOQL statement with the NOT operator. It assumes that Id and Name are included in a SELECT clause.

1SELECT Id, Name
2FROM Account
3WHERE NOT ShippingCity='Tokyo'

The NOT function can be used with the binary functions as well.

1where: {
2  not: {
3    and: [filter1, filter2]
4  }
5}

The previous NOT function is similar to the following SOQL statement.

1WHERE NOT (filter1 AND filter2)

This query uses the not filter type with multiple and filters.

1Opportunity(
2  where: {
3    not: { Probability: { eq: 100 }
4           StageName: { eq: "Closed Won" }
5    }
6  }
7)

The previous query is similar to the following SOQL statement with the WHERE NOT clause.

1SELECT Id, Name
2FROM Opportunity
3WHERE NOT (Probability=100 AND StageName='Closed Won')

Filter Nesting

Furthermore, we can have arbitrary nesting of filters.

1where: {
2  and: [
3    {or: [filter11, filter12]},
4    {or: [filter21, filter22]}
5  ]
6}

The nesting of filter is similar as follows.

1WHERE ( (filter11 OR filter12) AND (filter21 OR filter22) )

This query uses nesting of filters.

1Opportunity(
2    where: {
3      or: [
4        {
5          Probability: { eq: 100 },
6          StageName: { eq: "Closed Won" }
7        },
8        {
9          Type: { eq: "New Customer" },
10          ExpectedRevenue: { gt: 1000000 }
11        }
12      ]
13    }
14)

The previous query is similar to the following SOQL statement.

1SELECT Id, Name
2FROM Opportunity
3WHERE (Probability=100 AND StageName='Closed Won') OR (Type='New Customer' AND ExpectedRevenue>1000000)