Skip to main content
The !in operator in APL filters records based on whether a value doesn’t match any element in a specified set using case-sensitive comparison. Use this operator to exclude records where a field value equals one of several values, which is more concise and efficient than chaining multiple inequality checks with and. The !in operator works with any scalar type, including strings, numbers, booleans, datetime values, and dynamic arrays. Use the !in operator when you need to exclude specific values with exact case-sensitive matching, such as filtering out known good status codes, excluding requests from specific regions, or removing traces from certain services.

​
For users of other query languages

If you come from other query languages, this section explains how to adjust your existing queries to achieve the same results in APL.
In Splunk SPL, you negate the IN function using NOT to exclude values. APL’s !in operator provides a more concise syntax for the same operation and is case-sensitive by default.
index=web_logs | where NOT method IN ("OPTIONS", "HEAD", "TRACE")
In ANSI SQL, you use NOT IN within a WHERE clause to exclude rows where a column value matches any value in a list. APL’s !in operator behaves the same way but is case-sensitive for string comparisons.
SELECT * FROM sample_http_logs WHERE method NOT IN ('OPTIONS', 'HEAD', 'TRACE')

​
Usage

​
Syntax

Expression !in (Value1, Value2, ...)

​
Parameters

NameTypeRequiredDescription
ExpressionscalarYesThe value to check against the exclusion set.
Valuescalar or tabularYesThe values to exclude. Specify individual scalar values, a dynamic array, or a subquery. When using a subquery with multiple columns, APL uses the first column. The operator supports up to 1,000,000 unique values in the set.

​
Returns

Returns true if the expression value is not found in the specified set. Returns false otherwise.

​
Use case examples

Filter HTTP logs to exclude successful responses and focus on potential issues.Query
['sample-http-logs']
| where status !in ('200', '201', '204')
| project _time, method, uri, status
Run in PlaygroundOutput
_timemethoduristatus
2024-10-17 10:20:00GET/api/missing404
2024-10-17 10:25:30POST/api/data500
2024-10-17 10:30:45GET/api/forbidden403
This query filters the HTTP logs to return only requests that did not result in successful status codes, helping you identify errors and issues.

​
Use with dynamic arrays

When you pass a dynamic array with nested arrays, APL flattens them into a single list. For instance, x !in (dynamic([1, [2, 3]])) is equivalent to x !in (1, 2, 3). 
let error_codes = dynamic(['500', '502', '503', '504']);
['sample-http-logs']
| where status !in (error_codes)
  • in: Use for case-sensitive matching to include values. Returns true if the value is in the set.
  • in~: Use for case-insensitive matching. Matches values regardless of case.
  • !in~: Use for case-insensitive exclusion. Excludes values regardless of case.
  • where: Use to filter rows based on conditions. The !in operator is commonly used within where clauses.
  • !=: Use for single value inequality checks. Use !in when checking against multiple values for more concise queries.