Supported OpenSearch SQL commands and functions

SELECT clause

Displays projected values.

SELECT 
    method,
    status 
FROM 
    <tableName/logGroup>

Filters log events based on the provided field criteria.

SELECT 
    * 
FROM 
    <tableName/logGroup>
WHERE 
    status = 100

Groups log events based on category and finds the average based on stats.

SELECT 
    method,
    status,
    COUNT(*) AS request_count,
    SUM(bytes) AS total_bytes 
FROM 
    <tableName/logGroup> 
GROUP BY 
    method, 
    status

Filters the results based on grouping conditions.

SELECT 
    method,
    status,
    COUNT(*) AS request_count,
    SUM(bytes) AS total_bytes 
FROM 
    <tableName/logGroup> 
GROUP BY 
    method,
    status
HAVING 
    COUNT(*) > 5

Orders the results based on fields in the order clause. You can sort in either descending or ascending order.

SELECT 
    * 
FROM 
    <tableName/logGroup> 
ORDER BY 
    status DESC

JOIN clause

( INNER | CROSS | LEFT OUTER )

Joins the results for two tables based on common fields.

Supported (must use Inner and Left Outer keywords for join; Only only one JOIN operation is supported in a SELECT statement)

SELECT 
    A.Body,
    B.Timestamp
FROM 
    <tableNameA/logGroupA> AS A 
INNER JOIN 
    <tableNameB/logGroupB> AS B 
    ON A.`requestId` = B.`requestId`

Restricts the results to first N rows.

SELECT 
    * 
FROM 
    <tableName/logGroup> 
LIMIT 
    10

SELECT
    method,
    status,
    CASE
        WHEN status BETWEEN 100 AND 199 THEN 'Informational'
        WHEN status BETWEEN 200 AND 299 THEN 'Success'
        WHEN status BETWEEN 300 AND 399 THEN 'Redirection'
        WHEN status BETWEEN 400 AND 499 THEN 'Client Error'
        WHEN status BETWEEN 500 AND 599 THEN 'Server Error'
        ELSE 'Unknown Status'
    END AS status_category,
    CASE method
        WHEN 'GET' THEN 'Read Operation'
        WHEN 'POST' THEN 'Create Operation'
        WHEN 'PUT' THEN 'Update Operation'
        WHEN 'PATCH' THEN 'Partial Update Operation'
        WHEN 'DELETE' THEN 'Delete Operation'
        ELSE 'Other Operation'
    END AS operation_type,
    bytes,
    datetime
FROM <tableName/logGroup>                         

WITH RequestStats AS (
    SELECT 
        method,
        status,
        bytes,
        COUNT(*) AS request_count
    FROM 
        tableName
    GROUP BY 
        method,
        status,
        bytes
)
SELECT 
    method,
    status,
    bytes,
    request_count 
FROM 
    RequestStats 
WHERE 
    bytes > 1000

EXPLAIN
SELECT 
    k,
    SUM(v)
FROM 
    VALUES 
        (1, 2),
        (1, 3) AS t(k, v)
GROUP BY 
    k

 SELECT 
    * 
FROM 
    tableName
LATERAL (
    SELECT 
        * 
    FROM 
        t2 
    WHERE 
        t1.c1 = t2.c1
)

SELECT 
    * 
FROM 
    tableName
LATERAL VIEW 
    EXPLODE(ARRAY(30, 60)) tableName AS c_age
LATERAL VIEW 
    EXPLODE(ARRAY(40, 80)) AS d_age

 SELECT 
    method,
    status,
    request,
    host 
FROM 
    <tableName/logGroup> 
WHERE 
    method LIKE 'D%'

 SELECT 
    method,
    status,
    bytes,
    datetime 
FROM 
    <tableName/logGroup> 
ORDER BY 
    datetime
OFFSET 
    10 

SELECT 
    * 
FROM 
    (
        SELECT 
            method,
            status,
            bytes
        FROM 
            <tableName/logGroup>
    ) AS SourceTable 
PIVOT 
(
    SUM(bytes) 
    FOR method IN ('GET', 'POST', 'PATCH', 'PUT', 'DELETE')
) AS PivotTable

SELECT 
    method,
    status,
    bytes
FROM 
    <tableName/logGroup>
WHERE 
    status = '416'

UNION

SELECT 
    method,
    status,
    bytes
FROM 
    <tableName/logGroup>
WHERE 
    bytes > 20000

SELECT 
    method,
    status,
    bytes
FROM 
    <tableName/logGroup>
SORT BY 
    bytes DESC

 SELECT 
    status,
    REPLACE(method, '_bytes', '') AS request_method,
    bytes,
    datetime 
