Query Service REST API
- reference
Overview
The Query Service REST API is provided by the Query service. This API enables you to run SQL++ queries and set request-level parameters.
Version information
Version : 7.6
Host information
{scheme}://{host}:{port}
The URL scheme, host, and port are as follows.
Component | Description |
---|---|
scheme |
The URL scheme. Use Values: |
host |
The host name or IP address of a node running the Query service. Example: |
port |
The Query service REST port. Use Values: |
Resources
This section describes the operations available with this REST API.
Read-Only Query Service
GET /query/service
Description
Enables you to execute a SQL++ statement. This method allows you to run queries and modifying statements, and specify query parameters. It does not allow you to run modifying statements.
This endpoint is intended for situations where use of the POST
method is restricted.
Parameters
Name | Description | Schema |
---|---|---|
body |
Specify the parameters in the query URL in URL-encoded format. The format for URL-encoded parameters is consistent with the syntax for variables according to RFC 6570. |
Responses
This section describes the response HTTP status codes.
HTTP Code | Description | Schema |
---|---|---|
200 |
The operation was successful. |
|
400 |
Bad Request. The request cannot be processed for one of the following reasons: the statement contains a SQL++ syntax error; the request has a missing or unrecognized HTTP parameter; the request is badly formatted — for example, the request body contains a JSON syntax error. |
|
401 |
Unauthorized. The credentials provided with the request are missing or invalid. |
|
403 |
Forbidden. There is a read-only violation. Either there was an attempt to create or update in a GET request or a POST request where |
|
404 |
Not found. The statement in the request references an invalid namespace or keyspace. |
|
405 |
Method not allowed. The statement in the request references an invalid namespace or keyspace. |
|
409 |
Conflict. There is an attempt to create an object (keyspace or index) that already exists. |
|
410 |
Gone. The server is shutting down gracefully. Previously made requests are being completed, but no new requests are being accepted. |
|
413 |
Payload too large. The query is too large for the Query Service to process. |
|
500 |
Internal server error. There was an unforeseen problem processing the request. |
|
503 |
Service unavailable. There is an issue (that is possibly temporary) preventing the request being processed; the request queue is full or the data store is not accessible. |
Example HTTP Request
GET request with query parameters
curl -v http://localhost:8093/query/service?statement=SELECT%20name%20FROM%20%60travel-sample%60.inventory.hotel%20LIMIT%201%3B \
-u Administrator:password
For further examples, refer to Examples.
Query Service
POST /query/service
Description
Enables you to execute a SQL++ statement. This method allows you to run queries and modifying statements, and specify query parameters.
Parameters
For POST requests, you can specify form data parameters using the application/x-www-form-urlencoded
MIME type.
Alternatively, you can specify parameters in the request body using the application/json
MIME type with the Content-Type
header.
Name | Description | Schema |
---|---|---|
Body |
An object specifying one or more query parameters. |
Responses
This section describes the response HTTP status codes.
HTTP Code | Description | Schema |
---|---|---|
200 |
The operation was successful. |
|
400 |
Bad Request. The request cannot be processed for one of the following reasons: the statement contains a SQL++ syntax error; the request has a missing or unrecognized HTTP parameter; the request is badly formatted — for example, the request body contains a JSON syntax error. |
|
401 |
Unauthorized. The credentials provided with the request are missing or invalid. |
|
403 |
Forbidden. There is a read-only violation. Either there was an attempt to create or update in a GET request or a POST request where |
|
404 |
Not found. The statement in the request references an invalid namespace or keyspace. |
|
405 |
Method not allowed. The statement in the request references an invalid namespace or keyspace. |
|
409 |
Conflict. There is an attempt to create an object (keyspace or index) that already exists. |
|
410 |
Gone. The server is shutting down gracefully. Previously made requests are being completed, but no new requests are being accepted. |
|
413 |
Payload too large. The query is too large for the Query Service to process. |
|
500 |
Internal server error. There was an unforeseen problem processing the request. |
|
503 |
Service unavailable. There is an issue (that is possibly temporary) preventing the request being processed; the request queue is full or the data store is not accessible. |
Example HTTP Request
POST request with parameters as form data
curl -v http://localhost:8093/query/service \
-d 'statement=SELECT name FROM `travel-sample`.inventory.hotel LIMIT 1' \
-u Administrator:password
Because it is sent as form data, the statement in this example avoids using a semicolon.
POST request with parameters in JSON format
curl http://localhost:8093/query/service \
-H 'Content-Type: application/json' \
-d '{ "statement": "SELECT name FROM `travel-sample`.inventory.hotel LIMIT 1;" }' \
-u Administrator:password
Conversely, because it is sent as a JSON object, the statement in this example can contain a semicolon.
For further examples, refer to Examples.
Definitions
This section describes the properties consumed and returned by this REST API.
Conditions
Controls
Credentials
Execution Timings
Metrics
Profile
Request Parameters
Response Body
Statistics
Conditions
Property | Schema | |
---|---|---|
code |
A unique number that identifies the error or warning. The code ranges are partitioned by component. The codes can also include parts that indicate severity and transience. This property is always present in every condition returned in the Query REST API or captured in a log. |
Integer |
msg |
A message describing the error or warning in detail. This property is always present in every condition returned in the Query REST API or captured in a log. |
String |
name |
Unique name that has a 1:1 mapping to the code. Uniquely identifies the condition. This property is helpful for pattern matching and can have meaning, making it more memorable than the code. The name should be fully qualified. Example: |
String |
sev |
One of the following SQL++ severity levels, listed in order of severity:
|
Integer |
temp |
Indicates if the condition is transient — for example, the queue is full. If the value is |
Boolean |
Additional elements not listed here might also be present. Clients and consumers of the REST API or the logs must accommodate any additional elements.
Controls
Property | Schema | |
---|---|---|
scan_consistency |
The value of the query setting Scan Consistency used for the query. |
String |
use_cbo |
Whether the cost-based optimizer was enabled for the query. |
Boolean |
memoryQuota |
The memory quota for the request, in MB. This property is only returned if a memory quota was set for the query. |
Integer (unsigned) |
stmtType |
The type of query statement. Example: |
String |
Additional elements not listed here might also be present. Clients and consumers of the REST API or the logs must accommodate any additional elements.
Credentials
Property | Schema | |
---|---|---|
user |
An identity for authentication. Note that bucket names may be prefixed with Example: |
String |
pass |
A password for authentication. Example: |
String |
Execution Timings
Property | Schema | |
---|---|---|
#operator |
Name of the operator. Example: |
String |
#stats |
Statistics collected for the operator. |
|
~child |
Further nested operators, each with their own execution timings. |
Object |
Metrics
Property | Schema | |
---|---|---|
elapsedTime |
The total time taken for the request, that is the time from when the request was received until the results were returned. |
String |
executionTime |
The time taken for the execution of the request, that is the time from when query execution started until the results were returned. |
String |
resultCount |
The total number of objects in the results. |
Integer (unsigned) |
resultSize |
The total number of bytes in the results. |
Integer (unsigned) |
mutationCount |
The number of mutations that were made during the request. |
Integer (unsigned) |
sortCount |
The number of objects that were sorted. Present only if the request includes If a query includes ORDER BY, LIMIT, or OFFSET clauses, an application can use the |
Integer (unsigned) |
usedMemory |
The amount of document memory used to execute the request. This property is only returned if a memory quota was set for the query. |
Integer (unsigned) |
errorCount |
The number of errors that occurred during the request. |
Integer (unsigned) |
warningCount |
The number of warnings that occurred during the request. |
Integer (unsigned) |
Additional elements not listed here might also be present. Clients and consumers of the REST API or the logs must accommodate any additional elements.
Profile
Property | Schema | |
---|---|---|
requestTime |
Timestamp when the query was received. |
Date (date-time) |
servicingHost |
IP address and port number of the node where the query was executed. |
String |
phaseCounts |
Count of documents processed at selective phases involved in the query execution, such as authorize, index scan, fetch, parse, plan, run, etc. Example: |
Object |
phaseOperators |
Indicates the numbers of each kind of query operator involved in different phases of the query processing. For instance, a non-covering index path might involve one index scan and one fetch operator. A join would probably involve two or more fetches, one per keyspace. A union select would have twice as many operator counts, one per each branch of the union. This is in essence the count of all the operators in the Example: |
Object |
phaseTimes |
Cumulative execution times for various phases involved in the query execution, such as authorize, index scan, fetch, parse, plan, run, etc. Example: |
Object |
executionTimings |
Present only if The execution details for various phases involved in the query execution, such as kernel and service execution times, number of documents processed at each query operator in each phase, and number of phase switches. |
Request Parameters
Property | Schema | |
---|---|---|
args |
Supplies the values for positional parameters in the statement. Applicable if the statement or prepared statement contains 1 or more positional parameters. The value is an array of JSON values, one for each positional parameter in the statement. Refer to Named Parameters and Positional Parameters for details. Example: |
Any Type array |
atrcollection |
Specifies the collection where the active transaction record (ATR) is stored. The collection must be present. If not specified, the ATR is stored in the default collection in the default scope in the bucket containing the first mutated document within the transaction. The value must be a string in the form The node-level Example: |
String |
auto_execute |
Specifies that prepared statements should be executed automatically as soon as they are created. This saves you from having to make two separate requests in cases where you want to prepare a statement and execute it immediately. Refer to Auto-Execute for more information. Default: |
Boolean |
client_context_id |
A piece of data supplied by the client that is echoed in the response, if present. SQL++ is agnostic about the content of this parameter; it is just echoed in the response.
|
String |
compression |
Compression format to use for response data on the wire. Values are case-insensitive. Values: |
String |
controls |
Specifies if there should be a controls section returned with the request results. When set to If the request qualifies for caching, these values will also be cached in the The node-level Example: |
Boolean |
creds |
Specifies the login credentials. The Query API supports two types of identity: local (or bucket) and admin. The format is an identity and password. You can specify credentials for multiple identities. If credentials are supplied in the request header, then HTTP Basic Authentication takes precedence and Example: |
Credentials array |
durability_level |
The level of durability for mutations produced by the request. If the request contains a Durability is also supported for non-transactional DML statements.
In this case, the If not specified, the default durability level is Values: |
String |
encoded_plan |
In Couchbase Server 6.5 and later, this parameter is ignored and has no effect. It is included for compatibility with previous versions of Couchbase Server. |
String |
encoding |
Desired character encoding for the query results. Only possible value is Default: |
String |
format |
Desired format for the query results. Values are case-insensitive. Values: |
String |
kvtimeout |
The approximate time to wait for a KV get operation before timing out.
This applies to statements within a transaction, and to non-transactional statements, whether If The value for this parameter is a string.
Its format includes an amount and a mandatory unit, e.g.
Specify a duration of Default: |
String |
max_parallelism |
Specifies the maximum parallelism for the query. The node-level In addition, the cluster-level To enable queries to run in parallel, you must specify the cluster-level The default value is the same as the number of partitions of the index selected for the query. Example: |
Integer (int32) |
memory_quota |
Specifies the maximum amount of memory the request may use, in MB. Specify This parameter enforces a ceiling on the memory used for the tracked documents required for processing a request. It does not take into account any other memory that might be used to process a request, such as the stack, the operators, or some intermediate values. Within a transaction, this setting enforces the memory quota for the transaction by tracking the delta table and the transaction log (approximately). The node-level In addition, the cluster-level Default: |
Integer (int32) |
metrics |
Specifies that metrics should be returned with query results. Default: |
Boolean |
namespace |
Specifies the namespace to use. Currently, only the Example: |
String |
numatrs |
Specifies the total number of active transaction records. Must be a positive integer. The node-level In addition, the cluster-level Default: |
Integer (int32) |
pipeline_batch |
Controls the number of items execution operators can batch for Fetch from the KV. The node-level In addition, the cluster-level Example: |
Integer (int32) |
pipeline_cap |
Maximum number of items each execution operator can buffer between various operators. The node-level In addition, the cluster-level Example: |
Integer (int32) |
prepared |
Required if The name of the prepared SQL++ statement to be executed. Refer to EXECUTE for examples. If both Example: |
String |
preserve_expiry |
Specifies whether documents should keep their current expiration setting when modified by a DML statement. If If Not supported for statements in a transaction. Default: |
Boolean |
pretty |
Specifies the query results returned in pretty format. The node-level Example: |
Boolean |
profile |
Specifies if there should be a profile section returned with the request results. The valid values are:
If The node-level Values: |
String |
query_context |
Specifies the namespace, bucket, and scope used to resolve partial keyspace references within the request. The query context may be a full path, containing namespace, bucket, and scope; or a relative path, containing just the bucket and scope.
Currently, only the Default: |
String |
readonly |
Controls whether a query can change a resulting recordset. If
When using GET requests, it's best to set Default: |
Boolean |
scan_cap |
Maximum buffered channel size between the indexer client and the query service for index scans. This parameter controls when to use scan backfill. Use The node-level In addition, the cluster-level Example: |
Integer (int32) |
scan_consistency |
Specifies the consistency guarantee or constraint for index scanning. The valid values are:
Values are case-insensitive. For multi-statement requests, the default behavior is RYOW within each request.
If you want to disable RYOW within a request, add a separate If the request contains a Values: |
String |
scan_vector |
Required if Specify the lower bound vector timestamp for one keyspace when using Scan vectors are built of two-element [
Scan vectors have two forms:
Note that For queries referencing multiple keyspaces, use Example: |
Object |
scan_vectors |
Required if A map from keyspace names to scan vectors.
See The scan vectors can be Full or Sparse. |
Object |
scan_wait |
Can be supplied with Specifies the maximum time the client is willing to wait for an index to catch up to the vector timestamp in the request. Specifies how much time the client is willing to wait for the indexer to satisfy the required Its format includes an amount and a mandatory unit, e.g.
Specify Default: |
String (duration) |
signature |
Include a header for the results schema in the response. Default: |
Boolean |
sort_projection |
If If Default: |
Boolean |
statement |
Required if Any valid SQL++ statement for a POST request, or a read-only SQL++ statement (SELECT, EXPLAIN) for a GET request. If both When specifying the request parameters as form data, the statement may not contain an unescaped semicolon ( This restriction does not apply when specifying the request parameters in JSON format. Example: |
String |
timeout |
Maximum time to spend on the request before timing out. The value for this parameter is a string.
Its format includes an amount and a mandatory unit, e.g.
Specify a duration of If tximplicit or txid is set, this parameter is ignored. The request inherits the remaining time of the transaction as timeout. The node-level In addition, the cluster-level Example: |
String (duration) |
txdata |
Transaction data. For internal use only. |
Object |
txid |
Required for statements within a transaction. Transaction ID. Specifies the transaction to which a statement belongs. For use with DML statements within a transaction, rollbacks, and commits. The transaction ID should be the same as the transaction ID generated by the Example: |
UUID (UUID) |
tximplicit |
Specifies that a DML statement is a singleton transaction. When this parameter is true, the Query service starts a transaction and executes the statement. If execution is successful, the Query service commits the transaction; otherwise the transaction is rolled back. The statement may not be part of an ongoing transaction.
If the txid request-level parameter is set, the Default: |
Boolean |
txstmtnum |
Transaction statement number. The transaction statement number must be a positive integer, and must be higher than any previous transaction statement numbers in the transaction. If the transaction statement number is lower than the transaction statement number for any previous statement, an error is generated. Example: |
Integer (int32) |
txtimeout |
Maximum time to spend on a transaction before timing out.
Only applies to Within a transaction, the request-level timeout parameter is ignored.
The transaction timeout clock starts when the The value for this parameter is a string.
Its format includes an amount and a mandatory unit, e.g.
Specify a duration of The node-level In addition, the cluster-level The default is Example: |
String (duration) |
use_cbo |
Specifies whether the cost-based optimizer is enabled. The node-level In addition, the cluster-level Example: |
Boolean |
use_fts |
Specifies that the query should use a full-text index. If the query contains a If the query does not contain a Refer to Flex Indexes for more information. Default: |
Boolean |
use_replica |
Specifies whether a query can fetch data from a replica vBucket if active vBuckets are inaccessible. The possible values are:
The node-level In addition, the cluster-level Do not enable read from replica when you require consistent results. Only SELECT queries that are not within a transaction can read from replica. Reading from replica is only possible if the cluster uses Couchbase Server 7.6.0 or later. Note that KV range scans cannot currently be started on a replica vBucket. If a query uses sequential scan and a data node becomes unavailable, the query might return an error, even if read from replica is enabled for the request. Values: |
String |
additional |
Supplies the value for a named parameter in the statement. Applicable if the statement or prepared statement contains 1 or more named parameters. The name of this property consists of two parts:
The value of the named parameter can be any JSON value. Refer to Named Parameters and Positional Parameters for details. |
Any Type |
Response Body
Property | Schema | |
---|---|---|
requestID |
A unique identifier for the response. |
UUID (UUID) |
clientContextID |
The client context ID of the request, if one was supplied — see |
String |
signature |
The schema of the results. Present only when the query completes successfully. Example: |
Object |
results |
An array of all the objects returned by the query. An object can be any JSON value. |
Any Type array |
status |
The status of the request. Values: |
String |
errors |
An array of 0 or more error objects. If an error occurred during processing of the request, it will be represented by an error object in this list. |
Conditions array |
warnings |
An array of 0 or more warning objects. If a warning occurred during processing of the request, it is represented by a warning object in this list. |
Conditions array |
metrics |
An object containing metrics about the request. |
|
controls |
An object containing runtime information provided along with the request. Present only if |
|
profile |
An object containing monitoring and profiling information about the request. Present only if |
Statistics
Property | Schema | |
---|---|---|
#itemsIn |
Number of input documents to the operator. Example: |
Integer (int32) |
#itemsOut |
Number of output documents after the operator processing. Example: |
Integer (int32) |
#phaseSwitches |
Number of switches between executing, waiting for services, or waiting for the goroutine scheduler. Example: |
Integer (int32) |
execTime |
Time spent executing the operator code inside SQL++ query engine. Example: |
String (duration) |
kernTime |
Time spent waiting to be scheduled for CPU time. Example: |
String (duration) |
servTime |
Time spent waiting for another service, such as index or data. For index scan, it is time spent waiting for GSI/indexer. For fetch, it is time spent waiting on the KV store. Example: |
String (duration) |
The
|
Security
The Query Service API accepts credentials via HTTP basic authentication header, or via the creds
request parameter.
If a request contains both HTTP basic authentication header and a creds
parameter, the creds
parameter is ignored and only the HTTP basic authentication header is used for authenticating.
Header
Specify a user name and password via HTTP headers. This method can only be used to provide a single credential.
Type : http
Parameter
Specify user names and passwords via the creds
request parameter. This is the only method that can provide multiple credentials for a request.
Key parameter : creds
Key in : query
RBAC Role
Users must have the relevant Administrative or Query & Index RBAC roles, depending on the types of query they intend to run. In addition, users must have permissions on the required buckets, scopes, and collections, where appropriate. Refer to Roles for more details.
See Also
-
For cluster-level settings, see the Query Settings REST API.
-
For node-level settings, see the Admin REST API.