!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-insensitive comparison. Use this operator to exclude records where a field value equals one of several values regardless of letter case, 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 case-insensitive matching, such as filtering out known HTTP methods or status codes regardless of their capitalization in the data.
Usage
Syntax
Expression !in~ (Value1, Value2, ...)Parameters
| Name | Type | Required | Description |
|---|---|---|---|
| Expression | scalar | Yes | The value to check against the exclusion set, ignoring letter case. |
| 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 doesn't match any value in the specified set (case-insensitive). Returns false otherwise.
Use case examples
Filter HTTP logs to exclude certain methods regardless of case.
Query
['sample-http-logs']
| where method !in~ ('options', 'head')
| project _time, method, uri, statusOutput
| _time | method | uri | status |
|---|---|---|---|
| 2024-10-17 10:15:00 | GET | /api/users | 200 |
| 2024-10-17 10:16:30 | POST | /api/data | 201 |
| 2024-10-17 10:17:45 | DELETE | /api/item | 204 |
This query filters the HTTP logs to exclude OPTIONS and HEAD requests regardless of case, helping you focus on substantive requests.
Exclude internal span kinds regardless of case variations.
Query
['otel-demo-traces']
| where kind !in~ ('internal', 'producer', 'consumer')
| 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 | client | 120ms |
| 2024-10-17 11:00:10 | ghi789 | cart | Server | 30ms |
This query filters traces to exclude internal and messaging spans, helping you focus on client-server interactions.
Performance considerations
When two operators perform the same task, use the case-sensitive one (!in) for better performance. Use !in~ only when case-insensitive exclusion is necessary.
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(['a', ['b', 'c']])) is equivalent to x !in~ ('a', 'b', 'c').
let excluded_methods = dynamic(['options', 'head', 'trace']);
['sample-http-logs']
| where method !in~ (excluded_methods)List of related operators
- in: Use for case-sensitive matching to include values.
- !in: Use for case-sensitive exclusion. Better performance than
!in~. - in~: Use for case-insensitive matching to include values.
- where: Use to filter rows based on conditions. The
!in~operator is commonly used withinwhereclauses. - !~: Use for single value case-insensitive inequality checks. Use
!in~when checking against multiple values.