FROM 
    PivotedData 
UNPIVOT 
(
    bytes 
    FOR method IN 
    (
        GET_bytes,
        POST_bytes,
        PATCH_bytes,
        PUT_bytes,
        DELETE_bytes
    )
) AS UnpivotedData

Built-in functions that can manipulate and transform string and text data within SQL queries. For example, converting case, combining strings, extracting parts, and cleaning text.

SELECT 
    UPPER(method) AS upper_method,
    LOWER(host) AS lower_host 
FROM 
    <tableName/logGroup>

Built-in functions for handling and transforming date and timestamp data in queries. For example, date_add, date_format, datediff, and current_date.

SELECT 
    TO_TIMESTAMP(datetime) AS timestamp,
    TIMESTAMP_SECONDS(UNIX_TIMESTAMP(datetime)) AS from_seconds,
    UNIX_TIMESTAMP(datetime) AS to_unix,
    FROM_UTC_TIMESTAMP(datetime, 'PST') AS to_pst,
    TO_UTC_TIMESTAMP(datetime, 'EST') AS from_est 
FROM 
    <tableName/logGroup>

Built-in functions that perform calculations on multiple rows to produce a single summarized value. For example, sum, count, avg, max, and min.

Supported

SELECT 
    COUNT(*) AS total_records,
    COUNT(DISTINCT method) AS unique_methods,
    SUM(bytes) AS total_bytes,
    AVG(bytes) AS avg_bytes,
    MIN(bytes) AS min_bytes,
    MAX(bytes) AS max_bytes 
FROM 
    <tableName/logGroup>

Built-in functions that perform actions based on specified conditions, or that evaluate expressions conditionally. For example, CASE and IF.

SELECT 
    CASE 
        WHEN method = 'GET' AND bytes < 1000 THEN 'Small Read'
        WHEN method = 'POST' AND bytes > 10000 THEN 'Large Write'
        WHEN status >= 400 OR bytes = 0 THEN 'Problem'
        ELSE 'Normal'
    END AS request_type 
FROM 
    <tableName/logGroup>

Built-in functions for parsing, extracting, modifying, and querying JSON-formatted data within SQL queries (e.g., from_json, to_json, get_json_object, json_tuple) allowing manipulation of JSON structures in datasets.

SELECT 
    FROM_JSON(
        @message, 
        'STRUCT<
            host: STRING,
            user-identifier: STRING,
            datetime: STRING,
            method: STRING,
            status: INT,
            bytes: INT
        >'
    ) AS parsed_json 
FROM 
    <tableName/logGroup> 

Built-in functions for working with array-type columns in SQL queries, allowing operations like accessing, modifying, and analyzing array data (e.g., size, explode, array_contains).

SELECT 
    scores,
    size(scores) AS length,
    array_contains(scores, 90) AS has_90 
FROM 
    <tableName/logGroup>

 SELECT 
    field1,
    field2,
    RANK() OVER (ORDER BY field2 DESC) AS field2Rank 
FROM 
    <tableName/logGroup>

Built-in functions for converting data from one type to another within SQL queries, enabling data type transformations and format conversions (e.g., CAST, TO_DATE, TO_TIMESTAMP, BINARY)

SELECT 
    CAST('123' AS INT) AS converted_number,
    CAST(123 AS STRING) AS converted_string 
FROM 
    <tableName/logGroup>

Built-in functions that evaluate conditions and return boolean values (true/false) based on specified criteria or patterns (e.g., IN, LIKE, BETWEEN, IS NULL, EXISTS)

SELECT 
    * 
FROM 
    <tableName/logGroup> 
WHERE 
    id BETWEEN 50000 AND 75000

SELECT 
    MAP_FILTER(
        MAP(
            'method', method,
            'status', CAST(status AS STRING),
            'bytes', CAST(bytes AS STRING)
        ),
        (k, v) -> k IN ('method', 'status') AND v != 'null'
    ) AS filtered_map 
FROM 
    <tableName/logGroup> 
WHERE 
    status = 100

SELECT 
    bytes,
    bytes + 1000 AS added,
    bytes - 1000 AS subtracted,
    bytes * 2 AS doubled,
    bytes / 1024 AS kilobytes,
    bytes % 1000 AS remainder 
FROM 
    <tableName/logGroup>

Enables users to specify multiple log groups in a SQL SELECT statement

SELECT 
    lg1.Column1,
    lg1.Column2 
FROM 
    `logGroups(logGroupIdentifier: ['LogGroup1', 'LogGroup2'])` AS lg1 
WHERE 
    lg1.Column3 = "Success"

SELECT 
    explode(array(10, 20))