SQL++ Queries from the SDK

how-to

You can query for documents in Couchbase using the SQL++ query language, a language based on SQL, but designed for structured and flexible JSON documents. Querying can solve typical programming tasks such as finding a user profile by email address, facebook login, or user ID.

Our query service uses SQL++ (formerly N1QL), which will be fairly familiar to anyone who’s used any dialect of SQL. Further resources for learning about SQL++ are listed at the bottom of the page. Before you get started you may wish to checkout the SQL++ intro page, or just dive in with a query against our travel sample data set. In this case, the one thing that you need to know is that in order to make a Bucket queryable, it must have at least one index defined. You can define a _primary index on a bucket. When a primary index is defined you can issue non-covered queries on the bucket as well.

    std::string statement =
            "SELECT airportname, city, country FROM `" + bucket_name
                    + R"(` WHERE type="airport" AND city="New York")";

    lcb_CMDQUERY *cmd = nullptr;
    check(lcb_cmdquery_create(&cmd), "create QUERY command");
    check(lcb_cmdquery_statement(cmd, statement.c_str(), statement.size()),
            "assign statement for QUERY command");
    check(lcb_cmdquery_callback(cmd, query_callback), "assign callback for QUERY command");
    check(lcb_query(instance, &result, cmd), "schedule QUERY command");
    check(lcb_cmdquery_destroy(cmd), "destroy QUERY command");
    lcb_wait(instance, LCB_WAIT_DEFAULT);

Queries & Placeholders

Placeholders allow you to specify variable constraints for an otherwise constant query. There are two variants of placeholders: postional and named parameters. Positional parameters use an ordinal placeholder for substitution and named parameters use variables. Note that both parameters and options are optional.

    std::string statement =
            "SELECT airportname, city, country FROM `" + bucket_name
                    + R"(` WHERE type="airport" AND city=$1)";

    lcb_CMDQUERY *cmd = nullptr;
    check(lcb_cmdquery_create(&cmd), "create QUERY command");
    check(lcb_cmdquery_statement(cmd, statement.c_str(), statement.size()),
            "assign statement for QUERY command");
    std::string city_json = "\"" + city + "\""; // production code should use JSON encoding library
    check(lcb_cmdquery_positional_param(cmd, city_json.c_str(), city_json.size()),
            "add positional parameter for QUERY comand");
    // Enable using prepared (optimized) statements
    check(lcb_cmdquery_adhoc(cmd, false), "enable prepared statements for QUERY command");
    check(lcb_cmdquery_callback(cmd, query_callback), "assign callback for QUERY command");
    check(lcb_query(instance, &result, cmd), "schedule QUERY command");
    check(lcb_cmdquery_destroy(cmd), "destroy QUERY command");
    lcb_wait(instance, LCB_WAIT_DEFAULT);

The Query Result

The result for each query is JSON and as a result queries will function the same regardless whether they are executed using the cbq shell, an SDK, or using the REST API directly. Nevertheless, the result format recieved using an SDK may be different than that received using the cbq or the REST API.

Query Options

Table 1. Available Query options
Name	Description
`lcb_cmdquery_reset(command)`	Reset the structure so that it may be reused for a subsequent query.
`lcb_cmdquery_encoded_payload(command,payload,payload length)`	Get the JSON-encoded query payload.
`lcb_cmdquery_payload(command, query, query length)`	Sets the JSON-encodes query payload to be executed.
`lcb_cmdquery_statement(command, statement, statement length )`	Sets the actual statement to be executed.
`lcb_cmdquery_scope_name(command, scope name, scope length)`	Associate scope name with the query.
`lcb_cmdquery_named_param(command, argument name, name length, argument value, value length)`	Sets a named argument for the query.
`lcb_cmdquery_positional_param(command, argument value, argument length)`	Adds a positional argument for the query.
`lcb_cmdquery_readonly(command, readonly)`	Marks query as read-only ( set readonly value to non zero ).
`lcb_cmdquery_scan_cap(command, value)`	Sets maximum buffered channel size between the indexer client and the query service for index scans.
`lcb_cmdquery_flex_index(command, value)`	Tells the query engine to use a flex index (utilizing the search service).
`lcb_cmdquery_pipeline_cap(command, item number)`	Sets maximum number of items each execution operator can buffer between various operators.
`lcb_cmdquery_pipeline_batch(command, item number)`	Sets the number of items execution operators can batch for fetch from the KV.
`lcb_cmdquery_consistency(command, mode)`	Sets the consistency mode for the request.
`lcb_cmdquery_consistency_token_for_keyspace(command, keyspace, keyspace length, token)`	Indicate that the query should synchronize its internal snapshot to reflect the changes indicated by the given mutation token.
`lcb_cmdquery_option(command, option name, name length, option value, value length)`	Set a query option.

Examples

As well as the API docs, there are examples in the GitHub repo for:

Querying at Scope Level

It is possible to query off the Scope level with Couchbase Server 7.0, using the lcb_cmdquery_scope_name() method. It takes the statement as a required argument, and then allows additional options if needed.

Usage details for this and lcb_cmdquery_scope_qualifier() can be found in the API docs.

Additional Resources

The Query Service is not the only query option in Couchbase. Be sure to check that your use case fits your selection of query service.

The Server doc SQL++ intro introduces up a complete guide to the SQL++ language, including all of the latest additions.

The SQL++ interactive tutorial is a good introduction to the basics of SQL++ use.