!in
This page explains how to use the !in operator in APL.
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.
Usage
Syntax
Expression !in (Value1, Value2, ...)Parameters
| Name | Type | Required | Description |
|---|---|---|---|
| Expression | scalar | Yes | The value to check against the exclusion set. |
| Value | scalar or tabular | Yes | The 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 isn't 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, statusOutput
| _time | method | uri | status |
|---|---|---|---|
| 2024-10-17 10:20:00 | GET | /api/missing | 404 |
| 2024-10-17 10:25:30 | POST | /api/data | 500 |
| 2024-10-17 10:30:45 | GET | /api/forbidden | 403 |
This query filters the HTTP logs to return only requests that did not result in successful status codes, helping you identify errors and issues.
Exclude traces from infrastructure services to focus on application-level spans.
Query
['otel-demo-traces']
| where ['service.name'] !in ('load-generator', 'flagd', 'frontendproxy')
| project _time, trace_id, ['service.name'], kind, durationOutput
| _time | trace_id | service.name | kind | duration |
|---|---|---|---|---|
| 2024-10-17 11:00:00 | abc123 | frontend | server | 45ms |
| 2024-10-17 11:00:05 | def456 | checkout | server | 120ms |
| 2024-10-17 11:00:10 | ghi789 | product-catalog | client | 30ms |
This query filters traces to exclude infrastructure and support services, helping you analyze only the core application services.
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)List of related operators
- in: Use for case-sensitive matching to include values. Returns
trueif 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
!inoperator is commonly used withinwhereclauses. - !=: Use for single value inequality checks. Use
!inwhen checking against multiple values for more concise queries.