Query Workbench

Query Workbench

The Query Workbench provides a rich graphical user interface to perform query development.

Using the Query Workbench, you can conveniently explore data, create, edit, run, and save N1QL 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 N1QL keywords and bucket and field names, and easy cursor movement.
  • View the structure of the documents in a bucket by using the N1QL 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.
The Query Workbench is now integrated with the Couchbase Web console and is available on the Query tab.
Note: The Query Workbench only runs on nodes which are running the Query service. If the Query service is not running on the current node, it provides a link to the nodes in the cluster which are running the Query service.

Supported platforms

The Query Workbench is supported on Windows, Linux, and Mac OS X platforms.

The Query Workbench consists of three working areas as shown in the following figure :
  1. N1QL Editor
  2. Bucket Analysis
  3. Results
Figure 1. Query Workbench Areas

N1QL Editor

The N1QL editor is where you build queries, and run the queries using the Execute button.
Tip: You can also execute queries by typing a semi-colon (;) at the end of the query and hitting Enter.
. The N1QL editor provides the following additional features:
  • Auto-completion - When entering a keyword in the N1QL editor, if you enter the tab key or Ctrl+Space, the tool offers a list of matching N1QL keywords and bucket names that are close to what you have typed so far. For names that have a space or a hyphen (-), the auto-complete option includes back quotes around the name. If you expand a bucket in the Bucket Analyzer, the tool learns and includes the field names from the schema of the expanded bucket.
  • Query history - The tool maintains a history of all the queries executed. Use the arrow keys at the top of the editor to navigate through the history. 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. Use the Clear History button if you wish to start over. The query history only saves queries; due to limited browser storage it does not save query results. Thus, 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.
    Note: Clearing the browser history clears the history maintained by the N1QL Editor as well.
  • Support for N1QL INFER statements - In the Enterprise Edition, the tool supports the N1QL INFER statement.
  • Save query - You can save a query to an external text file using the Save Query button. By default, the query is saved as a text file (.txt) in the Downloads directory.
    Note: When using Safari, clicking Save loads the data into a new window. You have to save the file manually using the File > Save As menu.

Bucket Analysis

The bucket analysis area displays all the buckets installed in the cluster. The buckets are grouped into the following categories based on the indexes created for the bucket:
  • Fully Queryable Buckets: Contain a primary index or a primary index and secondary indexes.
  • Queryable on Indexed Fields: Do not contain a primary index, but have one or more secondary indexes.
  • Non-Indexed Buckets: Do not contain any indexes. These buckets do not support queries. You must first define an index before querying these buckets.
With the Enterprise Edition, you can expand the buckets to view the schema. Bucket analysis is based on the N1QL INFER statement, which you can run manually to get more detailed results. the cbq shell. This command infers a schema for a bucket by examining a random sample of documents. Because the command is based on a random sample, the results may vary slightly from run to run. The default sample size is 1000 documents. You can also hover the mouse pointer over a field name to see example values for that field. The syntax of the command is:
INFER bucket-name [ WITH options ]; 
where options is a JSON object, specifying values for one or more of sample_size, similarity_metric, num_sample_values, or dictionary_threshold.
Figure 2. Sample INFER statement: INFER `travel-sample`;

Results

When you execute a query, the results are displayed in the result area. Since large result sets can take a long time to display, we recommend using the LIMIT clause as part of your query when appropriate. The following figures display the result of the query select `beer-sample`.* from `beer-sample`; in different formats. You can choose to view the result in one of the following formats:
  • JSON (default), where the results are formatted 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.

  • Table, where the results are presented 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 tool tip.

  • Tree (or list), where the results are presented in a tree (or list or outline) format. Each sub-object or sub-array is displayed as a sub-list. You can hover the mouse pointer over a data value to see the path to that value in a tool tip.

Canceling Running Queries

You can cancel running queries using the Cancel button on the Query Workbench. When you cancel a running query, it stops the activity on the server side as well.

If you change to a different tab in the Couchbase Web Console when a query is running in the Query Workbench, the query will be interrupted on the client side. However, the query continues to run on the server. If you do need to change to a different tab when running a query, we recommend that you first terminate the running query using the Cancel button before changing the tab in order to avoid wasting server resources.

Note: The Cancel button does not cancel index creation statements. The index creation continues on the server side even though it appears to have been canceled from the Query Workbench.