<div class="docbook"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="3" align="center">6.3. REST API</th></tr><tr><td width="20%" align="left"><a accesskey="p" href="Java-Broker-Management-Channel-Web-Console.html">Prev</a> </td><th width="60%" align="center">Chapter 6. Management Channels</th><td width="20%" align="right"> <a accesskey="n" href="Java-Broker-Management-Metrics.html">Next</a></td></tr></table><hr /></div><div class="section"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a id="Java-Broker-Management-Channel-REST-API"></a>6.3. REST API</h2></div></div></div><div class="section"><div class="titlepage"><div><div><h3 class="title"><a id="Java-Broker-Management-Channel-REST-API-Introduction"></a>6.3.1. Introduction</h3></div></div></div><p>This section describes the REST API provided by the Apache Qpid Broker-J. The REST API is intended
      for use by developers who wish to automate the management or monitoring of the Broker. It
      is also very useful for adhoc monitoring on the command line using tools such as
        <code class="literal">curl</code>.</p><p>The REST API provides access to all of the Broker's entities using hierarchical paths
      expressed by the URI. Responses are returned in JSON format.</p><p>The <code class="literal">GET</code> method request retrieves information about an object, the
        <code class="literal">DELETE</code> method requests the removal of one, and the <code class="literal">PUT</code>
      or <code class="literal">POST</code> methods perform updates or create new objects. The
        <code class="literal">POST</code> method is also used to invoke operations.</p><p>The REST API is versioned with the version number embedded within the URI. The general form
      of the URI is <code class="literal">/api/&lt;version&gt;</code> where &lt;version&gt; is a dot separated
      major and minor model version prefixed with "v", for example, "v6.1" (without the quotation marks).
      For convenience the alias <code class="literal">latest</code> (<code class="literal">/api/latest</code>) signifies the
      latest supported version.</p><p>There are also some ancillary services under URI <code class="literal">/service</code> used for
      authentication and logout.</p></div><div class="section"><div class="titlepage"><div><div><h3 class="title"><a id="Java-Broker-Management-Channel-REST-API-APIDocs"></a>6.3.2. REST API documentation</h3></div></div></div><p>REST API documentation is available on-line from any Broker at location
        <code class="literal">/apidocs</code>. It is also linked from the menu of the Web Management Console.
    </p></div><div class="section"><div class="titlepage"><div><div><h3 class="title"><a id="Java-Broker-Management-Channel-REST-API-Authentication"></a>6.3.3. Authentication</h3></div></div></div><p>Before you can use the REST API, you must authenticate. Authentication decisions are made
      by the <a class="link" href="Java-Broker-Concepts-Authentication-Providers.html" title="4.9. Authentication Providers">authentication
        provider</a> associated with HTTP <a class="link" href="Java-Broker-Concepts-Ports.html" title="4.8. Ports">port</a>
      on which you connect.</p><p>You may authenticate using <a class="link" href="https://www.ietf.org/rfc/rfc4422.txt" target="_top">SASL</a>
        (<code class="literal">/service/sasl</code>) or <a class="link" href="https://tools.ietf.org/html/rfc2617" target="_top">HTTP
        Basic Authentication</a>. The latter is convienent when using tools such as
        <code class="literal">curl</code> on the command line. This is illustrated in the examples
      below.</p><p>For SASL authentication use a <code class="literal">GET</code> request to
        <code class="literal">/service/sasl</code> to get a list of supported SASL mechanisms, and use
        <code class="literal">PUT</code> to the same URL to perform the SASL negotiation.</p><p>It is possible to end an authenticated session using
      <code class="literal">/service/logout</code>.</p></div><div class="section"><div class="titlepage"><div><div><h3 class="title"><a id="Java-Broker-Management-Channel-REST-API-Create"></a>6.3.4. Configured Object creation</h3></div></div></div><p>Methods PUT or POST can be used to create ConfiguredObject.</p><p> ConfiguredObject can be created by submitting PUT request against ConfiguredObject full
      URI (the one ending with configured object name) or by submitting PUT/POST request against
      parent URI. The request encoding should be json (application/json) and request body should
      contain attributes values in json format. On successful completion of operation a response
      should be returned having response status code set to 201 and response header "Location" set
      to ConfiguredObject full URI. If object with a such name/id already exist and POST/PUT
      requests is made against parent URI, an error response should be returned having response code
      409 (conflict) and body containing the json with the reason of operation failure. If object
      with a such name/id already exist and and PUT request is made against ConfiguredObject full
      URI, then ConfiguredObject update should be performed and http status code 200 should be
      returned. If ConfiguredObject cannot be created because of validation failure(s) the response
      should have http status code set 422 (Unprocessible Entity) and body should contain json with
      the reason of operation failure. On any other failure to create ConfiguredObject the response
      should have status code set to 400 (Bad Request) and payload should contain a json with error
      explaining the exact reason of failure. </p><div class="example"><a id="d0e2147"></a><p class="title"><strong>Example 6.1. Examples of REST calls for Queue creation</strong></p><div class="example-contents"><p> To create Queue with name "my-queue" on a virtual host with name "vh" (which is
        contained within virtual host node with name "vhn") either of the following requests should
        be made: </p><pre class="screen">PUT /api/latest/queue/vhn/vh HTTP/1.1</pre><pre class="screen">POST /api/latest/queue/vhn/vh HTTP/1.1</pre><pre class="screen">PUT /api/latest/queue/vhn/vh/my-queue HTTP/1.1</pre><p> Response code 201 should be returned on successful queue creation. Response header
        "Location" should be set to "/api/latest/queue/test/my-queue". If queue with name "my-queue"
        already exists and either of 2 first requests above were used, an error response with
        response code 409 (conflict) and body containing json with message that queue exists should
        be returned. If queue with name "my-queue" exists and last request is used, then Queue
        update should occur. </p></div></div><br class="example-break" /></div><div class="section"><div class="titlepage"><div><div><h3 class="title"><a id="Java-Broker-Management-Channel-REST-API-Update"></a>6.3.5. Configured Object update</h3></div></div></div><p>Methods PUT or POST can be used to update ConfiguredObject.</p><p> ConfiguredObject can be updated by submitting PUT or POST request against
      ConfiguredObject full URI (the one ending with configured object name). The request encoding
      should be json (application/json) and request body should contain a ConfiguredObject json
      (with all or only modified attributes). On successful completion of operation a response code
      200 should be returned. If ConfiguredObject does not exists and PUT method is used, such
      object should be created (201 response will be returned in this case). If ConfiguredObject
      does not exists and POST method is used, an error response should be returned having response
      status code 404 and payload with json explaining the problem. If any error occur on update, a
      response with response code 400 or 422 or 404 should be sent back to the client containing
      json body with error details. </p><div class="example"><a id="d0e2167"></a><p class="title"><strong>Example 6.2. Examples of REST calls for Queue update</strong></p><div class="example-contents"><p>To update Queue with name "my-queue" on a virtual host with name "vh" (contained in
        virtual host node with name "vhn") either of the following requests can be made:</p><pre class="screen">POST /api/latest/queue/vhn/vh/my-queue HTTP/1.1</pre><pre class="screen">POST /api/latest/queue/vhn/vh/my-queue HTTP/1.1</pre></div></div><br class="example-break" /></div><div class="section"><div class="titlepage"><div><div><h3 class="title"><a id="Java-Broker-Management-Channel-REST-API-Delete"></a>6.3.6. Configured Object deletion</h3></div></div></div><p>Method DELETE can be used to delete ConfiguredObject. Alternatively, ConfiguredObject can
      be deleted with update request having desiredState attribute set to value "DELETED". POST or
      PUT methods can be used in this case.</p><p>On successful completion of operation a response code 200 should be returned.</p><p>With DELETE method object ConfiguredObject in following ways:</p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>by submitting DELETE request using ConfiguredObject full URI (the one ending with
          configured object name)</p></li><li class="listitem"><p>by submitting DELETE request using parent URI and providing parameters having the same
          names as children attributes, for example, id, name, etc. Multiple children can be deleted
          in a such way. Many "id" parameters can be specified in such requests. Only children with
          matching attribute values will be deleted.</p></li></ul></div><div class="example"><a id="d0e2192"></a><p class="title"><strong>Example 6.3. Examples of REST calls for Queue deletion</strong></p><div class="example-contents"><p>To delete Queue with name "my-queue" on a virtual host with name "vh" (contained in
        virtual host node with name "vhn") either of the following requests can be made:</p><pre class="screen">DELETE /api/latest/queue/vhn/vh/my-queue HTTP/1.1</pre><pre class="screen">DELETE /api/latest/queue/vhn/vh?name=my-queue HTTP/1.1</pre><pre class="screen">DELETE /api/latest/queue/vhn/vh?id=real-queue-id HTTP/1.1</pre></div></div><br class="example-break" /></div><div class="section"><div class="titlepage"><div><div><h3 class="title"><a id="Java-Broker-Management-Channel-REST-API-Get"></a>6.3.7. Retrieving Configured Object details</h3></div></div></div><p>Method GET is used to retrieve an object's attributes values and statistics.</p><p>To retrieve a single object, use its full URI. For instance, to retrieve a single queue:</p><pre class="screen">GET /api/latest/queue/vhn/vh/my-queue</pre><p>To retrieve all objects beneath a parent, pass the parent's URI. For instance, to retrieve
      all queues beneath the virtualhost called <code class="literal">vh</code>. A collection will be returned.
    </p><pre class="screen">GET /api/latest/queue/vhn/vh</pre><p>Request parameters (with the same name as an attribute) are used to filter the returned collection.
      For instance, to filter those queues of type <code class="literal">standard</code>:</p><pre class="screen">GET /api/latest/queue/vhn/vh?type=standard</pre><p>Additional parameters supported in GET requests:</p><div class="variablelist"><dl class="variablelist"><dt><span class="term">depth</span></dt><dd><p>To restrict the depth of hierarchy of configured objects to return in
            response</p></dd><dt><span class="term">actuals</span></dt><dd><p>If set to "true" attribute actual values are returned instead of effective</p></dd><dt><span class="term">excludeInheritedContext</span></dt><dd><p>If set to "false" the inherited context is included from the object's ancestors. Default is true.</p></dd><dt><span class="term">oversize</span></dt><dd><p>Sets the maximum length for values of over-sized attributes to trim</p></dd><dt><span class="term">extractInitialConfig</span></dt><dd><p>If set to "true", the returned json can be used as initial configuration.</p></dd></dl></div></div><div class="section"><div class="titlepage"><div><div><h3 class="title"><a id="Java-Broker-Management-Channel-REST-API-Operations"></a>6.3.8. Configured Object operations</h3></div></div></div><p>Method POST is used to invoke Configured Objects operations. Some operations support
      parameters. Pass parameters using a JSON request body containing a map with a map entry for
      each parameter. </p><div class="example"><a id="d0e2264"></a><p class="title"><strong>Example 6.4. Example REST call invoking the operation clear queue</strong></p><div class="example-contents"><p>To clear the queue with name "my-queue" on a virtual host with name "vh".</p><pre class="screen">POST api/latest/queue/vhn/vh/my-queue/clearQueue HTTP/1.1</pre></div></div><br class="example-break" /></div><div class="section"><div class="titlepage"><div><div><h3 class="title"><a id="Java-Broker-Management-Channel-REST-API-Status-Codes"></a>6.3.9. HTTP status codes returned by REST interfaces</h3></div></div></div><div class="table"><a id="d0e2274"></a><p class="title"><strong>Table 6.1. HTTP status codes returned by REST interfaces</strong></p><div class="table-contents"><table summary="HTTP status codes returned by REST interfaces" border="1"><colgroup><col class="statuscode" /><col class="description" /></colgroup><thead><tr><th>Status code</th><th>Description</th></tr></thead><tbody><tr><td>
              <p>200</p>
            </td><td>
              <p>REST request is successfully completed. This status code can be returned by
                update, delete and get requests.</p>
            </td></tr><tr><td>
              <p>201</p>
            </td><td>
              <p>New configured object is created. It is returned by REST PUT and POST requests
                for creation of configured objects.</p>
            </td></tr><tr><td>
              <p>400</p>
            </td><td>
              <p>REST request cannot be performed due to errors in request. It can be returned
                from create, update and delete requests. The details of a problem are provided in
                the response payload in json format.</p>
            </td></tr><tr><td>
              <p>401</p>
            </td><td>
              <p>The request requires user authentication</p>
            </td></tr><tr><td>
              <p>403</p>
            </td><td>
              <p>Execution of request is not allowed due to failure to authorize user
                operation.</p>
            </td></tr><tr><td>
              <p>404</p>
            </td><td>
              <p> The requested configured object cannot be found. This status code can be
                returned from POST update requests if configured object does not exist. The reason
                for the status code is provided in the response payload in json format. </p>
            </td></tr><tr><td>
              <p>409</p>
            </td><td>
              <p>The request can not be performed because its execution can create conflicts in
                the broker. This status code can be returned from POST/PUT create requests against
                parent URI if configured object with requested name or id already exists. The status
                code 409 can also be returned if removal or update of configured object can violate
                system integrity. The reason for the status code is provided in the response payload
                in json format. </p>
            </td></tr><tr><td>
              <p>422</p>
            </td><td>
              <p>The request can not be performed because provided information either incomplete
                or invalid. This status code can be returned from create or update requests. The
                reason for the status code is provided in the response payload in json
                format.</p>
            </td></tr></tbody></table></div></div><br class="table-break" /></div><div class="section"><div class="titlepage"><div><div><h3 class="title"><a id="Java-Broker-Management-Channel-REST-API-Examples"></a>6.3.10. Examples of REST requests with curl</h3></div></div></div><div class="example"><a id="d0e2378"></a><p class="title"><strong>Example 6.5. Examples of queue creation using curl (authenticating as user admin):</strong></p><div class="example-contents"><pre class="programlisting">
