parse-kv

This operator is useful in scenarios where logs, traces, or security events contain metadata encoded as key-value pairs. Instead of manually splitting strings, you can use parse-kv to transform the data into a structured format.

Usage#

Syntax#

parse-kv Expression as (KeysList) with (pair_delimiter = PairDelimiter, kv_delimiter = KvDelimiter [, Options...])

Parameters#

Parameter	Description
`Expression`	The string expression that contains the key-value pairs.
`KeysList`	A list of keys to extract into separate columns.
`PairDelimiter`	A character or string that separates key-value pairs (for example, `;` or `,`).
`KvDelimiter`	A character or string that separates keys and values (for example, `=` or `:`).
`Options`	Additional parsing options, such as case sensitivity.

Returns#

A dataset where each specified key is extracted into its own column with the corresponding value. If a key is missing in the original string, the column is empty for that row.

Use case example#

When analyzing HTTP logs, you might encounter a field where request metadata is encoded as key-value pairs. You can extract values like status and duration for easier analysis.

Query

['sample-http-logs']
| parse-kv kvdata as (status, req_duration_ms) with (pair_delimiter=';', kv_delimiter='=')
| project _time, status, req_duration_ms, method, uri

Output

_time	status	req_duration_ms	method	uri
2024-05-01T10:00:00Z	200	120	GET	/home
2024-05-01T10:01:00Z	404	35	GET	/missing

This query extracts status and request duration from a concatenated field and projects them alongside other useful fields.

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, you often use the kv or extract commands to parse key-value pairs from raw log data. In APL, you achieve similar functionality with the parse-kv operator. The difference is that parse-kv explicitly lets you define which keys to extract and what delimiters to use.

Splunk example

... | kv pairdelim=";" kvdelim="=" keys="key1,key2,key3"

APL equivalent

datatable(data:string)
[
  'key1=a;key2=b;key3=c'
]
| parse-kv data as (key1, key2, key3) with (pair_delimiter=';', kv_delimiter='=')

ANSI SQL users

ANSI SQL does not have a direct equivalent of parse-kv. Typically, you would use string functions such as SUBSTRING or SPLIT_PART to manually extract key-value pairs. In APL, parse-kv simplifies this process by automatically extracting multiple keys in one step.

SQL example

SELECT
  SUBSTRING_INDEX(SUBSTRING_INDEX(data, ';', 1), '=', -1) as key1,
  SUBSTRING_INDEX(SUBSTRING_INDEX(data, ';', 2), '=', -1) as key2,
  SUBSTRING_INDEX(SUBSTRING_INDEX(data, ';', 3), '=', -1) as key3
FROM logs;