String and Regex Functions
These functions pull values out of text fields at query time. To filter records with a regular expression, use re_match and re_not_match.
All patterns use Rust regex syntax. Look-ahead, look-behind, and back-references are not supported.
Function index
| Function | Purpose |
|---|---|
re_matches | Return every match of a pattern as an array |
regexp_match_to_fields | Return named capture groups as a struct with one field per group |
string_to_array_v2 | Split a string on a set of delimiter characters |
re_matches
Syntax: re_matches(field, 'pattern')
Description:
- Returns an array with one entry per match of
patterninfield. - When the pattern has capture groups, each entry is the innermost group that matched. Without groups, each entry is the whole match.
- Double quotes around an extracted value are removed, so
"value":"hello"with pattern"value":("[^"]+")giveshello. - Returns
NULLwhen the pattern does not match.
Example:
SELECT _timestamp, log, re_matches(log, 'user=(\w+)') AS users FROM "docs_sql_examples" WHERE re_match(log, 'user=')For a log line login ok user=alice retry user=bob, users is ["alice", "bob"].
Gotchas
- Filter with
re_matchfirst, as in the example.re_matchesruns on every row it is given and is slower than a filter. - The result is an array. Use array functions such as
array_length(users)orusers[1](1-based) to work with it.
regexp_match_to_fields
Syntax: regexp_match_to_fields(field, 'pattern')
Description:
- Matches
patternagainstfieldand returns a struct with one field per named capture group,(?P<name>...)or(?<name>...). - Useful to break one unstructured line into named parts without a pipeline function.
Example:
SELECT regexp_match_to_fields(log, '(?P<client_ip>[^\s]+) (?P<method>[A-Z]+) (?P<path>[^\s]+)') AS parsed FROM "docs_sql_examples" WHERE str_match(log, 'GET') LIMIT 1For a log line 15.128.22.213 GET /api/orders 200, parsed is {client_ip: 15.128.22.213, method: GET, path: /api/orders}.
Gotchas
- The pattern must use named capture groups. A pattern with only unnamed groups fails the query.
- The function returns the captures of the first row it reads, for every row in that batch. Use it on a single record (as with
LIMIT 1above), or usere_matchesor a VRL function to parse many rows. - If the first row does not match the pattern, the query fails.
string_to_array_v2
Syntax: string_to_array_v2(field, 'delimiters')
Description:
- Splits
fieldinto an array of strings. - Every character in
delimitersis a separator on its own.', ;'splits on commas, spaces, and semicolons. - Empty parts are dropped, so
a,,bgives["a", "b"]. - An empty
delimitersstring returns the whole value as a one-element array.
Example:
SELECT _timestamp, path, string_to_array_v2(path, '/?&') AS path_parts FROM "docs_sql_examples"For a path of /api/orders?id=42&page=2, path_parts is ["api", "orders", "id=42", "page=2"].
Gotchas
delimitersis a set of single characters, not a multi-character separator.string_to_array_v2(x, '::')splits on every:. For a multi-character separator, use the DataFusionstring_to_arrayfunction.- To work with stringified JSON arrays, use the array functions instead.
Next steps
- SQL functions reference: full list of functions you can use in queries.
- Full-text search functions: filter records by keyword or pattern.
Need some help?
- Join our Community Slack
- Or Contact support
Array Functions
Array functions for stringified JSON arrays in OpenObserve: sort, count, extract, join, and combine elements with arrsort, arrjoin, arrindex, and more.
Date and Time Functions
OpenObserve SQL date and time functions: filter by a time range, format timestamps in a time zone, and parse text into microsecond timestamps.