Expressions within the select, where and orderBy clauses can be comprised in the following manner. Expressions can be nested to arbitary depth. Parentheses allow for precedence to be explicitly denoted.

The following functions are supported:

After query is parsed from the SQL string, it is stored into a cache. When the same query will be fired against the query engine, parsing will be omitted and the query structure will be retrieved from cache. By default, query cache size is 1000. This means, that when 1000 different queries will be fired against the query engine, the next one will override the oldest cache entry. When set to 0 or to negative value, query cache will not be used and each query will be parsed.

The longer is the query and the more conditions it contains, the bigger becomes the query depth. To limit query complexity, maximal query depth parameter can be used. By default, maximal query depth is 4096. This should suffice for most queries even complicated ones. If query depth exceeds this limit, following error will be returned:

    {
        "errorMessage": "Max query depth reached: 4096"
    }
            

Zone ID value should follow the rules described in javadoc. The default value for zone id is "UTC".

SQL keywords, operators and functions are case-insensitive, so are domain names (object types) specified in the FROM clause. Field names specified in the SELECT clause are case-sensitive. Following queries are similar:

    {
        "sql": "SELECT name FROM BROKER"
    }
            

    {
        "sql": "SELECT name FROM broker"
    }
            

    {
        "sql": "select name from broker"
    }
            

They will return the same output. When an entity field name is written in wrong case or misspelled, an error will be returned. For example, following query

    {
        "sql": "SELECT NAME FROM BROKER"
    }
            

has field NAME written in upper case, which will result in an error:

    {
        "errorMessage": "Domain 'BROKER' does not contain field 'NAME'"
    }
            

In this document many SQL queries are split into several lines for better readability, but JSON format does not support multiline string fields. Therefore, even the long SQL queries should be placed in sql field of the JSON body as a single line. Aside from SQL query several configuration parameters can be provided to influence output format:

When datetime format is specified as LONG, datetime fields will be returned as milliseconds from UNIX epoch. So, following query

    {
        "sql": "select id, name, createdTime from broker",
        "dateTimeFormat": "LONG"
    }
            

returns following result:

    {
        "results": [
            {
                "id": "ce8bbaf0-3efa-4176-889a-7987ac1988cc",
                "name": "broker",
                "createdTime": 1645195849272
            }
        ],
        "total": 1
    }
            

In opposite the query

    {
        "sql": "select id, name, createdTime from broker",
        "dateTimeFormat": "STRING"
    }
            

returns following result:

    {
        "results": [
            {
                "id": "ce8bbaf0-3efa-4176-889a-7987ac1988cc",
                "name": "broker",
                "createdTime": "2022-02-18 15:50:49.272"
            }
        ],
        "total": 1
    }
            

The default format of the string datetime representation is "yyyy-MM-DD HH:mm:ss.SSS". It can be changed using the parameter dateTimePattern. The query

    {
        "sql": "select id, name, createdTime from broker",
        "dateTimeFormat": "STRING",
        "dateTimePattern": "yyyy/MM/dd HH:mm:ss.SSS"
    }
            

returns following result

    {
        "results": [
            {
                "id": "ce8bbaf0-3efa-4176-889a-7987ac1988cc",
                "name": "broker",
                "createdTime": "2022/02/18 15:50:49.272"
            }
        ],
        "total": 1
    }
            

By default, decimal digits value is 6, meaning there will be 6 digits after decimal point. For example, following query

    {
        "sql": "select avg(queueDepthMessages) from queue"
    }
            

returns following result:

    {
        "results": [
            {
                "avg(queueDepthMessages)": 0.437227
            }
        ],
        "total": 1
    }
            

This behavior can be changed for each value separately using ROUND or TRUNC functions, but can also be changed for the whole query result by supplying decimalDigits parameter. Following query

    {
        "sql": "select avg(queueDepthMessages) from queue",
        "decimalDigits": 2
    }
            

returns following result:

    {
        "results": [
            {
                "avg(queueDepthMessages)": 0.43
            }
        ],
        "total": 1
    }
            

Rounding mode affects how results of the arithmetic operations will be rounded. The rules of applying different rounding modes can be found in appropriate javadoc. Default rounding mode is HALF_UP. Changing rounding mode will affect division operations, but will not affect results of ROUND() and TRUNC() functions (which always use rounding mode HALF_UP and HALF_DOWN appropriately). Following query

    {
        "sql": "select 2/3",
        "decimalDigits": 2,
        "roundingMode": "DOWN"
    }
            

uses rounding mode DOWN and returns following result:

    {
        "results": [
            {
                "2/3": 0.66
            }
        ],
        "total": 1
    }
            

