parse-where

You can use parse-where when working with logs or event data that follow a known structure but may contain noise or irrelevant lines. For example, you can parse request logs to extract structured information like HTTP method, status code, or error messages, and automatically discard any rows that don’t match the format.

Usage#

Syntax#

parse-where [kind=kind [flags=regexFlags]] expression with [*] stringConstant columnName [:columnType] [*]

Parameters#

Parameter	Description
`kind`	(Optional) Specifies the parsing method. The default is `simple`. You can also specify `regex` for regular expression parsing.
`flags`	(Optional) Regex flags to control the behavior of pattern matching. Used only with `kind=regex`.
`expression`	The string expression to parse.
`stringConstant`	The constant parts of the pattern that must match exactly.
`columnName`	The name of the column where the extracted value is stored.
`columnType`	(Optional) The type of the extracted value (for example, `string`, `int`, `real`).

Returns#

The operator returns a table with the original columns and the newly extracted columns. Rows that don't match the parsing pattern are removed.

Use case example#

You want to extract the HTTP method and status code from request logs while ignoring rows that don’t follow the expected format.

Query

['http-logs']
| parse-where uri with '/' method:string '/' * 'status=' status:string
| project _time, method, status, uri

Output

_time	method	status	uri
2025-09-01T12:00:00Z	GET	200	/GET/api/items?status=200
2025-09-01T12:00:05Z	POST	500	/POST/api/orders?status=500

This query extracts the method and status from the uri field and discards rows where the uri doesn't match the pattern.

extend: Adds calculated columns. Use when parsing isn't required but you want to create new derived columns.
parse: Extracts values from a string expression without filtering out non-matching rows. Use when you want to keep all rows, including those that fail to parse.
project: Selects and computes columns without parsing. Use when you want to transform data rather than extract values.
where: Filters rows based on conditions. Use alongside parsing functions if you want more control over filtering logic.

Other query languages#

Splunk SPL users

In Splunk SPL, you use the rex command with the max_match=1 option to extract fields and filter out non-matching events. In APL, parse-where provides the same functionality in a more direct way. Rows that do not match the pattern are automatically excluded.

Splunk example

... | rex field=log_line "\[(?<level>\w+)\] (?<message>.+)"

APL equivalent

datatable(log_line:string)
[
    '[INFO] Service started',
    'invalid line'
]
| parse-where log_line with '[', level:string, '] ', message:string

ANSI SQL users

ANSI SQL does not have a direct equivalent to parse-where. You often use LIKE or REGEXP functions to test string patterns and then combine them with CASE expressions to extract substrings. In APL, parse-where simplifies this by combining extraction and filtering into one operator.

SQL example

SELECT
  REGEXP_SUBSTR(log_line, '\\[(\\w+)\\]', 1, 1) AS level,
  REGEXP_SUBSTR(log_line, '\\] (.+)', 1, 1) AS message
FROM logs
WHERE log_line REGEXP '\\[(\\w+)\\] (.+)';