!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.

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.

Splunk SPL users

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.

Splunk example

index=web_logs | where NOT method IN ("OPTIONS", "HEAD", "TRACE")

['sample-http-logs']
| where method !in ('OPTIONS', 'HEAD', 'TRACE')

ANSI SQL users

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.

SQL example

SELECT * FROM sample_http_logs WHERE method NOT IN ('OPTIONS', 'HEAD', 'TRACE')

['sample-http-logs']
| where method !in ('OPTIONS', 'HEAD', 'TRACE')

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 is not found in the specified set. Returns false otherwise.

Use case examples

Log analysis

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 Playground

Output

_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.

OpenTelemetry traces

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, duration

Run in Playground

Output

_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 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.