When rounding mode will be changed to UP

    {
        "sql": "select 2/3",
        "decimalDigits": 2,
        "roundingMode": "UP"
    }
            

result will be changed as well:

    {
        "results": [
            {
                "2/3": 0.67
            }
        ],
        "total": 1
    }
            

Broker entities have fields belonging to different java types: primitives (boolean, int, long, double), strings, datetime Date, LocalDate, LocalDateTime, Instant. Object IDs are usually of UUID type. Many values are enums. When comparing field values, they follow some implicit casting rules: enums and UUIDs are cast to strings, datetime values are cast to Instant, numeric values are cast to BigDecimal. When casting string value to date, by default is used pattern "uuuu-MM-dd". That allows to run following queries:

    SELECT *
    FROM certificate
    WHERE DATE(validUntil) = '2020-12-31'
            

Here string value is implicitly cast to Instant and both value are compared as Instant instances. When casting string to datetime, by default is used pattern "uuuu-MM-dd HH:mm:ss" with optional 0-6 second fractions. That allows to run following queries:

    SELECT *
    FROM certificate
    WHERE DATE(validUntil) > '2020-12-31 23:59:59.999'
            

Here string value is implicitly cast to Instant as well and both value are compared as Instant instances. It is important to compare values of the same type, otherwise an error may be returned or query may be evaluated erroneously. For example, following query

    SELECT *
    FROM queue
    WHERE durable = 'true'
            

will return an empty result, because field durable is of boolean type and comparing boolean value with a string 'true' will always return false. The correct query should be

    SELECT *
    FROM queue
    WHERE durable = true
            

To remove duplicates from the results keyword "DISTINCT" can be used. For example, query

    SELECT overflowPolicy
    FROM queue
            

will return results for all queues, but query

    SELECT DISTINCT overflowPolicy
    FROM queue
            

will return only several values.

Default sorting order is ascending, default sorting field is `name` for domains having this field. Results of the following query will be sorted ascending by name:

    SELECT *
    FROM queue
        

Few exceptions are following:

Results of the following query will be sorted ascending by alias:

    SELECT *
    FROM certificate
        

To apply another sorting rules clause `ORDER BY` should be used. It may contain one of the fields specified in the `SELECT` clause:

    SELECT
        id, name, state
    FROM queue
    ORDER BY name
        

Alternatively it may contain fields not specified in `SELECT` clause:

    SELECT
        id, name, state
    FROM queue
    ORDER BY overflowPolicy
        

Instead of using field names or aliases items in the `ORDER BY` clause can also be referenced by ordinal - the numeric value of their order of appearance in the `SELECT` clause. For example, following query

    SELECT
        name, overflowPolicy
    FROM queue
    ORDER BY 2 DESC, 1 ASC
        

will return results sorted in descending order by overflow policy and inside the groups with the same overflow policy name results will be sorted by queue name in ascending order.

Aggregation is achieved using functions AVG(), COUNT(), MAX(), MIN() and SUM(). It's important to remember, that aggregation functions don't consider NULL values. For example, following query

    SELECT COUNT(description)
    FROM queue
        

will return count of queues having non-null value of a field `description`. To consider NULL values, they should be handled using COALESCE() function or CASE operator:

    SELECT COUNT(COALESCE(description, ''))
    FROM queue
        

Alternatively

    SELECT COUNT(CASE WHEN description IS NULL THEN '' ELSE description END)
    FROM queue
        

Several aggregation functions can be used together in the same query:

    SELECT
        COUNT(*),
        AVG(queueDepthMessages),
        SUM(queueDepthMessages),
        SUM(queueDepthBytes),
        MIN(queueDepthMessages),
        MAX(queueDepthMessages),
        MIN(queueDepthBytes),
        MAX(queueDepthBytes)
    FROM queue
        

Grouping of the aggregated results can be achieved using the `GROUP BY` clause. For example, following query finds count of ACL rules for each user and output them in descending order:

    SELECT
        COUNT(*) AS cnt, identity
    FROM aclrule
    GROUP BY identity
    ORDER BY 1 DESC
        

The result of the query:

    {
        "results": [
            {
                "cnt": {
                    "amqp_user1": 6,
                    "amqp_user2": 4,
                    "amqp_user3": 4,
                    ... some results ommited ...
                    "amqp_user97": 2,
                    "amqp_user98": 1,
                    "amqp_user99": 1
                }
            }
        ],
        "total": 1
    }
        

To filter the grouped result `HAVING` clause can be used:

    SELECT
        overflowPolicy, COUNT(*)
    FROM queue
    GROUP BY overflowPolicy
    HAVING SUM(queueDepthMessages) > 1000
        

