Please use the form below to provide your feedback. Because your feedback is valuable to us, the information you submit in this form is recorded in our issue tracking system (JIRA), which is publicly available. You can track the status of your feedback using the ticket number displayed in the dialog once you submit the form.

Query Workbench

      +
      The Query Workbench is a user interface tool that can perform query development.

      Using the Query Workbench, you can explore data, create, edit, run, and save SQL++ queries, view and save query results, and explore the document structures in a bucket—​all in a single window.

      Features of the Query Workbench include:

      • A single, integrated visual interface to perform query development and testing.

      • Easy viewing and editing of complex queries by providing features such as multi-line formatting, copy-and-paste, syntax coloring, auto-completion of SQL++ keywords and bucket and field names, and easy cursor movement.

      • View the structure of the documents in a bucket by using the SQL++ INFER command. You no longer have to select the documents at random and guess the structure of the document.

      • Display query results in multiple formats: JSON, table, and tree. You can also save the query results to a file on disk.

      Prerequisites

      The database you’re using must have the Query Service running.

      Accessing the Query Workbench

      Permissions Required

      To view the Query Workbench and run queries that read documents, you need the Database Data Reader project role. To run queries that read and modify documents, you need the Project Owner or Database Data / Reader role.

      To open the Query Workbench in a database running the Query Service:

      1. With the Projects tab in your organization open, click the project with the database you’re working with.

      2. With the Databases tab open, select your database.

      3. Go to Data Tools  Query.

      4. Click the Query tab.

      The Query Workbench consists of three working areas:

      Data / Bucket Insights

      The Data Insights area (left pane) displays the buckets, scopes, and collections in the current database. These keyspaces are in a hierarchy that you can expand or collapse.

      • To expand or collapse a heading within the hierarchy, click the heading, or click the arrow before the heading.

      Buckets appear at the top level of the hierarchy. When you expand a bucket, the scopes within that bucket appear below it. Similarly, when you expand a scope, the collections within that scope appear below it. The number of collections within the bucket is displayed to the right of the bucket heading.

      Using the Query Editor

      The Query Editor is where you build and run queries, and view query execution plans. Enter a query into the Query Editor, and then run the query by clicking Run Query.

      You can also execute queries by typing a semi-colon (;) at the end of the query and pressing Enter.

      The Query Editor provides the following additional features:

      • Syntax coloring: For easy viewing, SQL++ keywords, numbers, and string literals are differently colored.

      • Support for SQL++ INFER statements: Support the SQL++ INFER statement.

      Specify a Bucket and Scope Context

      You can specify the bucket and scope query context. When the query context is set to a specified bucket and scope, you can write queries using just the collection name and don’t need to specify the keyspace path.

      For example, here’s how a query would look without specifying a bucket and scope in the Query Editor:

      SELECT * FROM `travel-sample`.`inventory`.`airline`;

      If you then specify the bucket as travel-sample and the scope as inventory, the query can be simplified to:

      SELECT * FROM `airline`;

      To specify the query context:

      1. In the Data Insights area (left pane), hover your cursor over the bucket or scope and click query from.

      To remove the query context, hover your cursor over the bucket or scope and click unselect.

      For more information about scopes and collections, refer to Scopes and Collections.

      Run a Query

      After entering a query, you can run it by typing a semicolon (;) and pressing Enter or clicking Run Query. When the query is running, the Run Query button changes to Cancel, which allows you to cancel the running query. When you cancel a running query, it stops the activity on the database side as well.

      The Cancel button doesn’t cancel index creation statements. The index creation continues on the server side even though it appears to have been canceled from the Query Workbench.

      Run a Transaction

      You can run a Couchbase transaction using the Query Editor. A transaction is a group of operations that are either committed to the database together or are all undone from the database if there’s a failure. For more information about Couchbase transactions, see Transactions.

      You can use the Query Editor to run a single statement as a transaction. To do this, enter the statement into the Query Editor and click Transaction.

      Index Advice

      The ADVISE command generates index advice that optimizes response time for a query. You can use this command with SELECT, MERGE, UPDATE, or DELETE queries. To display index advice for a query entered into the Query Editor, click Index Advice.

      Query Workbench shows any suggested indexes in the Query Results area using JSON format. This won’t prepend ADVISE to the current query in the Query Editor

      Viewing the Query Results

      When you execute a query, the results are in the Query Results area. Since large result sets can take a long time to display, it’s recommended that you use the LIMIT clause as part of your query when appropriate.

      The figures in this section display the result of the following query:

      SELECT * FROM `travel-sample` WHERE type="airport" LIMIT 1;

      When a query finishes, metrics for that query appear between the Query Editor and the Query Results areas.

      The following query metrics are shown with the query results:

      • The status of the query. The values can be success, failed, or HTTP codes.

      • The time at which the query was last executed.

      • The time taken by the server to execute the query.

      • The number of documents returned.

      JSON Format

      The JSON tab formats the results to make the data easy to read. You can also expand and collapse objects and array values using the small arrow icons next to the line numbers.

      Chart Format

      You can click Chart to present the results as a chart, as long as your query returns a suitable data series.

      You can select the type of chart and the data options from the drop-down controls at the top left of the chart. You can select the type of chart and the data options from the lists at the top left of the chart. To select the type of chart, in the Chart Type list, select a format for the chart: X-Y, Connected Points, Line, Area, Bar, Grouped Bar, Pie, or Donut.

      X-Y, Connected Points, Line, and Area Charts

      1. In the X-axis column list, select the field for the x-axis of the chart.

      2. In the Y-axis column list, select the field for the y-axis of the chart.

      For X-Y charts only: in the Color list, select a color for the data points.

      Bar, Grouped Bar, Pie, and Donut Charts

      1. In the Label list, select the field to use to categorize the data.

      2. In the Value list, specify the data series to be rendered on the chart.

      Click the download icon to download the chart in SVG format.

      Table Format

      The Table tab presents the results in a tabular format. The tool converts the JSON documents to HTML tables and presents sub-objects or sub-arrays as sub-tables. This format works well for queries that return an array of objects, like select `beer-sample`.* from `beer-sample`;. You can hover the mouse pointer over a data value to see the path to that value in a tooltip. You can sort a column by clicking the column header.

      Plans

      Each time you execute a query, an EXPLAIN command is automatically run in the background to retrieve the query plan for that query.

      Plan

      The Plan tab presents the query in a graphical format.

      At the top, it shows a summary which also shows lists of the buckets, indexes, and fields used by the query.

      At the bottom is a data-flow diagram of query operators, with the initial scans at the right, and the final output on the left.

      Potentially expensive operators are highlighted.

      The data flow generally follows these steps:

      1. Scan

      2. Fetch

      3. Filter

      4. Projection (part 1)

      5. Order

      6. Projection (part 2)

      Projection is split into two parts (one before Order and one after Order), but Query Workbench shows only the first part.

      Click a unit of the plan to see more details about it.

      An example query:

      Unit name Information shown when clicked

      Order

      		 
      {'#operator':'Order':'sort_terms':
[{'expr':'(`travel-sample`.`name`)'}]}

      Project

      		 
      {'#operator':'InitialProject':'result_terms':
[{'expr':'self','star':true}]}

      Filter

      		 
      {'#operator':'Filter','condition':'(((`travel-sample`.`type`) = \'landmark\') and
((`travel-sample`.`city`) = \'San Francisco\'))'}

      Fetch

      		 
      {'#operator':'Fetch','keyspace':'travel-sample','namespace':'default'}

      IntersectScan

      (none)

      IndexScan2 (above)

      		 
      {'#operator':'IndexScan2','index':'def_city','index_id':'d51323973a9c8458','index_projection':
{'primary_key':true},'keyspace':'travel-sample','namespace':'default','spans':
[{'exact':true,'range':[{'high':'\San Francisco\'','inclusion':3,'low':'\'San Francisco\''}]}],'using':'gsi'}

      IndexScan2 (below)

      		 
      {'#operator':'IndexScan2','index':'def_city','index_id':'a11b1af8651888cf','index_projection':
{'primary_key':true},'keyspace':'travel-sample','namespace':'default','spans':
[{'exact':true,'range':[{'high':'\'landmark'\'','inclusion':3,'low':'\'landmark\''}]}],'using':'gsi'}

      In general, the preference of scan is

      1. Covering Index

      2. Index Scan

      3. Intersect Scan

      4. Union Scan, and finally

      5. Fetch

      View Query History

      The Query Workbench maintains a history of all the queries executed.

      If you edit a previous query and execute it, the new query is stored at the end of the history. The history is persistent across browser sessions. The query history saves queries; due to limited browser storage, it doesn’t save query results. When you restart the browser or reload the page, you can see your old queries, but you must re-execute the queries if you want to see their results.

      Clearing the browser history clears the history maintained by the Query Editor as well.

      Clicking History opens the Query History menu.

      You can scroll through the entire query history, and click an individual query to view that particular point in the history.

      • Search history: You can search the query history by entering text in the filter saved queries search box. All matching queries are displayed.

      • Delete a specific entry: Click the Trash icon next to a particular query to delete it from the history.

        This can be useful if you want a more manicured history when you export the history for future use.

      • Delete all entries: Click More Options (⋮) and select Erase History to delete the entire query history.

      Import Queries

      You can load a new query history into the Query Workbench from a JSON file. This can be the exported query history from a different database.

      Importing query history overwrites your current query history.

      1. From the database’s Data Tools  Query page, click History.

        This opens the Query History menu.

      2. Click More Options (⋮) and select Import History.

      3. From the Open file window choose a local .json file that you want to import.

      4. Click Open.

        The preexisting query history is overwritten with the query history of the imported file.

      Export Query History

      You can export the current query history to a JSON file, which you import into other databases.

      1. From the database’s Data Tools  Query page, click History.

        This opens the Query History menu.

      2. Click More Options (⋮) and select Export History.

        This opens the Export File window.

      3. Use the File Name field to specify a name for the exported file.

      4. Click Submit.

        The window closes and the file downloads to your computer.

      Modify Query Settings

      You can specify various settings for the Query Workbench by clicking Query Options near the top-left corner of the Query Editor. This opens the Query Options window.

      Configure the following settings and click Save Settings to save the configuration.

      Option Description

      Collect query timings

      Collects per-operator query timings during query executions and displays them in a query plan. This option is selected by default.

      Automatically infer bucket schemas

      Makes field names available for auto-completion when you launch Query Workbench. This can affect server performance.

      Automatically format queries before executing

      The query workbench automatically formats queries with line breaks and indentation before executing.

      Use Cost-Based Optimizer

      Specifies whether the cost-based optimizer is enabled.

      Don’t save query history

      Disables auto-saving query history to local storage in your browser. This is a consideration for shared machines. When selected, any query history will be lost when you leave or refresh the query workbench.

      Max Parallelism

      Specifies the maximum parallelism for the query. If you do not specify, the cbq-engine uses its default value. For more information, refer to the max_parallelism parameter.

      QueryTimeout

      The timeout parameter in seconds limiting the running time of a query. You can set this to a maximum of 1800 seconds

      Transaction Timeout

      Specifies the maximum time in seconds spent on a transaction before timing out.

      Scan Consistency

      This is a cbq-engine option. Select one of the following options:

      • Not Bounded

      • Request Plus

      • Statement Plus

      For more information, refer to Settings and Parameters in the Couchbase Server documentation.

      Positional Parameters

      For the prepared queries, this option allows you to specify values for $1, $2, and so on up to as many positional parameters as you have. Click the + button to add new positional parameters, and the - button to remove the parameters. The parameters are automatically labeled as "$1", "$2", and so on.

      Named Parameters

      For the prepared queries, this option allows you to specify any number of named parameters. Click the + button to add new named parameters, and the - button to remove the parameters. Named parameters must start with the dollar sign ($) for use in prepared queries. Otherwise, they are interpreted as parameters to the Query REST API.