!in~

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#

Log analysisOpenTelemetry traces

Filter HTTP logs to exclude certain methods regardless of case.

Query

['sample-http-logs']
| where method !in~ ('options', 'head')
| project _time, method, uri, status

Run in Playground

Output

_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, 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	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)

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 within where clauses.
!~: Use for single value case-insensitive inequality checks. Use !in~ when checking against multiple values.

Other query languages#

Splunk SPL users

In Splunk SPL, string comparisons are case-insensitive by default. APL requires the explicit !in~ operator for case-insensitive exclusion. Use !in~ when you want to exclude values regardless of case.

Splunk example

index=web_logs | where NOT method IN ("options", "head")

APL equivalent

['sample-http-logs']
| where method !in~ ('options', 'head')

ANSI SQL users

In ANSI SQL, the NOT IN operator's case sensitivity depends on the database collation. APL's !in~ operator explicitly performs case-insensitive exclusion, similar to SQL databases with case-insensitive collation.

SQL example

SELECT * FROM sample_http_logs WHERE LOWER(method) NOT IN ('options', 'head')