SQL calculations for structured data - Simple Log Service

Use the extend and where SPL instructions to run SQL calculations on structured data.

extend

Creates a new field by evaluating an SQL expression. Supported functions are listed in SQL functions supported by SPL.

Syntax

| extend <output>=<sql-expr>, ...

Parameters

Parameter	Type	Required	Description
output	Field	Yes	The destination field name. Multiple expressions cannot output to the same field. Important If a field with this name already exists in the input data, it is overwritten.
sql-expr	SQLExpr	Yes	The data processing expression. Important Handle null values in SPL expressions.

Examples

Example 1: Use a calculation expression.

* | extend Duration = EndTime - StartTime

Example 2: Use a regular expression.

* | extend server_protocol_version=regexp_extract(server_protocol, '\d+')

Example 3: Extract from a JSON path and cast a field type.

SPL statement

*
| extend a=json_extract(content, '$.body.a'), b=json_extract(content, '$.body.b')
| extend b=cast(b as BIGINT)

Input data
```
content: '{"body": {"a": 1, "b": 2}}'
```

Output result

content: '{"body": {"a": 1, "b": 2}}'
a: '1'
b: 2

where

Filters data based on a boolean SQL expression. Only entries that evaluate to true are retained. Supported functions are listed in SQL functions supported by SPL.

Syntax

| where <sql-expr>

Parameters

Parameter	Type	Required	Description
sql-expr	SQLExp	Yes	The SQL expression. Matching entries are retained. Important Handle null values in SPL expressions.

Examples

Example 1: Filter by field value.
```
* | where userId='123'
```

Example 2: Filter with a regular expression.

* | where regexp_like(server_protocol, '\d+')

Example 3: Cast a data type and filter server-side errors.
```
* | where cast(status as BIGINT) >= 500
```

SQL functions supported by SPL

Category	Description	Common functions
Aggregation functions	Calculates a single statistical value from a set of data.	avg function count function max function min function sum function
String functions	Finds, replaces, extracts, concatenates, and formats text data.	concat function length function position function replace function split function
Date and time functions	Converts, groups, and aggregates dates and times in logs.	current_date function date function day function date_trunc function time_series function
JSON functions	Extracts data from, converts, or calculates statistics on JSON objects.	json_array_contains function json_array_get function json_array_length function json_extract function json_extract_scalar function
Regular expression functions	Performs pattern matching and text processing.	regexp_extract_all function regexp_extract function regexp_extract_bool function regexp_replace function regexp_split function
Year-over-year and period-over-period functions	Calculates the relative change in time-series data.	compare function ts_compare function
Array functions and operators	Creates, manipulates, traverses, and transforms arrays.	Subscript operator array_distinct function array_intersect function array_join function reverse function
Map functions and operators	Works with key-value pairs.	cardinality function element_at function histogram function histogram_u function map function
Mathematical functions	Performs numerical calculations, rounding, random number generation, and trigonometric calculations.	abs function ceil function log function mod function random function
Statistical functions	Performs data distribution analysis and numerical calculations.	corr function covar_pop function regr_intercept function beta_cdf function inverse_chi_squared_cdf function
Type conversion functions	Converts between data types.	cast function try_cast function typeof function
Window functions	Aggregates or sorts data over a window.	Aggregation functions dense_rank function lag function
IP functions	Parses and calculates IP addresses.	ip_to_city function ip_prefix function ipv6_to_city function
URL functions	Parses URL structures.	url_encode function url_decode function url_extract_fragment function url_extract_path function url_extract_query function
Approximation functions	Performs approximate aggregations on large datasets, trading accuracy for performance.	approx_distinct function approx_percentile function numeric_histogram function numeric_histogram_u function
Binary functions	Operates on binary data.	from_base64 function from_big_endian_64 function from_hex function to_hex function sha256 function
Bitwise functions	Directly manipulates binary bits.	bit_count function bitwise_and function bitwise_not function bitwise_or function bitwise_xor function
Spatial functions	Operates on spatial geometry data.	ST_AsText function ST_GeometryFromText function ST_Boundary function ST_Buffer function ST_Contains function
Geographic functions	Performs geographic location analysis and map calculations.	geohash function
Color functions	Represents and converts colors.	bar function color function render function rgb function
HyperLogLog functions	Performs statistical processing on large datasets, trading precision for memory efficiency.	approx_set function cardinality function empty_approx_set function merge function
Comparison operators	Compares values across comparable types: double, bigint, varchar, timestamp, and date.	Basic operators ALL operator ANY operator BETWEEN operator DISTINCT operator
Logical operators	Combines multiple boolean conditions to control logical flow.	AND operator OR operator NOT operator
Unit conversion functions	Converts units of data size or time intervals.	convert_data_size function format_duration function
Window funnel functions	Analyzes data such as user behavior, app traffic, and product goal conversion.	window_funnel function
Lambda expressions	Lets you define lambda expressions in SQL and SPL statements and pass them to other functions for greater flexibility.	filter function reduce function transform function
Conditional expressions	Returns a value based on a specified condition.	CASE WHEN expression IF expression COALESCE expression NULLIF expression TRY expression