#create a durable queue
curl --user admin -X PUT  -d '{"durable":true}' http://localhost:8080/api/latest/queue/&lt;vhostnode name&gt;/&lt;vhostname&gt;/&lt;queuename&gt;
#create a durable priority queue
curl --user admin -X PUT  -d '{"durable":true,"type":"priority"}' http://localhost:8080/api/latest/queue/&lt;vhostnode name&gt;/&lt;vhostname&gt;/&lt;queuename&gt;
            </pre></div></div><br class="example-break" /><p> NOTE: These curl examples utilise an unsecured HTTP transport. To use the examples it is
      first necessary enable Basic authentication for HTTP within the HTTP Management Configuration
      (it is off by default). For details see <a class="xref" href="Java-Broker-Management-Managing-Plugin-HTTP.html" title="7.17. HTTP Plugin">Section 7.17, “HTTP Plugin”</a>
    </p></div><div class="section"><div class="titlepage"><div><div><h3 class="title"><a id="Java-Broker-Management-Channel-REST-Query-API"></a>6.3.11. Query API</h3></div></div></div><div class="section"><div class="titlepage"><div><div><h4 class="title"><a id="Java-Broker-Management-Channel-REST-Query-API-Introduction"></a>6.3.11.1. Introduction</h4></div></div></div><p>The <span class="emphasis"><em>Qpid Broker-J</em></span> provides a powerful feature called
            the <span class="emphasis"><em>Query API</em></span>. This allows the retrieval of the existing configured objects attributes
            satisfying user-provided queries.</p><p>Developers and operators can use this feature to monitor the Broker.
            For example, using <span class="emphasis"><em>Query API</em></span> one can find all queues with queue depth
            exceeding some limit or existing connections made from a particular location(s).</p></div><div class="section"><div class="titlepage"><div><div><h4 class="title"><a id="Java-Broker-Management-Channel-REST-Query-API-Overview"></a>6.3.11.2. Query API Overview</h4></div></div></div><p>
            When using the <span class="emphasis"><em>Query API</em></span> one specifies the category of the object
            to query, a list of attributes to return in the result set, an optional where clause,
            expressed as a predicate, that determines the filtering criteria, ordering, and
            limit/offset. The features should be readily recognisable to anyone who has has familiarity
            with SQL.
        </p><p>Queries associate with either the <span class="emphasis"><em>broker</em></span> as a whole, or an
            individual  <span class="emphasis"><em>virtualhost</em></span>.  Queries associated with the Broker
            can query any object within the Broker. Queries associated with a virtualhost are limited
            to the objects of the virtualhost itself.   For instance a queue query associated
            with a virtualhost queries only the queues belonging to that virtualhost.  On the other
            hand, a queue query associated with the Broker sees all the queues belonging on the entire
            Broker.
        </p><p>
            </p><div class="table"><a id="d0e2425"></a><p class="title"><strong>Table 6.2. Query API URLs</strong></p><div class="table-contents"><table summary="Query API URLs" border="1"><colgroup><col class="queryurl" /><col class="description" /></colgroup><thead><tr><th>Query API URL</th><th>Description</th></tr></thead><tbody><tr><td>
                                <p>/api/latest/querybroker/&lt;configured object category name&gt;</p>
                                <p>/api/&lt;version&gt;/querybroker/&lt;configured object category name&gt;</p>
                            </td><td>
                                <p>Query API URL fragment to query the specified object type across the entire broker</p>
                            </td></tr><tr><td>
                                <p>/api/latest/queryvhost/&lt;virtual host node name&gt;/&lt;virtual host name&gt;/&lt;configured object category name&gt;</p>
                                <p>/api/&lt;version&gt;/queryvhost/&lt;virtual host node name&gt;/&lt;virtual host name&gt;/&lt;configured object category name&gt;</p>
                            </td><td>
                                <p>Query API URL fragment to query the specified object type for a specific virtualhost</p>
                            </td></tr></tbody></table></div></div><p><br class="table-break" />
        </p><p>
            The QueryAPI accepts <code class="literal">select</code>, <code class="literal">where</code>, <code class="literal">orderBy</code>,
            <code class="literal">limit</code> and <code class="literal">offset</code> request parameters.
            </p><div class="table"><a id="d0e2484"></a><p class="title"><strong>Table 6.3. Query API request parameters</strong></p><div class="table-contents"><table summary="Query API request parameters" border="1"><colgroup><col class="paramname" /><col class="description" /></colgroup><thead><tr><th>Parameter Name</th><th>Parameter Description</th></tr></thead><tbody><tr><td>
                                <p><code class="literal">select</code></p>
                            </td><td>
                                <p>The <code class="literal">select</code> defines the columns of the result set.  It is a
                                    comma-separated list of expressions.  At its most simple, an expression can be
                                    the name of the attribute (e.g. <code class="literal">queueDepthBytes</code>), but more complex
                                    <a class="link" href="Java-Broker-Management-Channel-REST-API.html#Java-Broker-Management-Channel-REST-Query-API-Expressions" title="Query API expressions">expressions</a> are also supported.</p>
                                <p>Columns within the result set are named.  For expressions that are simple attribute
                                    names, the column names will follow the attributes themselves.  By default, other
                                    expressions will have a no name.</p>
                                <p>Column names can be overridden with an <code class="literal">AS</code>
                                    clause e.g. <code class="literal">now() AS currentDate</code>
                                </p>
                            </td></tr><tr><td>
                                <p><code class="literal">where</code></p>
                            </td><td>
                                <p>The <code class="literal">where</code> provides a boolean expression defining the result set filtering.</p>
                                <p>The syntax of the <a class="link" href="Java-Broker-Management-Channel-REST-API.html#Java-Broker-Management-Channel-REST-Query-API-Expressions" title="Query API expressions">expression</a>
                                    is based on a subset of the SQL92 conditional expression syntax and is similar to selector expressions in JMS e.g.
                                    <code class="literal">queueDepthBytes &gt; 16384 AND name like '%flow_queue'</code>.
                                </p>
                            </td></tr><tr><td>
                                <p><code class="literal">orderBy</code></p>
                            </td><td>
                                <p>Ordering conditions; the syntax of the
                                    <a class="link" href="Java-Broker-Management-Channel-REST-API.html#Java-Broker-Management-Channel-REST-Query-API-Expressions" title="Query API expressions">
                                        expression
                                    </a>
                                    is based on a subset of
                                    the SQL92 ordering expression syntax. Similar to ordering expressions in SQL,
                                    one can specify in ordering expression attributes names, sub-expressions
                                    or indexes (starting from 1) of attributes or expressions specified in select.
                                </p>
                            </td></tr><tr><td>
                                <p><code class="literal">limit</code></p>
                            </td><td>
                                <p>The maximum number of results to provide starting from given offset.</p>
                            </td></tr><tr><td>
                                <p><code class="literal">offset</code></p>
                            </td><td>
                                <p>An offset in results (default is 0) to provide results from.</p>
                            </td></tr></tbody></table></div></div><p><br class="table-break" />
        </p><div class="example"><a id="d0e2594"></a><p class="title"><strong>Example 6.6. Example of a Query API request to retrieve queue names and depths.</strong></p><div class="example-contents"><pre class="screen">GET api/latest/querybroker/queue?select=name,queueDepthBytes,queueDepthMessages&amp;where=queueDepthBytes&gt;0&amp;orderBy=1 desc,2 desc&amp;offset=0&amp;limit=100 HTTP/1.1</pre></div></div><br class="example-break" /></div><div class="section"><div class="titlepage"><div><div><h4 class="title"><a id="Java-Broker-Management-Channel-REST-Query-API-Results"></a>6.3.11.3. Query API Results</h4></div></div></div><p>The <span class="emphasis"><em>Query API</em></span> returns a JSON response.  The response contains the following:
            </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="literal">headers</code></span></dt><dd><p>ordered list of result set column names derived from the <code class="literal">select</code>
                            clause. Note that anonymous expressions (that is, those expressed without an
                            <code class="literal">AS</code>) will have empty column name.</p></dd><dt><span class="term"><code class="literal">results</code></span></dt><dd><p>two dimensional array containing the result-set</p></dd><dt><span class="term"><code class="literal">total</code></span></dt><dd><p>The <span class="emphasis"><em>total</em></span> number of results matching the where criteria.</p></dd></dl></div><p>
        </p><div class="example"><a id="d0e2639"></a><p class="title"><strong>Example 6.7. Example of Query API call for queue names and depths.</strong></p><div class="example-contents"><pre class="screen">GET api/latest/querybroker/queue?select=name,queueDepthBytes,queueDepthMessages&amp;where=queueDepthBytes&gt;0&amp;orderBy=1 desc,2 desc&amp;offset=0&amp;limit=100 HTTP/1.1</pre><pre class="programlisting">
{
    "headers" : [ "name", "queueDepthBytes", "queueDepthMessages" ],
    "results" : [ [ "foo", 312, 26], [ "bar", 300, 24 ] ],
    "total" : 2
}
            </pre></div></div><br class="example-break" /><div class="section"><div class="titlepage"><div><div><h5 class="title"><a id="Java-Broker-Management-Channel-REST-Query-API-Expressions"></a>Query API expressions</h5></div></div></div><p>Expressions within the <code class="literal">select</code>, <code class="literal">where</code> and <code class="literal">orderBy</code>
                clauses can be comprised in the following manner. Expressions can be nested to arbitary depth. Parentheses
                allow for precedence to be explicitly denoted.
            </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>variable name which can be an attribute name e.g <code class="literal">queueDepthBytes</code> or
                a reference to a parent attribute <code class="literal">$parent.name</code></p></li><li class="listitem"><p>literal e.g. <code class="literal">3</code> or <code class="literal">'foo'</code></p></li><li class="listitem"><p>functions - see below e.g. <code class="literal">now()</code> or <code class="literal">to_string(createdDate, '%tm/%td/%ty', 'EST')</code></p></li><li class="listitem"><p>arithmetic operations e.g. <code class="literal">3 * 4</code> or <code class="literal">to_string(now()) + name</code></p></li></ul></div><p>The following functions are supported:
                </p><div class="table"><a id="d0e2695"></a><p class="title"><strong>Table 6.4. Query API functions</strong></p><div class="table-contents"><table summary="Query API functions" border="1"><colgroup><col class="functionname" /><col class="description" /></colgroup><thead><tr><th>Function Name</th><th>Function Description</th></tr></thead><tbody><tr><td>
                                <p><code class="literal">concat(obj[,obj..])</code></p>
                            </td><td>
                                <p>concatenates the given objects into a string</p>
                            </td></tr><tr><td>
                                <p><code class="literal">now()</code></p>
                            </td><td>
                                <p>returns current date and time</p>
                            </td></tr><tr><td>
                                <p><code class="literal">to_date(object)</code></p>
                            </td><td>
                                <p>converts the first parameter, which must be a string. into a date.  The
                                string must be in ISO-8601 format e.g. <code class="literal">1970-01-01T10:00:00Z</code>.</p>
                            </td></tr><tr><td>
                                <p><code class="literal">date_add(object, duration)</code></p>
                            </td><td>
                                <p>adds the given ISO-8601 duration <code class="literal">duration</code> e.g.
                                    <code class="literal">P1D</code> or <code class="literal">-PT10H</code> to the date provided by the
                                    first parameter.</p>
                            </td></tr><tr><td>
                                <p><code class="literal">to_string(object[, format[, timezone]])</code></p>
                            </td><td>
                                <p>Converts given object into a string.</p>
                                <p>If the format argument is present, it must be a Java
                                    <a class="link" href="http://docs.oracle.com/javase/7/docs/api/java/util/Formatter.html" target="_top">Formatter</a>
                                    compliant string e.g. <code class="literal">%f</code> or <code class="literal">%tY-%tm-%td</code>.
                                </p>
                                <p>The timezone argument is significant if the object is a Date. If the timezone
                                    argument is specified it must be a valid Java timezone name. The date is converted
                                    to the specified timezone before being formatted by the<code class="literal">format</code>.
                                    If the timezone is omitted <code class="literal">UTC</code> is assumed.
                                </p>
                            </td></tr></tbody></table></div></div><p><br class="table-break" />
            </p></div></div></div><div class="section"><div class="titlepage"><div><div><h3 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine"></a>6.3.12. Query Engine</h3></div></div></div><div class="section"><div class="titlepage"><div><div><h4 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-Introduction"></a>6.3.12.1. Introduction</h4></div></div></div><p>
            Broker query engine extends existing functionality of broker query API and allows executing complex SQL-like
            queries against the broker. It allows using predicates combining AND/OR/NOT logical operations, supports
            aggregation and grouping as well as numerous numeric, datetime and string functions.
            Currently, querying from multiple object types (domains) in a single query as well as all types of joins
            are not supported.
        </p></div><div class="section"><div class="titlepage"><div><div><h4 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-Broker-Configuration"></a>6.3.12.2. Broker Configuration</h4></div></div></div><p>
            Some properties influencing the query output can be specified directly in the request, but there are also global
            properties, affecting the way query engine works.
        </p><p>
            </p><div class="table"><a id="d0e2818"></a><p class="title"><strong>Table 6.5. Query Engine Configuration</strong></p><div class="table-contents"><table summary="Query Engine Configuration" border="1"><colgroup><col class="context-property-name" /><col class="description" /></colgroup><thead><tr><th>Context Property Name</th><th>Description</th></tr></thead><tbody><tr><td>
                                <p>qpid.port.http.query.engine.cacheSize</p>
                            </td><td>
                                <p>Query cache size</p>
                            </td></tr><tr><td>
                                <p>qpid.port.http.query.engine.maxQueryDepth</p>
                            </td><td>
                                <p>Maximal query depth</p>
                            </td></tr><tr><td>
                                <p>qpid.port.http.query.engine.zoneId</p>
                            </td><td>
                                <p>Timezone ID</p>
                            </td></tr></tbody></table></div></div><p><br class="table-break" />
        </p><div class="section"><div class="titlepage"><div><div><h5 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-Broker-Configuration-Query-Cache-Size"></a>Query cache size</h5></div></div></div><p>
                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.
            </p></div><div class="section"><div class="titlepage"><div><div><h5 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-Broker-Configuration-Max-Query-Depth"></a>Maximal query depth</h5></div></div></div><p>
                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:
            </p><pre class="programlisting">
    {
        "errorMessage": "Max query depth reached: 4096"
    }
            </pre></div><div class="section"><div class="titlepage"><div><div><h5 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-Broker-Configuration-Zone-ID"></a>Zone ID</h5></div></div></div><p>
                Zone ID value should follow the rules described in <a class="link" href="https://docs.oracle.com/javase/8/docs/api/java/time/ZoneId.html#of-java.lang.String-" target="_top">javadoc</a>.
                The default value for zone id is "UTC".
            </p></div></div><div class="section"><div class="titlepage"><div><div><h4 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-Request-Format"></a>6.3.12.3. Request Format</h4></div></div></div><p>
            An authorized request should be sent to the following endpoint:
            POST http://&lt;hostname&gt;:&lt;port&gt;/api/latest/querybroker/broker
            SQL query should be supplied in the "sql" field of the JSON body:
        </p><pre class="programlisting">
    {
        "sql": "select * from broker"
    }
        </pre><div class="section"><div class="titlepage"><div><div><h5 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-SQL-Query-Format"></a>SQL Query Format</h5></div></div></div><p>
                SQL keywords, operators and functions are case-insensitive, so are domain names (object types) specified in
                the <code class="literal">FROM</code> clause. Field names specified in the <code class="literal">SELECT</code> clause are case-sensitive.
                Following queries are similar:
            </p><pre class="programlisting">
    {
        "sql": "SELECT name FROM BROKER"
    }
            </pre><pre class="programlisting">
    {
        "sql": "SELECT name FROM broker"
    }
            </pre><pre class="programlisting">
    {
        "sql": "select name from broker"
    }
            </pre><p>
                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
            </p><pre class="programlisting">
    {
        "sql": "SELECT NAME FROM BROKER"
    }
            </pre><p>
                has field <code class="literal">NAME</code> written in upper case, which will result in an error:
            </p><pre class="programlisting">
    {
        "errorMessage": "Domain 'BROKER' does not contain field 'NAME'"
    }
            </pre><p>
                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 <code class="literal">sql</code> field of the JSON body as a
                single line.
                Aside from SQL query several configuration parameters can be provided to influence output format:
            </p><p>
                </p><div class="table"><a id="d0e2927"></a><p class="title"><strong>Table 6.6. Additional Request Parameters</strong></p><div class="table-contents"><table summary="Additional Request Parameters" border="1"><colgroup><col class="field-name" /><col class="description" /></colgroup><thead><tr><th>Field Name</th><th>Description</th></tr></thead><tbody><tr><td>
                                    <p>dateTimeFormat</p>
                                </td><td>
                                    <p>Format of the datetime fields, possible values: LONG, STRING</p>
                                </td></tr><tr><td>
                                    <p>dateTimePattern</p>
                                </td><td>
                                    <p>Pattern for datetime fields formatting, e.g. yyyy-MM-dd HH:mm:ss</p>
                                </td></tr><tr><td>
                                    <p>decimalDigits</p>
                                </td><td>
                                    <p>Amount of decimal digits</p>
                                </td></tr><tr><td>
                                    <p>roundingMode</p>
                                </td><td>
                                    <p>Rounding mode for arithmetic operations, possible values UP, DOWN, CEILING, FLOOR,
                                        HALF_UP, HALF_DOWN, HALF_EVEN, UNNECESSARY
                                    </p>
                                </td></tr></tbody></table></div></div><p><br class="table-break" />
            </p></div><div class="section"><div class="titlepage"><div><div><h5 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-Datetime-Format"></a>Datetime Format</h5></div></div></div><p>
                When datetime format is specified as <code class="literal">LONG</code>, datetime fields will be returned as
                milliseconds from UNIX epoch. So, following query
            </p><pre class="programlisting">
    {
        "sql": "select id, name, createdTime from broker",
        "dateTimeFormat": "LONG"
    }
            </pre><p>
                returns following result:
            </p><pre class="programlisting">
    {
        "results": [
            {
                "id": "ce8bbaf0-3efa-4176-889a-7987ac1988cc",
                "name": "broker",
                "createdTime": 1645195849272
            }
        ],
        "total": 1
    }
            </pre><p>
                In opposite the query
            </p><pre class="programlisting">
    {
        "sql": "select id, name, createdTime from broker",
        "dateTimeFormat": "STRING"
    }
            </pre><p>
                returns following result:
            </p><pre class="programlisting">
    {
        "results": [
            {
                "id": "ce8bbaf0-3efa-4176-889a-7987ac1988cc",
                "name": "broker",
                "createdTime": "2022-02-18 15:50:49.272"
            }
        ],
        "total": 1
    }
            </pre></div><div class="section"><div class="titlepage"><div><div><h5 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-Datetime-Pattern"></a>Datetime Pattern</h5></div></div></div><p>
                The default format of the string datetime representation is "yyyy-MM-DD HH:mm:ss.SSS".
                It can be changed using the parameter <code class="literal">dateTimePattern</code>.
                The query
            </p><pre class="programlisting">
    {
        "sql": "select id, name, createdTime from broker",
        "dateTimeFormat": "STRING",
        "dateTimePattern": "yyyy/MM/dd HH:mm:ss.SSS"
    }
            </pre><p>
                returns following result
            </p><pre class="programlisting">
    {
        "results": [
            {
                "id": "ce8bbaf0-3efa-4176-889a-7987ac1988cc",
                "name": "broker",
                "createdTime": "2022/02/18 15:50:49.272"
            }
        ],
        "total": 1
    }
            </pre></div><div class="section"><div class="titlepage"><div><div><h5 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-Decimal-Digits"></a>Decimal Digits</h5></div></div></div><p>
                By default, decimal digits value is 6, meaning there will be 6 digits after decimal point.
                For example, following query
            </p><pre class="programlisting">
    {
        "sql": "select avg(queueDepthMessages) from queue"
    }
            </pre><p>
                returns following result:
            </p><pre class="programlisting">
    {
        "results": [
            {
                "avg(queueDepthMessages)": 0.437227
            }
        ],
        "total": 1
    }
            </pre><p>
                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 <code class="literal">decimalDigits</code> parameter. Following query
            </p><pre class="programlisting">
    {
        "sql": "select avg(queueDepthMessages) from queue",
        "decimalDigits": 2
    }
            </pre><p>
                returns following result:
            </p><pre class="programlisting">
    {
        "results": [
            {
                "avg(queueDepthMessages)": 0.43
            }
        ],
        "total": 1
    }
            </pre></div><div class="section"><div class="titlepage"><div><div><h5 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-Rounding-Mode"></a>Rounding Mode</h5></div></div></div><p>
                Rounding mode affects how results of the arithmetic operations will be rounded. The rules of applying different rounding
                modes can be found in appropriate <a class="link" href="https://docs.oracle.com/javase/8/docs/api/java/math/RoundingMode.html" target="_top">javadoc</a>.
                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
            </p><pre class="programlisting">
    {
        "sql": "select 2/3",
        "decimalDigits": 2,
        "roundingMode": "DOWN"
    }
            </pre><p>
                uses rounding mode <code class="literal">DOWN</code> and returns following result:
            </p><pre class="programlisting">
    {
        "results": [
            {
                "2/3": 0.66
            }
        ],
        "total": 1
    }
            </pre><p>
                When rounding mode will be changed to <code class="literal">UP</code>
            </p><pre class="programlisting">
    {
        "sql": "select 2/3",
        "decimalDigits": 2,
        "roundingMode": "UP"
    }
            </pre><p>
                result will be changed as well:
            </p><pre class="programlisting">
    {
        "results": [
            {
                "2/3": 0.67
            }
        ],
        "total": 1
    }
            </pre></div></div><div class="section"><div class="titlepage"><div><div><h4 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-Object-Types"></a>6.3.12.4. Object Types (Domains)</h4></div></div></div><p>
            Object types or domains to query from are specified in the "FROM" clause. The broker object hierarchy can be retrieved
            using an endpoint http://&lt;hostname&gt;:&lt;port&gt;/service/metadata

            Alternatively following SQL query can be fired
        </p><pre class="programlisting">
    {
        "sql": "select * from domain"
    }
        </pre><p>
            returning similar result:
        </p><pre class="programlisting">
    {
        "results": [
            {
                "name": "AccessControlProvider"
            },
            {
                "name": "AclRule"
            },
            {
                "name": "AuthenticationProvider"
            },
            {
                "name": "Binding"
            },
            {
                "name": "BrokerConnectionLimitProvider"
            },
            {
                "name": "BrokerLogInclusionRule"
            },
            {
                "name": "BrokerLogger"
            },
            {
                "name": "Certificate"
            },
            {
                "name": "Connection"
            },
            {
                "name": "ConnectionLimitRule"
            },
            {
                "name": "Consumer"
            },
            {
                "name": "Domain"
            },
            {
                "name": "Exchange"
            },
            {
                "name": "Group"
            },
            {
                "name": "GroupMember"
            },
            {
                "name": "GroupProvider"
            },
            {
                "name": "KeyStore"
            },
            {
                "name": "Plugin"
            },
            {
                "name": "Port"
            },
            {
                "name": "Queue"
            },
            {
                "name": "RemoteReplicationNode"
            },
            {
                "name": "Session"
            },
            {
                "name": "TrustStore"
            },
            {
                "name": "User"
            },
            {
                "name": "VirtualHost"
            },
            {
                "name": "VirtualHostAccessControlProvider"
            },
            {
                "name": "VirtualHostAlias"
            },
            {
                "name": "VirtualHostConnectionLimitProvider"
            },
            {
                "name": "VirtualHostLogInclusionRule"
            },
            {
                "name": "VirtualHostLogger"
            },
            {
                "name": "VirtualHostNode"
            }
        ],
        "total": 31
    }
        </pre><p>
            In addition to the object types supported by broker query REST API, following object types (domains)
            can be used as well:
        </p><p>
            </p><div class="table"><a id="d0e3086"></a><p class="title"><strong>Table 6.7. Query Engine Domains</strong></p><div class="table-contents"><table summary="Query Engine Domains" border="1"><colgroup><col class="domain" /></colgroup><thead><tr><th>Domain</th></tr></thead><tbody><tr><td>
                                <p>AclRule</p>
                            </td></tr><tr><td>
                                <p>Binding</p>
                            </td></tr><tr><td>
                                <p>Certificate</p>
                            </td></tr><tr><td>
                                <p>ConnectionLimitRule</p>
                            </td></tr><tr><td>
                                <p>Domain</p>
                            </td></tr></tbody></table></div></div><p><br class="table-break" />
        </p><p>
            Those objects do not belong to the broker object hierarchy (as they don't descend from ConfiguredObject), they were
            added to make queries against listed domains more simple.
            For example, following query
        </p><pre class="programlisting">
    SELECT *
    FROM AclRule
    WHERE identity = 'amqp_user1'
        </pre><p>
            returns following result:
        </p><pre class="programlisting">
    {
        "results": [
            {
                "identity": "amqp_user1",
                "attributes": {},
                "action": {
                    "objectType": "VIRTUALHOST",
                    "properties": {
                        "name": null,
                        "empty": true
                    },
                    "operation": "ACCESS"
                },
                "objectType": null,
                "operation": null,
                "outcome": "ALLOW_LOG"
            },
            {
                "identity": "amqp_user1",
                "attributes": {
                    "NAME": "request.amqp_user1",
                    "ROUTING_KEY": "*"
                    },
                "action": {
                    "objectType": "EXCHANGE",
                    "properties": {
                        "name": "request.amqp_user1",
                        "empty": false
                        },
                    "operation": "PUBLISH"
                },
                "objectType": null,
                "operation": null,
                "outcome": "ALLOW"
            },
            {
                "identity": "amqp_user1",
                "attributes": {
                    "NAME": "broadcast.amqp_user1.*"
                    },
                "action": {
                    "objectType": "QUEUE",
                    "properties": {
                        "name": "broadcast.amqp_user1.*",
                        "empty": false
                        },
                    "operation": "CONSUME"
                },
                "objectType": null,
                "operation": null,
                "outcome": "ALLOW_LOG"
            },
            {
                "identity": "amqp_user1",
                "attributes": {
                    "NAME": "response.amqp_user1"
                    },
                "action": {
                    "objectType": "QUEUE",
                    "properties": {
                        "name": "response.amqp_user1",
                        "empty": false
                        },
                    "operation": "CONSUME"
                },
                "objectType": null,
                "operation": null,
                "outcome": "ALLOW_LOG"
            }
        ],
        "total": 4
    }
        </pre><p>
            Please note, that keyword <code class="literal">FROM</code> isn't mandatory, it is possible to execute queries without it, when the result shouldn't
            retrieve any data from broker. Few examples of such queries would be:
        </p><pre class="programlisting">
            SELECT CURRENT_TIMESTAMP()
        </pre><pre class="programlisting">
            SELECT DATE(CURRENT_TIMESTAMP())
        </pre><pre class="programlisting">
            SELECT (2 + 10) / 3
        </pre><pre class="programlisting">
            SELECT 2 * 5 &gt; 12
        </pre></div><div class="section"><div class="titlepage"><div><div><h4 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-Filtering-Results"></a>6.3.12.5. Filtering Results</h4></div></div></div><p>
            Filtering is achieved by using different operators groups in a <code class="literal">WHERE</code> clause. Operators
            can be divided into comparison operators, conditional operators and logical operators.
        </p><div class="section"><div class="titlepage"><div><div><h5 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-Broker-Data-Types"></a>Broker Data Types</h5></div></div></div><p>
                Broker entities have fields belonging to different java types: primitives (boolean, int, long, double), strings,
                datetime <a class="link" href="https://docs.oracle.com/javase/8/docs/api/java/util/Date.html" target="_top">Date</a>,
                <a class="link" href="https://docs.oracle.com/javase/8/docs/api/java/time/LocalDate.html" target="_top">LocalDate</a>,
                <a class="link" href="https://docs.oracle.com/javase/8/docs/api/java/time/LocalDateTime.html" target="_top">LocalDateTime</a>,
                <a class="link" href="https://docs.oracle.com/javase/8/docs/api/java/time/Instant.html" target="_top">Instant</a>.
                Object IDs are usually of <a class="link" href="https://docs.oracle.com/javase/8/docs/api/java/util/UUID.html" target="_top">UUID</a> 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 <a class="link" href="https://docs.oracle.com/javase/8/docs/api/java/time/Instant.html" target="_top">Instant</a>,
                numeric values are cast to <a class="link" href="https://docs.oracle.com/javase/8/docs/api/java/math/BigDecimal.html" target="_top">BigDecimal</a>.

                When casting string value to date, by default is used pattern "uuuu-MM-dd".

                That allows to run following queries:
            </p><pre class="programlisting">
    SELECT *
    FROM certificate
    WHERE DATE(validUntil) = '2020-12-31'
            </pre><p>
                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:
            </p><pre class="programlisting">
    SELECT *
    FROM certificate
    WHERE DATE(validUntil) &gt; '2020-12-31 23:59:59.999'
            </pre><p>
                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
            </p><pre class="programlisting">
    SELECT *
    FROM queue
    WHERE durable = 'true'
            </pre><p>
                will return an empty result, because field <code class="literal">durable</code> is of boolean type and comparing boolean value with a string 'true'
                will always return false. The correct query should be
            </p><pre class="programlisting">
    SELECT *
    FROM queue
    WHERE durable = true
            </pre></div><div class="section"><div class="titlepage"><div><div><h5 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-Broker-Keyword-DISTINCT"></a>Keyword DISTINCT</h5></div></div></div><p>
                To remove duplicates from the results keyword "DISTINCT" can be used. For example, query
            </p><pre class="programlisting">
    SELECT overflowPolicy
    FROM queue
            </pre><p>
                will return results for all queues, but query
            </p><pre class="programlisting">
    SELECT DISTINCT overflowPolicy
    FROM queue
            </pre><p>
                will return only several values.
            </p></div><div class="section"><div class="titlepage"><div><div><h5 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-Comparison-Operators"></a>Comparison Operators</h5></div></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-BETWEEN"></a>BETWEEN</h6></div></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-BETWEEN-Definition"></a>Definition and Usage</h6></div></div></div><p>
                        The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates. The BETWEEN
                        operator is inclusive: begin and end values are included.
                    </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-BETWEEN-Syntax"></a>Syntax</h6></div></div></div><pre class="programlisting">
    BETWEEN(expression1 AND expression2)
    BETWEEN(expression1, expression2)
    BETWEEN expression1 AND expression2
    BETWEEN expression1, expression2
                    </pre></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-BETWEEN-Parameter-Values"></a>Parameter Values</h6></div></div></div><p>
                        </p><div class="table"><a id="d0e3233"></a><p class="title"><strong>Table 6.8. BETWEEN Parameters</strong></p><div class="table-contents"><table summary="BETWEEN Parameters" border="1"><colgroup><col class="parameter" /><col class="description" /></colgroup><thead><tr><th>Parameter</th><th>Description</th></tr></thead><tbody><tr><td>
                                            <p>expression1</p>
                                        </td><td>
                                            <p>Lower threshold</p>
                                        </td></tr><tr><td>
                                            <p>expression2</p>
                                        </td><td>
                                            <p>Higher threshold</p>
                                        </td></tr></tbody></table></div></div><p><br class="table-break" />
                    </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-BETWEEN-Examples"></a>Examples</h6></div></div></div><p>
                        Find names of the queues having depth in messages between 1000 and 2000
                    </p><pre class="programlisting">
    SELECT
        name
    FROM queue
    WHERE queueDepthMessages BETWEEN (1000, 2000)
                    </pre><p>
                        Find certificates expiring between 2024-12-01 and 2024-12-31
                    </p><pre class="programlisting">
    SELECT *
    FROM certificate
    WHERE DATE(validUntil) BETWEEN ('2024-12-01' AND '2024-12-31')
                    </pre></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-EQUAL"></a>EQUAL</h6></div></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-EQUAL-Definition"></a>Definition and Usage</h6></div></div></div><p>
                            Equal operator is designated using "=" character. It allows comparison of boolean, datetime,
                            numeric and string values. Both compared values must have same type.
                        </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-EQUAL-Syntax"></a>Syntax</h6></div></div></div><pre class="programlisting">
        expression1 = expression2
                        </pre></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-EQUAL-Parameter-Values"></a>Parameter Values</h6></div></div></div><p>
                            </p><div class="table"><a id="d0e3298"></a><p class="title"><strong>Table 6.9. EQUAL Parameters</strong></p><div class="table-contents"><table summary="EQUAL Parameters" border="1"><colgroup><col class="parameter" /><col class="description" /></colgroup><thead><tr><th>Parameter</th><th>Description</th></tr></thead><tbody><tr><td>
                                                <p>expression1</p>
                                            </td><td>
                                                <p>Expression to compare to</p>
                                            </td></tr><tr><td>
                                                <p>expression2</p>
                                            </td><td>
                                                <p>Expression to compare with</p>
                                            </td></tr></tbody></table></div></div><p><br class="table-break" />
                        </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-EQUAL-Examples"></a>Examples</h6></div></div></div><p>
                            Find queue by name
                        </p><pre class="programlisting">
        SELECT *
        FROM queue
        WHERE name = 'broadcast.amqp_user1.Public'
                        </pre></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-GREATER-THAN"></a>GREATER THAN</h6></div></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-GREATER-THAN-Definition"></a>Definition and Usage</h6></div></div></div><p>
                        Greater than operator is designated using "&gt;" character. It allows comparison of datetime,
                        numeric and string values. Both compared values must have same type.
                    </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-GREATER-THAN-Syntax"></a>Syntax</h6></div></div></div><pre class="programlisting">
    expression1 &gt; expression2
                    </pre></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-GREATER-THAN-Parameter-Values"></a>Parameter Values</h6></div></div></div><p>
                        </p><div class="table"><a id="d0e3359"></a><p class="title"><strong>Table 6.10. GREATER THAN Parameters</strong></p><div class="table-contents"><table summary="GREATER THAN Parameters" border="1"><colgroup><col class="parameter" /><col class="description" /></colgroup><thead><tr><th>Parameter</th><th>Description</th></tr></thead><tbody><tr><td>
                                            <p>expression1</p>
                                        </td><td>
                                            <p>Expression to compare to</p>
                                        </td></tr><tr><td>
                                            <p>expression2</p>
                                        </td><td>
                                            <p>Expression to compare with</p>
                                        </td></tr></tbody></table></div></div><p><br class="table-break" />
                    </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-GREATER-THAN-Examples"></a>Examples</h6></div></div></div><p>
                        Find queues having message depth greater than 1000
                    </p><pre class="programlisting">
    SELECT *
    FROM queue
    WHERE queueDepthMessages &gt; 1000
                    </pre></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-GREATER-THAN-OR-EQUAL"></a>GREATER THAN OR EQUAL</h6></div></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-GREATER-THAN-OR-EQUAL-Definition"></a>Definition and Usage</h6></div></div></div><p>
                        Greater than or equal operator is designated using "&gt;=" characters. It allows comparison of
                        datetime, numeric and string values. Both compared values must have same type.
                    </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-GREATER-THAN-OR-EQUAL-Syntax"></a>Syntax</h6></div></div></div><pre class="programlisting">
    expression1 &gt;= expression2
                    </pre></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-GREATER-THAN-OR-EQUAL-Parameter-Values"></a>Parameter Values</h6></div></div></div><p>
                        </p><div class="table"><a id="d0e3420"></a><p class="title"><strong>Table 6.11. GREATER THAN OR EQUAL Parameters</strong></p><div class="table-contents"><table summary="GREATER THAN OR EQUAL Parameters" border="1"><colgroup><col class="parameter" /><col class="description" /></colgroup><thead><tr><th>Parameter</th><th>Description</th></tr></thead><tbody><tr><td>
                                            <p>expression1</p>
                                        </td><td>
                                            <p>Expression to compare to</p>
                                        </td></tr><tr><td>
                                            <p>expression2</p>
                                        </td><td>
                                            <p>Expression to compare with</p>
                                        </td></tr></tbody></table></div></div><p><br class="table-break" />
                    </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-GREATER-THAN-OR-EQUAL-Examples"></a>Examples</h6></div></div></div><p>
                        Find queues having message depth greater than or equal to 1000
                    </p><pre class="programlisting">
    SELECT *
    FROM queue
    WHERE queueDepthMessages &gt;= 1000
                    </pre></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-IN"></a>IN</h6></div></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-IN-Definition"></a>Definition and Usage</h6></div></div></div><p>
                        The IN operator allows specifying multiple values in a WHERE clause. The IN operator is a
                        shorthand for multiple OR conditions.
                        Alternatively IN operator can be used with a subquery. When a subquery is used, it should
                        return only one value, otherwise an error will be returned.
                    </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-IN-Syntax"></a>Syntax</h6></div></div></div><pre class="programlisting">
    expression IN (value_1, value_2, ..., value_n)
    expression IN (SELECT value FROM domain)
                    </pre></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-IN-Parameter-Values"></a>Parameter Values</h6></div></div></div><p>
                        </p><div class="table"><a id="d0e3481"></a><p class="title"><strong>Table 6.12. IN Parameters</strong></p><div class="table-contents"><table summary="IN Parameters" border="1"><colgroup><col class="parameter" /><col class="description" /></colgroup><thead><tr><th>Parameter</th><th>Description</th></tr></thead><tbody><tr><td>
                                            <p>expression</p>
                                        </td><td>
                                            <p>Expression to compare to</p>
                                        </td></tr><tr><td>
                                            <p>value_1 - value_n</p>
                                        </td><td>
                                            <p>Values to compare with</p>
                                        </td></tr></tbody></table></div></div><p><br class="table-break" />
                    </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-IN-Examples"></a>Examples</h6></div></div></div><p>
                        Find bindings having destination queue belonging to the list
                    </p><pre class="programlisting">
    SELECT *
    FROM binding
    WHERE destination IN ('broadcast.amqp_user1.Service1', 'broadcast.amqp_user1.Service2', 'broadcast.amqp_user1.Service3')
                    </pre><p>
                        Find bindings having destination queue with message depth between 1000 and 2000
                    </p><pre class="programlisting">
    SELECT *
    FROM binding
    WHERE destination IN (SELECT name FROM queue WHERE queueDepthMessages BETWEEN (1000, 2000))
                    </pre></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-IS-NULL"></a>IS NULL</h6></div></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-IS-NULL-Definition"></a>Definition and Usage</h6></div></div></div><p>
                        The IS NULL operator is used to compare ordinary values with NULL values.
                    </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-IS-NULL-Syntax"></a>Syntax</h6></div></div></div><pre class="programlisting">
    expression IS NULL
                    </pre></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-IS-NULL-Parameter-Values"></a>Parameter Values</h6></div></div></div><p>
                        </p><div class="table"><a id="d0e3546"></a><p class="title"><strong>Table 6.13. IS NULL Parameters</strong></p><div class="table-contents"><table summary="IS NULL Parameters" border="1"><colgroup><col class="parameter" /><col class="description" /></colgroup><thead><tr><th>Parameter</th><th>Description</th></tr></thead><tbody><tr><td>
                                            <p>expression</p>
                                        </td><td>
                                            <p>Expression to compare to NULL</p>
                                        </td></tr></tbody></table></div></div><p><br class="table-break" />
                    </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-IS-NULL-Examples"></a>Examples</h6></div></div></div><p>
                        Find queues having NULL description
                    </p><pre class="programlisting">
    SELECT *
    FROM queue
    WHERE description IS NULL
                    </pre></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-LESS-THAN"></a>LESS THAN</h6></div></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-LESS-THAN-Definition"></a>Definition and Usage</h6></div></div></div><p>
                        Less than operator is designated using "&lt;" character. It allows comparison of datetime, numeric and string values.
                        Both compared values must have same type.
                    </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-LESS-THAN-Syntax"></a>Syntax</h6></div></div></div><pre class="programlisting">
    expression1 &lt; expression2
                    </pre></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-LESS-THAN-Parameter-Values"></a>Parameter Values</h6></div></div></div><p>
                        </p><div class="table"><a id="d0e3596"></a><p class="title"><strong>Table 6.14. LESS THAN Parameters</strong></p><div class="table-contents"><table summary="LESS THAN Parameters" border="1"><colgroup><col class="parameter" /><col class="description" /></colgroup><thead><tr><th>Parameter</th><th>Description</th></tr></thead><tbody><tr><td>
                                            <p>expression1</p>
                                        </td><td>
                                            <p>Expression to compare to</p>
                                        </td></tr><tr><td>
                                            <p>expression2</p>
                                        </td><td>
                                            <p>Expression to compare with</p>
                                        </td></tr></tbody></table></div></div><p><br class="table-break" />
                    </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-LESS-THAN-Examples"></a>Examples</h6></div></div></div><p>
                        Find queues having message depth less than 1000
                    </p><pre class="programlisting">
    SELECT *
    FROM queue
    WHERE queueDepthMessages &lt; 1000
                    </pre></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-LESS-THAN-OR-EQUAL"></a>LESS THAN OR EQUAL</h6></div></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-LESS-THAN-OR-EQUAL-Definition"></a>Definition and Usage</h6></div></div></div><p>
                        Less than or equal operator is designated using "&lt;=" characters. It allows comparison of
                        datetime, numeric and string values. Both compared values must have same type.
                    </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-LESS-THAN-OR-EQUAL-Syntax"></a>Syntax</h6></div></div></div><pre class="programlisting">
    expression1 &lt;= expression2
                    </pre></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-LESS-THAN-OR-EQUAL-Parameter-Values"></a>Parameter Values</h6></div></div></div><p>
                        </p><div class="table"><a id="d0e3657"></a><p class="title"><strong>Table 6.15. LESS THAN OR EQUAL Parameters</strong></p><div class="table-contents"><table summary="LESS THAN OR EQUAL Parameters" border="1"><colgroup><col class="parameter" /><col class="description" /></colgroup><thead><tr><th>Parameter</th><th>Description</th></tr></thead><tbody><tr><td>
                                            <p>expression1</p>
                                        </td><td>
                                            <p>Expression to compare to</p>
                                        </td></tr><tr><td>
                                            <p>expression2</p>
                                        </td><td>
                                            <p>Expression to compare with</p>
                                        </td></tr></tbody></table></div></div><p><br class="table-break" />
                    </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-LESS-THAN-OR-EQUAL-Examples"></a>Examples</h6></div></div></div><p>
                        Find queues having message depth less than or equal to 1000
                    </p><pre class="programlisting">
    SELECT *
    FROM queue
    WHERE queueDepthMessages &lt;= 1000
                    </pre></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-LIKE"></a>LIKE</h6></div></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-LIKE-Definition"></a>Definition and Usage</h6></div></div></div><p>
                        The LIKE operator is used to search for a specified pattern in a string. There are two
                        wildcards often used in conjunction with the LIKE operator: the percent sign "%" represents
                        zero, one, or multiple characters; the question mark "?" represents one, single character.
                    </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-LIKE-Syntax"></a>Syntax</h6></div></div></div><pre class="programlisting">
    expression LIKE pattern
    expression LIKE pattern ESCAPE escapeCharacter
    expression LIKE (pattern)
    expression LIKE (pattern ESCAPE escapeCharacter)
                    </pre></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-LIKE-Parameter-Values"></a>Parameter Values</h6></div></div></div><p>
                        </p><div class="table"><a id="d0e3718"></a><p class="title"><strong>Table 6.16. LIKE Parameters</strong></p><div class="table-contents"><table summary="LIKE Parameters" border="1"><colgroup><col class="parameter" /><col class="description" /></colgroup><thead><tr><th>Parameter</th><th>Description</th></tr></thead><tbody><tr><td>
                                            <p>expression</p>
                                        </td><td>
                                            <p>Expression to compare to</p>
                                        </td></tr><tr><td>
                                            <p>pattern</p>
                                        </td><td>
                                            <p>Pattern to compare against</p>
                                        </td></tr><tr><td>
                                            <p>escapeCharacter</p>
                                        </td><td>
                                            <p>Character used to escape percent sign or question mark</p>
                                        </td></tr></tbody></table></div></div><p><br class="table-break" />
                    </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-LIKE-Examples"></a>Examples</h6></div></div></div><p>
                        Find queues having name starting with a string "broadcast"
                    </p><pre class="programlisting">
    SELECT *
    FROM queue
    WHERE name LIKE 'broadcast%'
                    </pre><p>
                        Find queues with name containing string "amqp_user1"
                    </p><pre class="programlisting">
    SELECT *
    FROM queue
    WHERE name LIKE '%amqp_user1%'
                    </pre></div></div></div><div class="section"><div class="titlepage"><div><div><h5 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-Conditional-Operators"></a>Conditional Operators</h5></div></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-CASE"></a>CASE</h6></div></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-CASE-Definition"></a>Definition and Usage</h6></div></div></div><p>
                        The CASE statement goes through conditions and returns a value when the first condition is met
                        (like an if-then-else statement). So, once a condition is true, it will stop reading and return
                        the result. If no conditions are true, it returns the value in the ELSE clause.
                    </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-CASE-Syntax"></a>Syntax</h6></div></div></div><pre class="programlisting">
    CASE
        WHEN condition1 THEN result1
        WHEN condition2 THEN result2
        WHEN conditionN THEN resultN
        ELSE result
    END
                    </pre></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-CASE-Parameter-Values"></a>Parameter Values</h6></div></div></div><p>
                        </p><div class="table"><a id="d0e3797"></a><p class="title"><strong>Table 6.17. CASE Parameters</strong></p><div class="table-contents"><table summary="CASE Parameters" border="1"><colgroup><col class="parameter" /><col class="description" /></colgroup><thead><tr><th>Parameter</th><th>Description</th></tr></thead><tbody><tr><td>
                                            <p>condition1 - conditionN</p>
                                        </td><td>
                                            <p>Conditions to estimate</p>
                                        </td></tr><tr><td>
                                            <p>result1 - resultN</p>
                                        </td><td>
                                            <p>Results to return</p>
                                        </td></tr></tbody></table></div></div><p><br class="table-break" />
                    </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-CASE-Examples"></a>Examples</h6></div></div></div><p>
                        Group queues into good (&lt; 60% of max depth), bad (60% - 90% of max depth) and critical
                        (&gt; 90% of max depth), count number of queues in each group. Consider queues with unlimited
                        depth being good.
                    </p><pre class="programlisting">
    SELECT
        COUNT(*),
        CASE
            WHEN maximumQueueDepthMessages != -1 AND maximumQueueDepthBytes != -1
                AND (queueDepthMessages &gt; maximumQueueDepthMessages * 0.9 OR queueDepthBytes &gt; maximumQueueDepthBytes * 0.9)
            THEN 'critical'
            WHEN maximumQueueDepthMessages != -1 AND maximumQueueDepthBytes != -1
                AND queueDepthMessages BETWEEN (maximumQueueDepthMessages * 0.6 AND maximumQueueDepthMessages * 0.9)
                OR queueDepthBytes BETWEEN (maximumQueueDepthBytes * 0.6 AND maximumQueueDepthBytes * 0.9)
            THEN 'bad'
            ELSE 'good'
        END AS queueState
    FROM queue
    GROUP BY queueState
                    </pre></div></div></div><div class="section"><div class="titlepage" /><p>
                The "AND" and "OR" operators are used to filter records based on more than one condition: the "AND"
                operator displays a record if all the conditions separated by "AND" are TRUE. The "OR" operator
                displays a record if any of the conditions separated by "OR" is TRUE. The "NOT" operator displays a
                record if the condition(s) is NOT TRUE.
            </p></div></div><div class="section"><div class="titlepage"><div><div><h4 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-Sorting-Results"></a>6.3.12.6. Sorting Results</h4></div></div></div><p>
            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:
        </p><pre class="programlisting">
    SELECT *
    FROM queue
        </pre><p>Few exceptions are following:</p><p>
            </p><div class="table"><a id="d0e3854"></a><p class="title"><strong>Table 6.18. Default sorting fields</strong></p><div class="table-contents"><table summary="Default sorting fields" border="1"><colgroup><col class="domain" /><col class="field-name" /></colgroup><thead><tr><th>Domain</th><th>Default sorting field</th></tr></thead><tbody><tr><td>
                                <p>AclRule</p>
                            </td><td>
                                <p>identity</p>
                            </td></tr><tr><td>
                                <p>Certificate</p>
                            </td><td>
                                <p>alias</p>
                            </td></tr><tr><td>
                                <p>ConnectionLimitRule</p>
                            </td><td>
                                <p>identity</p>
                            </td></tr></tbody></table></div></div><p><br class="table-break" />
        </p><p>Results of the following query will be sorted ascending by alias:</p><pre class="programlisting">
    SELECT *
    FROM certificate
        </pre><p>
            To apply another sorting rules clause `ORDER BY` should be used. It may contain one of the fields specified
            in the `SELECT` clause:
        </p><pre class="programlisting">
    SELECT
        id, name, state
    FROM queue
    ORDER BY name
        </pre><p>
            Alternatively it may contain fields not specified in `SELECT` clause:
        </p><pre class="programlisting">
    SELECT
        id, name, state
    FROM queue
    ORDER BY overflowPolicy
        </pre><p>
            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
        </p><pre class="programlisting">
    SELECT
        name, overflowPolicy
    FROM queue
    ORDER BY 2 DESC, 1 ASC
        </pre><p>
            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.
        </p></div><div class="section"><div class="titlepage"><div><div><h4 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-Aggregation"></a>6.3.12.7. Aggregation</h4></div></div></div><p>
            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
        </p><pre class="programlisting">
    SELECT COUNT(description)
    FROM queue
        </pre><p>
            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:
        </p><pre class="programlisting">
    SELECT COUNT(COALESCE(description, ''))
    FROM queue
        </pre><p>Alternatively</p><pre class="programlisting">
    SELECT COUNT(CASE WHEN description IS NULL THEN '' ELSE description END)
    FROM queue
        </pre><p>Several aggregation functions can be used together in the same query:</p><pre class="programlisting">
    SELECT
        COUNT(*),
        AVG(queueDepthMessages),
        SUM(queueDepthMessages),
        SUM(queueDepthBytes),
        MIN(queueDepthMessages),
        MAX(queueDepthMessages),
        MIN(queueDepthBytes),
        MAX(queueDepthBytes)
    FROM queue
        </pre></div><div class="section"><div class="titlepage"><div><div><h4 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-Grouping"></a>6.3.12.8. Grouping</h4></div></div></div><p>
            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:
        </p><pre class="programlisting">
    SELECT
        COUNT(*) AS cnt, identity
    FROM aclrule
    GROUP BY identity
    ORDER BY 1 DESC
        </pre><p>The result of the query:</p><pre class="programlisting">
    {
        "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
    }
        </pre><p>
            To filter the grouped result `HAVING` clause can be used:
        </p><pre class="programlisting">
    SELECT
        overflowPolicy, COUNT(*)
    FROM queue
    GROUP BY overflowPolicy
    HAVING SUM(queueDepthMessages) &gt; 1000
        </pre></div><div class="section"><div class="titlepage"><div><div><h4 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-Functions"></a>6.3.12.9. Functions</h4></div></div></div><div class="section"><div class="titlepage"><div><div><h5 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-Aggregation-Functions"></a>Aggregation Functions</h5></div></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-AVG"></a>AVG</h6></div></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-AVG-Definition"></a>Definition and Usage</h6></div></div></div><p>
                        The AVG() function returns the average value of a collection.
                    </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-AVG-Syntax"></a>Syntax</h6></div></div></div><pre class="programlisting">
    AVG(expression)
                    </pre></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-AVG-Parameter-Values"></a>Parameter Values</h6></div></div></div><p>
                        </p><div class="table"><a id="d0e3977"></a><p class="title"><strong>Table 6.19. AVG Parameters</strong></p><div class="table-contents"><table summary="AVG Parameters" border="1"><colgroup><col class="parameter" /><col class="description" /></colgroup><thead><tr><th>Parameter</th><th>Description</th></tr></thead><tbody><tr><td>
                                            <p>expression</p>
                                        </td><td>
                                            <p>Expression result average value of which should be found</p>
                                        </td></tr></tbody></table></div></div><p><br class="table-break" />
                    </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-AVG-Examples"></a>Examples</h6></div></div></div><p>
                        Find average amount of bytes used by queues with names starting with "broadcast"
                    </p><pre class="programlisting">
    SELECT
        AVG(queueDepthBytes)
    FROM queue
    WHERE name LIKE 'broadcast%'
                    </pre></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-COUNT"></a>COUNT</h6></div></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-COUNT-Definition"></a>Definition and Usage</h6></div></div></div><p>
                        The COUNT() function returns the number of items that matches a specified criterion.
                    </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-COUNT-Syntax"></a>Syntax</h6></div></div></div><pre class="programlisting">
    COUNT(expression)
    COUNT(DISTINCT expression)
                    </pre></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-COUNT-Parameter-Values"></a>Parameter Values</h6></div></div></div><p>
                        </p><div class="table"><a id="d0e4027"></a><p class="title"><strong>Table 6.20. COUNT Parameters</strong></p><div class="table-contents"><table summary="COUNT Parameters" border="1"><colgroup><col class="parameter" /><col class="description" /></colgroup><thead><tr><th>Parameter</th><th>Description</th></tr></thead><tbody><tr><td>
                                            <p>expression</p>
                                        </td><td>
                                            <p>Expression result of which should be counted</p>
                                        </td></tr></tbody></table></div></div><p><br class="table-break" />
                    </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-COUNT-Examples"></a>Examples</h6></div></div></div><p>
                        Find amount of queues with names starting with "broadcast"
                    </p><pre class="programlisting">
    SELECT
        COUNT (*)
    FROM queue
    WHERE name LIKE 'broadcast%'
                    </pre></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-MAX"></a>MAX</h6></div></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-MAX-Definition"></a>Definition and Usage</h6></div></div></div><p>
                        The MAX() function returns the maximum value of a collection.
                    </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-MAX-Syntax"></a>Syntax</h6></div></div></div><pre class="programlisting">
    MAX(expression)
                    </pre></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-MAX-Parameter-Values"></a>Parameter Values</h6></div></div></div><p>
                        </p><div class="table"><a id="d0e4077"></a><p class="title"><strong>Table 6.21. MAX Parameters</strong></p><div class="table-contents"><table summary="MAX Parameters" border="1"><colgroup><col class="parameter" /><col class="description" /></colgroup><thead><tr><th>Parameter</th><th>Description</th></tr></thead><tbody><tr><td>
                                            <p>expression</p>
                                        </td><td>
                                            <p>Expression result maximal value of which should be found</p>
                                        </td></tr></tbody></table></div></div><p><br class="table-break" />
                    </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-MAX-Examples"></a>Examples</h6></div></div></div><p>
                        Find maximal amount of bytes used by queues with names starting with "broadcast"
                    </p><pre class="programlisting">
    SELECT
        MAX(queueDepthBytes)
    FROM queue
    WHERE name LIKE 'broadcast%'
                    </pre></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-MIN"></a>MIN</h6></div></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-MIN-Definition"></a>Definition and Usage</h6></div></div></div><p>
                        The MIN() function returns the minimum value of a collection.
                    </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-MIN-Syntax"></a>Syntax</h6></div></div></div><pre class="programlisting">
    MIN(expression)
                    </pre></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-MIN-Parameter-Values"></a>Parameter Values</h6></div></div></div><p>
                        </p><div class="table"><a id="d0e4127"></a><p class="title"><strong>Table 6.22. MIN Parameters</strong></p><div class="table-contents"><table summary="MIN Parameters" border="1"><colgroup><col class="parameter" /><col class="description" /></colgroup><thead><tr><th>Parameter</th><th>Description</th></tr></thead><tbody><tr><td>
                                            <p>expression</p>
                                        </td><td>
                                            <p>Expression result minimal value of which should be found</p>
                                        </td></tr></tbody></table></div></div><p><br class="table-break" />
                    </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-MIN-Examples"></a>Examples</h6></div></div></div><p>
                        Find minimal amount of bytes used by queues with names starting with "broadcast"
                    </p><pre class="programlisting">
    SELECT
        MIN(queueDepthBytes)
    FROM queue
    WHERE name LIKE 'broadcast%'
                    </pre></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-SUM"></a>SUM</h6></div></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-SUM-Definition"></a>Definition and Usage</h6></div></div></div><p>
                        The SUM() function returns the total sum of a numeric collection.
                    </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-SUM-Syntax"></a>Syntax</h6></div></div></div><pre class="programlisting">
    SUM(expression)
                    </pre></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-SUM-Parameter-Values"></a>Parameter Values</h6></div></div></div><p>
                        </p><div class="table"><a id="d0e4177"></a><p class="title"><strong>Table 6.23. SUM Parameters</strong></p><div class="table-contents"><table summary="SUM Parameters" border="1"><colgroup><col class="parameter" /><col class="description" /></colgroup><thead><tr><th>Parameter</th><th>Description</th></tr></thead><tbody><tr><td>
                                            <p>expression</p>
                                        </td><td>
                                            <p>Expression result of which should be summed</p>
                                        </td></tr></tbody></table></div></div><p><br class="table-break" />
                    </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-SUM-Examples"></a>Examples</h6></div></div></div><p>
                        Find amount of bytes used by queues having names starting with "broadcast"
                    </p><pre class="programlisting">
    SELECT
        SUM(queueDepthBytes)
    FROM queue
    WHERE name LIKE 'broadcast%'
                    </pre></div></div></div><div class="section"><div class="titlepage"><div><div><h5 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-Datetime-Functions"></a>Datetime Functions</h5></div></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-CURRENT_TIMESTAMP"></a>CURRENT_TIMESTAMP</h6></div></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-CURRENT_TIMESTAMP-Definition"></a>Definition and Usage</h6></div></div></div><p>
                        The CURRENT_TIMESTAMP() function returns current date and time.
                    </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-CURRENT_TIMESTAMP-Syntax"></a>Syntax</h6></div></div></div><pre class="programlisting">
    CURRENT_TIMESTAMP()
                    </pre></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-CURRENT_TIMESTAMP-Parameter-Values"></a>Parameter Values</h6></div></div></div><p>Function has no parameters</p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-CURRENT_TIMESTAMP-Examples"></a>Examples</h6></div></div></div><p>
                        Find current date and time
                    </p><pre class="programlisting">
    SELECT CURRENT_TIMESTAMP()
                    </pre></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-DATE"></a>DATE</h6></div></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-DATE-Definition"></a>Definition and Usage</h6></div></div></div><p>
                        The DATE() function extracts the date part from a datetime expression.
                    </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-DATE-Syntax"></a>Syntax</h6></div></div></div><pre class="programlisting">
    DATE(expression)
                    </pre></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-DATE-Parameter-Values"></a>Parameter Values</h6></div></div></div><p>
                        </p><div class="table"><a id="d0e4255"></a><p class="title"><strong>Table 6.24. DATE Parameters</strong></p><div class="table-contents"><table summary="DATE Parameters" border="1"><colgroup><col class="parameter" /><col class="description" /></colgroup><thead><tr><th>Parameter</th><th>Description</th></tr></thead><tbody><tr><td>
                                            <p>expression</p>
                                        </td><td>
                                            <p>A valid date/datetime value</p>
                                        </td></tr></tbody></table></div></div><p><br class="table-break" />
                    </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-DATE-Examples"></a>Examples</h6></div></div></div><p>
                        Find certificates having validFrom equal to 01. January 2020
                    </p><pre class="programlisting">
    SELECT *
    FROM certificate
    WHERE DATE(validFrom) = '2020-01-01'
                    </pre><p>
                        Find certificates expiring between 01. January 2020 and 10. January 2020
                    </p><pre class="programlisting">
    SELECT *
    FROM certificate
    WHERE DATE(validUntil) BETWEEN ('2020-01-01', '2020-01-10')
                    </pre></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-DATEADD"></a>DATEADD</h6></div></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-DATEADD-Definition"></a>Definition and Usage</h6></div></div></div><p>
                        The DATEADD() function adds a time/date interval to a date and returns the date.
                    </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-DATEADD-Syntax"></a>Syntax</h6></div></div></div><pre class="programlisting">
    DATEADD(TIMEUNIT, VALUE, DATE)
                    </pre></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-DATEADD-Parameter-Values"></a>Parameter Values</h6></div></div></div><p>
                        </p><div class="table"><a id="d0e4309"></a><p class="title"><strong>Table 6.25. DATEADD Parameters</strong></p><div class="table-contents"><table summary="DATEADD Parameters" border="1"><colgroup><col class="parameter" /><col class="description" /></colgroup><thead><tr><th>Parameter</th><th>Description</th></tr></thead><tbody><tr><td>
                                            <p>TIMEUNIT</p>
                                        </td><td>
                                            <p>
                                                The type of time unit to add.
                                                Can be one of the following values:
                                                YEAR, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND, MILLISECOND
                                            </p>
                                        </td></tr><tr><td>
                                            <p>VALUE</p>
                                        </td><td>
                                            <p>
                                                The value of the time/date interval to add.
                                                Both positive and negative values are allowed
                                            </p>
                                        </td></tr><tr><td>
                                            <p>DATE</p>
                                        </td><td>
                                            <p>The date to be modified</p>
                                        </td></tr></tbody></table></div></div><p><br class="table-break" />
                    </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-DATEADD-Examples"></a>Examples</h6></div></div></div><p>
                        Find certificates expiring in less than 30 days
                    </p><pre class="programlisting">
    SELECT *
    FROM certificate
    WHERE DATEADD(DAY, -30, validUntil) &lt; CURRENT_TIMESTAMP()
    LIMIT 10 OFFSET 0
                    </pre></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-DATEDIFF"></a>DATEDIFF</h6></div></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-DATEDIFF-Definition"></a>Definition and Usage</h6></div></div></div><p>
                        The DATEDIFF() function returns the number of time units between two date values.
                    </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-DATEDIFF-Syntax"></a>Syntax</h6></div></div></div><pre class="programlisting">
    DATEDIFF(TIMEUNIT, DATE1, DATE2)
                    </pre></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-DATEDIFF-Parameter-Values"></a>Parameter Values</h6></div></div></div><p>
                        </p><div class="table"><a id="d0e4381"></a><p class="title"><strong>Table 6.26. DATEDIFF Parameters</strong></p><div class="table-contents"><table summary="DATEDIFF Parameters" border="1"><colgroup><col class="parameter" /><col class="description" /></colgroup><thead><tr><th>Parameter</th><th>Description</th></tr></thead><tbody><tr><td>
                                            <p>TIMEUNIT</p>
                                        </td><td>
                                            <p>
                                                Time unit to calculate difference.
                                                Can be one of the following values:
                                                YEAR, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND, MILLISECOND
                                            </p>
                                        </td></tr><tr><td>
                                            <p>DATE1</p>
                                        </td><td>
                                            <p>
                                                Start date
                                            </p>
                                        </td></tr><tr><td>
                                            <p>DATE2</p>
                                        </td><td>
                                            <p>
                                                End date
                                            </p>
                                        </td></tr></tbody></table></div></div><p><br class="table-break" />
                    </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-DATEDIFF-Examples"></a>Examples</h6></div></div></div><p>
                        Find certificate aliases and days until expiry
                    </p><pre class="programlisting">
    SELECT
        alias,
        DATEDIFF(DAY, CURRENT_TIMESTAMP(), validUntil) AS days_until_expiry
    FROM certificate
    LIMIT 10 OFFSET 0
                    </pre></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-EXTRACT"></a>EXTRACT</h6></div></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-EXTRACT-Definition"></a>Definition and Usage</h6></div></div></div><p>
                        The EXTRACT() function extracts a part from a given date.
                    </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-EXTRACT-Syntax"></a>Syntax</h6></div></div></div><pre class="programlisting">
    EXTRACT(TIMEUNIT FROM DATE)
                    </pre></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-EXTRACT-Parameter-Values"></a>Parameter Values</h6></div></div></div><p>
                        </p><div class="table"><a id="d0e4453"></a><p class="title"><strong>Table 6.27. EXTRACT Parameters</strong></p><div class="table-contents"><table summary="EXTRACT Parameters" border="1"><colgroup><col class="parameter" /><col class="description" /></colgroup><thead><tr><th>Parameter</th><th>Description</th></tr></thead><tbody><tr><td>
                                            <p>TIMEUNIT</p>
                                        </td><td>
                                            <p>
                                                Time unit to extract.
                                                Can be one of the following values:
                                                YEAR, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND, MILLISECOND
                                            </p>
                                        </td></tr><tr><td>
                                            <p>DATE</p>
                                        </td><td>
                                            <p>The date to extract a part from</p>
                                        </td></tr></tbody></table></div></div><p><br class="table-break" />
                    </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-EXTRACT-Examples"></a>Examples</h6></div></div></div><p>
                        Find certificates issued in January 2020
                    </p><pre class="programlisting">
    SELECT *
    FROM certificate
    WHERE EXTRACT(YEAR FROM validFrom) = 2020
    AND EXTRACT(MONTH FROM validFrom) = 1
    LIMIT 10 OFFSET 0
                    </pre></div></div></div><div class="section"><div class="titlepage"><div><div><h5 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-Null-Functions"></a>NULL Functions</h5></div></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-COALESCE"></a>COALESCE</h6></div></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-COALESCE-Definition"></a>Definition and Usage</h6></div></div></div><p>
                        The COALESCE() function returns the first non-null value in a list.
                    </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-COALESCE-Syntax"></a>Syntax</h6></div></div></div><pre class="programlisting">
    COALESCE(value_1, value_2, ...., value_n)
                    </pre></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-COALESCE-Parameter-Values"></a>Parameter Values</h6></div></div></div><p>
                        </p><div class="table"><a id="d0e4517"></a><p class="title"><strong>Table 6.28. COALESCE Parameters</strong></p><div class="table-contents"><table summary="COALESCE Parameters" border="1"><colgroup><col class="parameter" /><col class="description" /></colgroup><thead><tr><th>Parameter</th><th>Description</th></tr></thead><tbody><tr><td>
                                            <p>value_1 - value_n</p>
                                        </td><td>
                                            <p>The values to test</p>
                                        </td></tr></tbody></table></div></div><p><br class="table-break" />
                    </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-COALESCE-Examples"></a>Examples</h6></div></div></div><p>
                        Find count of queues having NULL description
                    </p><pre class="programlisting">
    SELECT
        COUNT(COALESCE(description, 'empty')) AS RESULT
    FROM queue
    HAVING COALESCE(description, 'empty') = 'empty'
                    </pre></div></div></div><div class="section"><div class="titlepage"><div><div><h5 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-Numeric-Functions"></a>Numeric Functions</h5></div></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-ABS"></a>ABS</h6></div></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-ABS-Definition"></a>Definition and Usage</h6></div></div></div><p>
                        The ABS() function returns the absolute value of a number.
                    </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-ABS-Syntax"></a>Syntax</h6></div></div></div><pre class="programlisting">
    ABS(number)
                    </pre></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-ABS-Parameter-Values"></a>Parameter Values</h6></div></div></div><p>
                        </p><div class="table"><a id="d0e4570"></a><p class="title"><strong>Table 6.29. ABS Parameters</strong></p><div class="table-contents"><table summary="ABS Parameters" border="1"><colgroup><col class="parameter" /><col class="description" /></colgroup><thead><tr><th>Parameter</th><th>Description</th></tr></thead><tbody><tr><td>
                                            <p>number</p>
                                        </td><td>
                                            <p>A numeric value</p>
                                        </td></tr></tbody></table></div></div><p><br class="table-break" />
                    </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-ABS-Examples"></a>Examples</h6></div></div></div><p>
                        Find absolute amount of days after the validFrom date of the certificates
                    </p><pre class="programlisting">
    SELECT
        ABS(DATEDIFF(DAY, CURRENT_TIMESTAMP(), validFrom))
    FROM certificate
                    </pre></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-ROUND"></a>ROUND</h6></div></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-ROUND-Definition"></a>Definition and Usage</h6></div></div></div><p>
                        The ROUND() function takes a numeric parameter and rounds it to the specified number
                        of decimal places.
                    </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-ROUND-Syntax"></a>Syntax</h6></div></div></div><pre class="programlisting">
    ROUND(number, decimals)
                    </pre></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-ROUND-Parameter-Values"></a>Parameter Values</h6></div></div></div><p>
                        </p><div class="table"><a id="d0e4620"></a><p class="title"><strong>Table 6.30. ROUND Parameters</strong></p><div class="table-contents"><table summary="ROUND Parameters" border="1"><colgroup><col class="parameter" /><col class="description" /></colgroup><thead><tr><th>Parameter</th><th>Description</th></tr></thead><tbody><tr><td>
                                            <p>number</p>
                                        </td><td>
                                            <p>The number to be rounded</p>
                                        </td></tr><tr><td>
                                            <p>decimals</p>
                                        </td><td>
                                            <p>The number of decimal places to round to</p>
                                        </td></tr></tbody></table></div></div><p><br class="table-break" />
                    </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-ROUND-Examples"></a>Examples</h6></div></div></div><p>
                        Find average queue depth in messages and round result to 2 decimal places
                    </p><pre class="programlisting">
    SELECT
        ROUND(AVG(queueDepthMessages)) as result
    FROM queue
                    </pre></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-TRUNC"></a>TRUNC</h6></div></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-TRUNC-Definition"></a>Definition and Usage</h6></div></div></div><p>
                        The TRUNC() function takes a numeric parameter and truncates it to the specified number
                        of decimal places.
                    </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-TRUNC-Syntax"></a>Syntax</h6></div></div></div><pre class="programlisting">
    TRUNC(number, decimals)
                    </pre></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-TRUNC-Parameter-Values"></a>Parameter Values</h6></div></div></div><p>
                        </p><div class="table"><a id="d0e4681"></a><p class="title"><strong>Table 6.31. TRUNC Parameters</strong></p><div class="table-contents"><table summary="TRUNC Parameters" border="1"><colgroup><col class="parameter" /><col class="description" /></colgroup><thead><tr><th>Parameter</th><th>Description</th></tr></thead><tbody><tr><td>
                                            <p>number</p>
                                        </td><td>
                                            <p>The number to be truncated</p>
                                        </td></tr><tr><td>
                                            <p>decimals</p>
                                        </td><td>
                                            <p>The number of decimal places to truncate to</p>
                                        </td></tr></tbody></table></div></div><p><br class="table-break" />
                    </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-TRUNC-Examples"></a>Examples</h6></div></div></div><p>
                        Find average queue depth in messages and truncate result to 2 decimal places
                    </p><pre class="programlisting">
    SELECT
        TRUNC(AVG(queueDepthMessages)) as result
    FROM queue
                    </pre></div></div></div><div class="section"><div class="titlepage"><div><div><h5 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-String-Functions"></a>String Functions</h5></div></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-CONCAT"></a>CONCAT</h6></div></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-CONCAT-Definition"></a>Definition and Usage</h6></div></div></div><p>
                        The CONCAT() function takes a variable number of arguments and concatenates them into a single string.
                        It requires a minimum of one input value, otherwise CONCAT will raise an error. CONCAT implicitly
                        converts all arguments to string types before concatenation. The implicit conversion to strings follows
                        the existing rules for data type conversions. If any argument is NULL, CONCAT returns NULL.
                    </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-CONCAT-Syntax"></a>Syntax</h6></div></div></div><pre class="programlisting">
    CONCAT(expression_1, expression_2, expression_3, ..., expression_n)
                    </pre></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-CONCAT-Parameter-Values"></a>Parameter Values</h6></div></div></div><p>
                        </p><div class="table"><a id="d0e4745"></a><p class="title"><strong>Table 6.32. LENGTH Parameters</strong></p><div class="table-contents"><table summary="LENGTH Parameters" border="1"><colgroup><col class="parameter" /><col class="description" /></colgroup><thead><tr><th>Parameter</th><th>Description</th></tr></thead><tbody><tr><td>
                                            <p>expression_1 - expression_n</p>
                                        </td><td>
                                            <p>The expressions to add together</p>
                                        </td></tr></tbody></table></div></div><p><br class="table-break" />
                    </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-CONCAT-Examples"></a>Examples</h6></div></div></div><p>
                        Output certificate alias and validity dates using format "alias: validFrom - validUntil"
                    </p><pre class="programlisting">
    SELECT
        CONCAT(alias, ': ', DATE(validFrom), ' - ', DATE(validUntil)) as validity
    FROM certificate
                    </pre></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-LENGTH"></a>LEN / LENGTH</h6></div></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-LENGTH-Definition"></a>Definition and Usage</h6></div></div></div><p>
                        The LEN() / LENGTH() function takes a string parameter and returns its length.
                        The implicit conversion to strings follows the existing rules for data
                        type conversions. If any argument is NULL, LEN / LENGTH returns 0.
                    </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-LENGTH-Syntax"></a>Syntax</h6></div></div></div><pre class="programlisting">
    LEN(string)
    LENGTH(string)
                    </pre></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-LENGTH-Parameter-Values"></a>Parameter Values</h6></div></div></div><p>
                        </p><div class="table"><a id="d0e4795"></a><p class="title"><strong>Table 6.33. LENGTH Parameters</strong></p><div class="table-contents"><table summary="LENGTH Parameters" border="1"><colgroup><col class="parameter" /><col class="description" /></colgroup><thead><tr><th>Parameter</th><th>Description</th></tr></thead><tbody><tr><td>
                                            <p>string</p>
                                        </td><td>
                                            <p>The string to count the length for</p>
                                        </td></tr></tbody></table></div></div><p><br class="table-break" />
                    </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-LENGTH-Examples"></a>Examples</h6></div></div></div><p>
                        Find certificate aliases having alias length greater than 10
                    </p><pre class="programlisting">
    SELECT
        alias
    FROM certificate
    WHERE LENGTH(alias) &gt; 10
    LIMIT 10 OFFSET 0
                    </pre></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-LOWER"></a>LOWER</h6></div></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-LOWER-Definition"></a>Definition and Usage</h6></div></div></div><p>
                        The LOWER() function takes a string parameter and converts it to lower case. The implicit
                        conversion to strings follows the existing rules for data type conversions.
                        If argument is NULL, LOWER returns NULL.
                    </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-LOWER-Syntax"></a>Syntax</h6></div></div></div><pre class="programlisting">
    LOWER(string)
                    </pre></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-LOWER-Parameter-Values"></a>Parameter Values</h6></div></div></div><p>
                        </p><div class="table"><a id="d0e4845"></a><p class="title"><strong>Table 6.34. LOWER Parameters</strong></p><div class="table-contents"><table summary="LOWER Parameters" border="1"><colgroup><col class="parameter" /><col class="description" /></colgroup><thead><tr><th>Parameter</th><th>Description</th></tr></thead><tbody><tr><td>
                                            <p>string</p>
                                        </td><td>
                                            <p>The string to convert</p>
                                        </td></tr></tbody></table></div></div><p><br class="table-break" />
                    </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-LOWER-Examples"></a>Examples</h6></div></div></div><p>
                        Filter connections by principal name (case-insensitive)
                    </p><pre class="programlisting">
    SELECT *
    FROM connection
    WHERE LOWER(principal) = 'amqp_user1'
    LIMIT 10 OFFSET 0
                    </pre></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-LTRIM"></a>LTRIM</h6></div></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-LTRIM-Definition"></a>Definition and Usage</h6></div></div></div><p>
                        The LTRIM() function removes leading spaces from a string.
                        If argument is NULL, RTRIM returns NULL.
                    </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-LTRIM-Syntax"></a>Syntax</h6></div></div></div><pre class="programlisting">
    LTRIM(string)
    LTRIM(string, chars)
                    </pre></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-LTRIM-Parameter-Values"></a>Parameter Values</h6></div></div></div><p>
                        </p><div class="table"><a id="d0e4895"></a><p class="title"><strong>Table 6.35. LTRIM Parameters</strong></p><div class="table-contents"><table summary="LTRIM Parameters" border="1"><colgroup><col class="parameter" /><col class="description" /></colgroup><thead><tr><th>Parameter</th><th>Description</th></tr></thead><tbody><tr><td>
                                            <p>string</p>
                                        </td><td>
                                            <p>The string to remove leading and trailing spaces from</p>
                                        </td></tr><tr><td>
                                            <p>chars</p>
                                        </td><td>
                                            <p>Specific characters to remove</p>
                                        </td></tr></tbody></table></div></div><p><br class="table-break" />
                    </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-LTRIM-Examples"></a>Examples</h6></div></div></div><p>
                        Find connection remote addresses
                    </p><pre class="programlisting">
    SELECT
        LTRIM(remoteAddress, '/') AS remoteAddress
    FROM connection
                    </pre></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-POSITION"></a>POSITION</h6></div></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-POSITION-Definition"></a>Definition and Usage</h6></div></div></div><p>
                        The POSITION() function takes a search pattern and a source string as parameters and returns
                        the position of the first occurrence of a pattern in a source string. If the pattern is not
                        found within the source string, this function returns 0.
                        Optionally takes third integer parameter, defining from which position search should be started.
                        Third parameter should be an integer greater than 0. If source string is NULL, returns zero.
                    </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-POSITION-Syntax"></a>Syntax</h6></div></div></div><pre class="programlisting">
    POSITION(pattern IN source)
    POSITION(pattern IN source, startIndex)
                    </pre></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-POSITION-Parameter-Values"></a>Parameter Values</h6></div></div></div><p>
                        </p><div class="table"><a id="d0e4956"></a><p class="title"><strong>Table 6.36. POSITION Parameters</strong></p><div class="table-contents"><table summary="POSITION Parameters" border="1"><colgroup><col class="parameter" /><col class="description" /></colgroup><thead><tr><th>Parameter</th><th>Description</th></tr></thead><tbody><tr><td>
                                            <p>pattern</p>
                                        </td><td>
                                            <p>The pattern to search for in source</p>
                                        </td></tr><tr><td>
                                            <p>source</p>
                                        </td><td>
                                            <p>The original string that will be searched</p>
                                        </td></tr><tr><td>
                                            <p>startIndex</p>
                                        </td><td>
                                            <p>The index from which search will be started</p>
                                        </td></tr></tbody></table></div></div><p><br class="table-break" />
                    </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-POSITION-Examples"></a>Examples</h6></div></div></div><p>
                        Find queues having string "broadcast" in their names
                    </p><pre class="programlisting">
    SELECT *
    FROM queue
    WHERE POSITION('broadcast', name) &gt; 0
    LIMIT 10 OFFSET 0
                    </pre></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-REPLACE"></a>REPLACE</h6></div></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-REPLACE-Definition"></a>Definition and Usage</h6></div></div></div><p>
                        The REPLACE() function replaces all occurrences of a substring within
                        a string, with a new substring. If source string is NULL, returns NULL.
                    </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-REPLACE-Syntax"></a>Syntax</h6></div></div></div><pre class="programlisting">
    REPLACE(source, pattern, replacement)
                    </pre></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-REPLACE-Parameter-Values"></a>Parameter Values</h6></div></div></div><p>
                        </p><div class="table"><a id="d0e5028"></a><p class="title"><strong>Table 6.37. REPLACE Parameters</strong></p><div class="table-contents"><table summary="REPLACE Parameters" border="1"><colgroup><col class="parameter" /><col class="description" /></colgroup><thead><tr><th>Parameter</th><th>Description</th></tr></thead><tbody><tr><td>
                                            <p>source</p>
                                        </td><td>
                                            <p>The original string</p>
                                        </td></tr><tr><td>
                                            <p>pattern</p>
                                        </td><td>
                                            <p>The substring to be replaced</p>
                                        </td></tr><tr><td>
                                            <p>replacement</p>
                                        </td><td>
                                            <p>The new replacement substring</p>
                                        </td></tr></tbody></table></div></div><p><br class="table-break" />
                    </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-REPLACE-Examples"></a>Examples</h6></div></div></div><p>
                        Output certificate issuer names without leading "CN="
                    </p><pre class="programlisting">
    SELECT
        REPLACE(issuerName, 'CN=', '') AS issuer
    FROM certificate
    LIMIT 10 OFFSET 0
                    </pre></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-RTRIM"></a>RTRIM</h6></div></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-RTRIM-Definition"></a>Definition and Usage</h6></div></div></div><p>
                        The RTRIM() function removes trailing spaces from a string.
                        If argument is NULL, RTRIM returns NULL.
                    </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-RTRIM-Syntax"></a>Syntax</h6></div></div></div><pre class="programlisting">
    RTRIM(string)
    RTRIM(string, chars)
                    </pre></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-RTRIM-Parameter-Values"></a>Parameter Values</h6></div></div></div><p>
                        </p><div class="table"><a id="d0e5100"></a><p class="title"><strong>Table 6.38. RTRIM Parameters</strong></p><div class="table-contents"><table summary="RTRIM Parameters" border="1"><colgroup><col class="parameter" /><col class="description" /></colgroup><thead><tr><th>Parameter</th><th>Description</th></tr></thead><tbody><tr><td>
                                            <p>string</p>
                                        </td><td>
                                            <p>The string to remove leading and trailing spaces from</p>
                                        </td></tr><tr><td>
                                            <p>chars</p>
                                        </td><td>
                                            <p>Specific characters to remove</p>
                                        </td></tr></tbody></table></div></div><p><br class="table-break" />
                    </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-RTRIM-Examples"></a>Examples</h6></div></div></div><p>
                        Find connection remote addresses
                    </p><pre class="programlisting">
    SELECT
        RTRIM(remoteAddress)
    FROM connection
                    </pre></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-SUBSTRING"></a>SUBSTR / SUBSTRING</h6></div></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-SUBSTRING-Definition"></a>Definition and Usage</h6></div></div></div><p>
                        The SUBSTRING() function takes a source parameter, a start index parameter and optional
                        length parameter. Returns substring of a source string from the start index to the end or
                        using the length parameter. If source string is NULL, return NULL.
                    </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-SUBSTRING-Syntax"></a>Syntax</h6></div></div></div><pre class="programlisting">
    SUBSTRING(source, startIndex, length)
                    </pre></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-SUBSTRING-Parameter-Values"></a>Parameter Values</h6></div></div></div><p>
                        </p><div class="table"><a id="d0e5161"></a><p class="title"><strong>Table 6.39. SUBSTRING Parameters</strong></p><div class="table-contents"><table summary="SUBSTRING Parameters" border="1"><colgroup><col class="parameter" /><col class="description" /></colgroup><thead><tr><th>Parameter</th><th>Description</th></tr></thead><tbody><tr><td>
                                            <p>source</p>
                                        </td><td>
                                            <p>The string to extract from</p>
                                        </td></tr><tr><td>
                                            <p>startIndex</p>
                                        </td><td>
                                            <p>
                                                The start position. Can be both a positive or negative number. If it is
                                                a positive number, this function extracts from the beginning of the string.
                                                If it is a negative number, function extracts from the end of the string
                                            </p>
                                        </td></tr><tr><td>
                                            <p>length</p>
                                        </td><td>
                                            <p>
                                                The number of characters to extract. If omitted, the whole string will
                                                be returned (from the start position). If zero or negative, an empty
                                                string is returned
                                            </p>
                                        </td></tr></tbody></table></div></div><p><br class="table-break" />
                    </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-SUBSTRING-Examples"></a>Examples</h6></div></div></div><p>
                        Find queue names removing from name part before the `.` character
                    </p><pre class="programlisting">
    SELECT
        SUBSTRING(name, POSITION('.', name) + 1, LEN(name) - POSITION('.', name))
    FROM queue
                    </pre></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-TRIM"></a>TRIM</h6></div></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-TRIM-Definition"></a>Definition and Usage</h6></div></div></div><p>
                        The TRIM() function removes both leading and trailing spaces from a string.
                        If argument is NULL, TRIM returns NULL.
                    </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-TRIM-Syntax"></a>Syntax</h6></div></div></div><pre class="programlisting">
    TRIM(string)
    TRIM(string, chars)
                    </pre></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-TRIM-Parameter-Values"></a>Parameter Values</h6></div></div></div><p>
                        </p><div class="table"><a id="d0e5233"></a><p class="title"><strong>Table 6.40. TRIM Parameters</strong></p><div class="table-contents"><table summary="TRIM Parameters" border="1"><colgroup><col class="parameter" /><col class="description" /></colgroup><thead><tr><th>Parameter</th><th>Description</th></tr></thead><tbody><tr><td>
                                            <p>string</p>
                                        </td><td>
                                            <p>The string to remove leading and trailing spaces from</p>
                                        </td></tr><tr><td>
                                            <p>chars</p>
                                        </td><td>
                                            <p>Specific characters to remove</p>
                                        </td></tr></tbody></table></div></div><p><br class="table-break" />
                    </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-TRIM-Examples"></a>Examples</h6></div></div></div><p>
                        Find connections remote addresses removing `/` characters from both sides
                    </p><pre class="programlisting">
    SELECT
        TRIM(remoteAddress, '/')
    FROM connection
                    </pre></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-UPPER"></a>UPPER</h6></div></div></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-UPPER-Definition"></a>Definition and Usage</h6></div></div></div><p>
                        The UPPER() function takes a string parameter and converts it to upper case. The implicit
                        conversion to strings follows the existing rules for data type conversions. If argument is NULL,
                        UPPER returns NULL.
                    </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-UPPER-Syntax"></a>Syntax</h6></div></div></div><pre class="programlisting">
    UPPER(string)
                    </pre></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-UPPER-Parameter-Values"></a>Parameter Values</h6></div></div></div><p>
                        </p><div class="table"><a id="d0e5294"></a><p class="title"><strong>Table 6.41. UPPER Parameters</strong></p><div class="table-contents"><table summary="UPPER Parameters" border="1"><colgroup><col class="parameter" /><col class="description" /></colgroup><thead><tr><th>Parameter</th><th>Description</th></tr></thead><tbody><tr><td>
                                            <p>string</p>
                                        </td><td>
                                            <p>The string to convert</p>
                                        </td></tr></tbody></table></div></div><p><br class="table-break" />
                    </p></div><div class="section"><div class="titlepage"><div><div><h6 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-UPPER-Examples"></a>Examples</h6></div></div></div><p>
                        Filter connections by principal name (case-insensitive)
                    </p><pre class="programlisting">
    SELECT *
    FROM connection
    WHERE UPPER(principal) = 'AMQP_USER1'
    LIMIT 10 OFFSET 0
                    </pre></div></div></div></div><div class="section"><div class="titlepage"><div><div><h4 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-Set-Operations"></a>6.3.12.10. Set Operations</h4></div></div></div><p>
            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:
        </p><pre class="programlisting">
    SELECT UPPER(alias)
    FROM certificate
    UNION
    SELECT UPPER(name)
    FROM user
        </pre><p>
            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:
        </p><pre class="programlisting">
    SELECT name
    FROM queue
    MINUS
    SELECT destination
    FROM binding
        </pre><p>
            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:
        </p><pre class="programlisting">
    SELECT UPPER(alias)
    FROM certificate
    INTERSECT
    SELECT UPPER(name)
    FROM user
        </pre></div><div class="section"><div class="titlepage"><div><div><h4 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-Subqueries"></a>6.3.12.11. Subqueries</h4></div></div></div><p>
            When executing subquery parent query domain mat be passed into the subquery using alias.
            E.g. this query
        </p><pre class="programlisting">
    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
        </pre><p>
            returns following result:
        </p><pre class="programlisting">
    {
        "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
    }
        </pre><p>
            Query
        </p><pre class="programlisting">
    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.#'
        </pre><p>
            returns following result:
        </p><pre class="programlisting">
    {
        "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
    }
        </pre><p>
            Query
        </p><pre class="programlisting">
    SELECT
        alias,
        (SELECT COUNT(id) FROM queue WHERE POSITION(UPPER(c.alias) IN name) &gt; 0) AS queueCount
    FROM certificate c
        </pre><p>
            returns following result:
        </p><pre class="programlisting">
    {
        "results": [
            {
                "alias": "xxx",
                "queueCount": 5
            },
            {
                "alias": "xxy",
                "queueCount": 5
            },
            {
                "alias": "xxz",
                "queueCount": 7
            }
        ],
        "total": 3
    }
        </pre></div><div class="section"><div class="titlepage"><div><div><h4 class="title"><a id="Java-Broker-Management-Channel-REST-Query-Engine-Performance-Tips"></a>6.3.12.12. Performance Tips</h4></div></div></div><p>
            Try to select entity fields by names instead of using an asterix. For example, this query
        </p><pre class="programlisting">
    SELECT
        id, name, state, overflowPolicy, expiryPolicy
    FROM queue
        </pre><p>
            will be executed faster than this one:
        </p><pre class="programlisting">
    SELECT *
    FROM queue
        </pre><p>Try to use `LIMIT` and `OFFSET` clauses where applicable to reduce the response JSON size:</p><pre class="programlisting">
    SELECT
        id, name, state, overflowPolicy, expiryPolicy
    FROM queue
    LIMIT 10 OFFSET 0
        </pre></div></div><div class="section"><div class="titlepage"><div><div><h3 class="title"><a id="Java-Broker-Management-Channel-REST-API-CORS"></a>6.3.13. Cross Origin Resource Sharing (CORS)</h3></div></div></div><p> The Broker supports Cross Origin Resource Sharing (CORS)
      to allow web management consoles other than the one embedded in the
      broker to use the REST API.  This feature must be enabled by configuring
      the CORS Allow Origins and related attributes on the
      <a class="xref" href="Java-Broker-Management-Managing-Plugin-HTTP.html" title="7.17. HTTP Plugin">Section 7.17, “HTTP Plugin”</a>
    </p></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="Java-Broker-Management-Channel-Web-Console.html">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="Java-Broker-Management-Channel.html">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="Java-Broker-Management-Metrics.html">Next</a></td></tr><tr><td width="40%" align="left" valign="top">6.2. Web Management Console </td><td width="20%" align="center"><a accesskey="h" href="Apache-Qpid-Broker-J-Book.html">Home</a></td><td width="40%" align="right" valign="top"> 6.4. Prometheus Metrics</td></tr></table></div></div>