UNION, MINUS and INTERSECT set operations are supported. The UNION operator is used to combine the result-set of two or more SELECT statements. Every SELECT statement within UNION must have the same number of columns. The UNION operator selects distinct values by default. To keep duplicates, UNION ALL should be used. For example, following query return certificate aliases along with the user names:

    SELECT UPPER(alias)
    FROM certificate
    UNION
    SELECT UPPER(name)
    FROM user
        

The MINUS operator is used to remove the results of right SELECT statement from the results of left SELECT statement. Every SELECT statement within MINUS must have the same number of columns. The MINUS operator selects distinct values by default. To eliminate duplicates, MINUS ALL should be used. For example, following query finds queue names, not specified as binding destinations:

    SELECT name
    FROM queue
    MINUS
    SELECT destination
    FROM binding
        

The INTERSECT operation is used to retain the results of right SELECT statement present in the results of left SELECT statement. Every SELECT statement within INTERSECT must have the same number of columns. The INTERSECT operator selects distinct values by default. to eliminate duplicates, INTERSECT ALL should be used. For example, following query finds certificate aliases similar with the user names:

    SELECT UPPER(alias)
    FROM certificate
    INTERSECT
    SELECT UPPER(name)
    FROM user
        

When executing subquery parent query domain mat be passed into the subquery using alias. E.g. this query

    SELECT
        id,
        name,
        (SELECT name FROM connection WHERE SUBSTRING(name, 1, POSITION(']' IN name)) = '[' + SUBSTRING(c.name, 1, POSITION('|' IN c.name) - 1) + ']') as connection,
        (SELECT id FROM connection WHERE SUBSTRING(name, 1, POSITION(']' IN name)) = '[' + SUBSTRING(c.name, 1, POSITION('|' IN c.name) - 1) + ']') as connectionId,
        (SELECT name FROM session WHERE id = c.session.id) as session
    FROM consumer c
        

returns following result:

    {
        "results": [
            {
                "id": "7a4d7a86-652b-4112-b535-61272b936b57",
                "name": "1|1|qpid-jms:receiver:ID:6bd18833-3c96-4936-b9ee-9dec5f408b5c:1:1:1:broadcast.amqp_user1.public",
                "connection": "[1] 127.0.0.1:39134",
                "connectionId": "afbd0480-43b1-4b39-bc00-260c077095f3",
                "session": "1"
            }
        ],
        "total": 1
    }
        

Query

    SELECT
        name,
        destination,
        (SELECT id FROM queue WHERE name = b.destination) AS destinationId,
        exchange,
        (SELECT id FROM exchange WHERE name = b.exchange) AS exchangeId
    FROM binding b
    WHERE name = 'broadcast.amqp_user1.xxx.#'
        

returns following result:

    {
        "results": [
            {
                "name": "broadcast.amqp_user1.xxx.#",
                "destination": "broadcast.amqp_user1.xxx",
                "destinationId": "d5ce9e78-8558-40db-8690-15abf69ab255",
                "exchange": "broadcast",
                "exchangeId": "470273aa-7243-4cb7-80ec-13e698c36158"
            },
            {
                "name": "broadcast.amqp_user1.xxx.#",
                "destination": "broadcast.amqp_user2.xxx",
                "destinationId": "88357d15-a590-4ccf-aee8-2d5cda77752e",
                "exchange": "broadcast",
                "exchangeId": "470273aa-7243-4cb7-80ec-13e698c36158"
            },
            {
                "name": "broadcast.amqp_user1.xxx.#",
                "destination": "broadcast.amqp_user3.xxx",
                "destinationId": "c8200f89-2587-4b0c-a8f6-120cda975d03",
                "exchange": "broadcast",
                "exchangeId": "470273aa-7243-4cb7-80ec-13e698c36158"
            }
        ],
        "total": 3
    }
        

Query

    SELECT
        alias,
        (SELECT COUNT(id) FROM queue WHERE POSITION(UPPER(c.alias) IN name) > 0) AS queueCount
    FROM certificate c
        

returns following result:

    {
        "results": [
            {
                "alias": "xxx",
                "queueCount": 5
            },
            {
                "alias": "xxy",
                "queueCount": 5
            },
            {
                "alias": "xxz",
                "queueCount": 7
            }
        ],
        "total": 3
    }
        

Try to select entity fields by names instead of using an asterix. For example, this query

    SELECT
        id, name, state, overflowPolicy, expiryPolicy
    FROM queue
        

will be executed faster than this one:

    SELECT *
    FROM queue
        

Try to use `LIMIT` and `OFFSET` clauses where applicable to reduce the response JSON size:

    SELECT
        id, name, state, overflowPolicy, expiryPolicy
    FROM queue
    LIMIT 10 OFFSET 0