You are viewing the documentation for a prerelease version.

View Latest

USE Clause

The USE clause enables you to specify that the query should use particular keys, or a particular index.

Purpose

The USE clause is used within the FROM clause. It enables you to provide a hint to the query service, specifying that the query should use particular keys, or a particular index.

Prerequisites

For you to select data from a document or keyspace, you must have the query_select privilege on the document or keyspace. For more details about user roles, see Authorization.

Syntax

use-clause ::= use-keys-clause | use-index-clause
use clause

USE KEYS Clause

Purpose

You can refer to a document’s unique document key by using the USE KEYS clause. Only documents having those document keys will be included as inputs to a query.

Syntax

use-keys-clause ::= USE use-keys-term
use keys clause 
use-keys-term ::= [ PRIMARY ] KEYS expr
use keys predicate
PRIMARY KEYS

USE KEYS and USE PRIMARY KEYS are synonyms.

Arguments

expr

String of a document key or an array of comma-separated document keys.

Examples

Example 1. Select a single document by its document key
SELECT *
FROM `travel-sample`
USE KEYS "airport_1254";
Results
[
  {
    "travel-sample": {
      "airportname": "Calais Dunkerque",
      "city": "Calais",
      "country": "France",
      "faa": "CQF",
      "geo": {
        "alt": 12,
        "lat": 50.962097,
        "lon": 1.954764
      },
      "icao": "LFAC",
      "id": 1254,
      "type": "airport",
      "tz": "Europe/Paris"
    }
  }
]
Example 2. Select multiple documents by their document keys
SELECT *
FROM `travel-sample`
USE KEYS ["airport_1254","airport_1255"];
Results
[
  {
    "travel-sample": {
      "airportname": "Calais Dunkerque",
      "city": "Calais",
      "country": "France",
      "faa": "CQF",
      "geo": {
        "alt": 12,
        "lat": 50.962097,
        "lon": 1.954764
      },
      "icao": "LFAC",
      "id": 1254,
      "type": "airport",
      "tz": "Europe/Paris"
    }
  },
  {
    "travel-sample": {
      "airportname": "Peronne St Quentin",
      "city": "Peronne",
      "country": "France",
      "faa": null,
      "geo": {
        "alt": 295,
        "lat": 49.868547,
        "lon": 3.029578
      },
      "icao": "LFAG",
      "id": 1255,
      "type": "airport",
      "tz": "Europe/Paris"
    }
  }
]

USE INDEX clause

(Introduced in Couchbase Server 3.0)

Purpose

Use the USE INDEX clause to specify which index to use as part of the query execution. The query engine attempts to use the specified index if the index is applicable for the query.

Syntax

use-index-clause ::= USE use-index-term
use index clause 
use-index-term ::= INDEX '(' index-ref [ ',' index-ref ]* ')'
use index predicate 
index-ref ::= index-name [ index-using ]
index ref

Arguments

index-name

[Required] String or expression representing the index or indexes to be used for the query.

USING clause

index-using ::= USING ( VIEW | GSI | FTS )
index using

Specifies which index form to use.

USING VIEW

The legacy index form, which lives on the data node.

USING GSI

The newer and faster Global Secondary Index form, which lives on an index node and can possibly be separate from a data node.

USING FTS

A Full Text Search index, for use with queries containing Search Functions. For more information on Full Text Search indexes, refer to Creating Indexes.

This clause is optional; if omitted, the default is USING GSI.

Examples

Example 3. Use an existing index with GSI in a query

Create an index of airlines and destination airports, and then use it in a query for flights originating in San Francisco.

CREATE INDEX idx_destinations
ON `travel-sample` (airlineid, airline, destinationairport)
WHERE type="route";
SELECT airlineid, airline, sourceairport, destinationairport
FROM `travel-sample` USE INDEX (idx_destinations USING GSI)
WHERE sourceairport = "SFO";
Example 4. Use an existing index with VIEW in a query

The usage of VIEW is identical to that of GSI.

CREATE INDEX idx_destinations
ON `travel-sample` (airlineid, airline, destinationairport)
WHERE type="route";
SELECT airlineid, airline, sourceairport, destinationairport
FROM `travel-sample` USE INDEX (idx_destinations USING VIEW)
WHERE sourceairport = "SFO";