Searching with the Boolean Operators AND and OR
One especially powerful feature of the Console is the fact that you can combine multiple search criteria in a single query. For example, you can locate all the users who live in Portland or who live in Seattle. You can find users who live in Portland and who haven’t updated their profile in the past three months. You can even pinpoint users who live in Portland or or who live in Seattle and who haven’t updated their profile in the past three months. All of this is done through the power of the Boolean operators AND and OR. In the following sections of this documentation we’ll explain how to use:
- The OR Operator
- The AND Operator
- Both the OR Operator and the AND Operator in the Same Query
The OR Operator
The OR operator lets you specify multiple criteria in a search, then return any profile that meets one or more of those specifications. For example, suppose you want to return information for users who live in the city of Portland or who live in the city of Seattle. More technically, you want to find all the users whose primaryAddress.city attribute is equal to Portlandor whose primaryAddress.city attribute is equal to Seattle. Here’s how you do that:
primaryAddress.city = "Portland" OR primaryAddress.city = "Seattle"
In other words:
- Criteria A (primaryAddress.city = “Portland”).
- The OR operator.
- Criteria B (primaryAddress.city = “Seattle”).
If you’re familiar with other search languages, you might wonder if you really need to specify the attribute name in each search. Yes, you do. Admittedly, this query (which doesn’t repeat the attribute name) won’t result in an error:
primaryAddress.city = "Portland" OR "Seattle"
However, that query won’t return the expected data, either. Instead, it returns all the users who either live in the city of Portland, or who have a display name or an email address equal to Seattle. On top of that, it won’t return any users who live in the city of Seattle. (Well, unless you have a user with the display name Seattle who happens to in Seattle.) Why does the query fail? The query fails because its syntax causes the Console to search for:
- Users who live in the city of Portland.
- Users who have the display name or the email address Seattle. Because we didn’t specify an attribute name, the Console looks for the target value in the default search attributes. That’s typically going to be displayName and email.
In other words, when constructing Boolean searches, you must include the attribute name with each clause:
primaryAddress.city = "Portland" OR primaryAddress.city = "Seattle"
And what if you want to add a third city to the list of “acceptable” cities? That’s fine; just include another OR clause (again, making sure that you specify the attribute name). Here’s how we look for users who live in Portland, Seattle, or Denver:
primaryAddress.city = "Portland" OR primaryAddress.city = "Seattle" OR primaryAddress.city = "Denver"
This can be continued ad infinitum. In this query, we add both Austin and Santa Fe to the list:
primaryAddress.city = "Portland" OR primaryAddress.city = "Seattle" OR primaryAddress.city = "Denver" OR primaryAddress.city = "Austin" OR primaryAddress.city = "Santa Fe"
primaryAddress.city = "Portland" || primaryAddress.city = "Seattle"
In other words, you can use OR (all uppercase letters, of course) or you can use ||. That’s up to you.
Incidentally, if you have a background in SQL, you might be familiar with the IN operator, which offers an easy way to select from a list of values:
Select * FROM users WHERE city IN ("Portland", "Seattle", "Denver", "Austin", "Santa Fe")
For now, at least, the Console does not have a search operator similar to IN. In the Console, you need to write out the entire query; there’s no way to simply specify a list of allowed values.
The AND Operator
The OR operator lets you specify multiple criteria in a search and then return user profiles that meet one (or more) of those specifications. But what if you want to specify multiple criteria and return only the profiles that meet all of those specifications? In that case, the OR operator won’t help you. Instead, that’s the job of the AND operator.
Here’s an example. Suppose we want to return a list of users who live in the city of Portland and who haven’t updated their profile since October 1, 2017. The following query returns those users:
primaryAddress.city = "Portland" AND lastUpdated < 2017-10-01
If we look at a list of locations and last update times it’s easy to see why some users (the boldfaced ones) get returned while others don’t
- 2017-10-02 Portland
- 2017-10-01 Seattle
- 2017-09-29 Portland
- 2017-09-27 Portland
- 2017-09-25 Seattle
As you can see, we only have two users who live in the city of Portland, and haven’t updated their user profile since September 30, 2017. Those are the same two users returned by our AND query.
Generally speaking, AND searches are conducted using multiple attributes: in the previous query, we looked for values in the primaryAddress.city attribute and in the lastUpdated attribute. And that makes sense: you can’t do an AND search with, say, a user’s last name, because a user can’t have a last name of Smith and simultaneously have a last name of Jones. However, there is one exception to this general rule: plurals. A plural attribute is an attribute that can store more than one value; for example, a user who has both a Google account and a Facebook account might have the values google.com and facebook.com stored in their profiles.domain attribute. Because of that, the Console lets you search for users who have both a Google account and a Facebook account by using a query similar to this:
profiles.domain = "Google.com" AND profiles.domain = "Facebook.com"
Just make sure you reference the full path to the attribute: it’s profiles.domain, not just domain.
Similar to OR queries, you’re not limited to a single AND clause. For example, this query returns all the users who live in Portland, work for Akamai, and have not updated their profile since October 1, 2017:
primaryAddress.city = "Portland" AND lastUpdated < 2017-10-01 AND primaryAddress.company = "Akamai"
Effectively, that’s three separate queries bound together by a couple of AND operators.
displayName = "Bob*" AND primaryAddress.city = "Portland" displayName = "Bob*" && primaryAddress.city = "Portland"
It’s up to you.
Using AND and OR Together
When it comes to running Boolean searches in the Console, you might find yourself wondering, “Hey, could I use both the AND operator and the OR operator in the same query?” As it turns out, you can. You just need to take a little bit of care when formatting those queries.
To explain what we mean by that, let’s take a look at a sample query that includes multiple criteria:
familyName = Johnson OR familyName = Johnston AND primaryAddress.city = Portland
That query looks simple enough, or at least it does until someone asks you: what exactly are you searching for here? That’s a good question, and the answer depends on how we (or, more correctly, how the Console) parses the query. After all, we could be asking for all the users who:
- Have the last name Johnson or Johnston, and
- Live in Portland
Alternatively, we could be asking for all the users who:
- Have the last name Johnson, or
- Have the last name Johnston and live in Portland
Two different questions, and two different answers, a conundrum reminiscent of basic math. For example, what’s the answer to this problem:
5 + 2 * 7
As you might recall from your junior high days, that depends. It could be this:
(5 + 2) * 7 = 49
Or it could be this:
5 + (2 * 7) = 19
Why are there two answers to the “same” question? That’s because, in basic math, the answer you come up with depends on how you group the individual terms in the equation (i.e., where you put the parentheses). The same thing is true when it comes to search queries. If we want all the users named Johnson or Johnston and who live in Portland, we need to write our query like this, with the parentheses around the two familyName clauses:
(familyName = Johnson OR familyName = Johnston) AND primaryAddress.city = Portland
In this example, the Console first looks for users with the familyName Johnson or Johnston. Why? Because we put parentheses around those two search clauses, and the Console always acts on items in the parentheses before it does anything else. After the Console grabs all the Johnsons and Johnstons, it then winnows the list even further by returning only the Johnsons/Johnstons who live in Portland.
And what if we really do want all the Johnsons, plus all the Johnstons who live in Portland? In that case, we need to write a query like this:
familyName = Johnson OR (familyName = Johnston AND primaryAddress.city = Portland)
This time, and based on the placement of the parentheses, the Console first returns all the Johnstons who live in Portland. It then augments the returned data with all the Johnsons, regardless of where they live.
And yes, you can make your queries as complicated as you want, as long as you keep adding parentheses:
(familyName = Johnson AND primaryAddress.city = Seattle) OR (familyName = Johnston AND primaryAddress.city = Portland)
Or even this:
(familyName = Johnson AND primaryAddress.city = Seattle) OR (familyName = Johnston AND (primaryAddress.city = Portland AND primaryAddress.stateAbbreviation = "OR"))
In the preceding example, the Console starts with the innermost clause (primaryAddress.city = Portland AND primaryAddress.stateAbbreviation = “OR”) and returns all the users who live in Portland, OR. It then works its way out to the next set of parentheses, and then the next set, and then the next set, until all the search criteria have been accounted for.
Good question: what if you forget to use any parentheses in your query? To be honest, and depending on how complicated your query is, it’s hard to say what you’ll get back. As a general rule, however, the Console processes all the AND operators before it processes any OR operators. Take this query for example:
familyName = Johnson OR familyName = Johnston AND city = Portland
The Console processes the preceding query as if we had written it like this, first processing the AND clause, and then processing the OR clause:
familyName = Johnson OR (familyName = Johnston AND city = Portland)