!in

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#

Log analysisOpenTelemetry traces

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.

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)

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.

Other query languages#

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")

APL equivalent

['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')