PREPARE

The PREPARE statement prepares a query for repeated execution.

Purpose

Building plans for N1QL requests may be expensive, in particular where a cluster has many indexes. Sometimes planning may take more time than actually executing a request.

If you know that a statement text will be executed repeatedly, you can request the N1QL service to prepare the execution plan beforehand, and then request to execute the prepared plan as many times as needed, thereby avoiding the cost of repeated planning.

Syntax

prepare ::= PREPARE [ name ( FROM | AS ) ] statement
'PREPARE' ( name ( 'FROM' | 'AS' ) )? statement
name

[Optional] A name for the prepared statement. If you do not specify an name for the prepared statement, the query engine generates a UUID as a name.

statement

The full text of the N1QL statement to prepare.

Result

A JSON array is returned that contains the following properties:

name

The name of the prepared statement. This may be a UUID that was assigned automatically, or the name that you specified when the statement was created.

operator

The execution plan of the statement being prepared.

signature

The signature of the statement being prepared.

text

The full PREPARE statement text.

encoded_plan

The full prepared statement in encoded format. The encoded plan can be included in addition to the prepared parameter in a request to execute a prepared statement. If the name given by the prepared parameter is unrecognized, the query engine will decode and execute the prepared statement given by the encoded plan. The decoded prepared statement will be saved by the query engine so the next request that uses its name will succeed.

Usage

You can prepare a statement in three ways:

  • Using the PREPARE statement in the Query Workbench.

  • Using the PREPARE statement in the the cbq command line shell.

  • Using the Query REST API (/query/service endpoint).

Repeatedly preparing a statement with the same name will result in the statement being overwritten. A PREPARE statement that uses a name that was used for a different statement will result in a duplicate name error.

Prepared statements are stored in memory until you restart the Couchbase Server. After restarting the server, you must prepare the statements again before you can execute the prepared statements.

For information on how to use prepared statements with various SDKs, refer to Querying with N1QL and N1QL from the SDK.

Authorization

The user executing the PREPARE statement must have the RBAC privileges of the statement being prepared. For more details about user roles, see Authorization.

For example,

To execute the following statement, user must have the Query Select privilege on both keyspaces `travel-sample` and `beer-sample`.

PREPARE SELECT * FROM `travel-sample`
WHERE city = (SELECT RAW city FROM `beer-sample`)

To execute the following statement, user must have the Query Update and Query Select privileges on `travel-sample`.

PREPARE UPDATE `travel-sample`
SET city = "San Francisco" WHERE lower(city) = "sanfrancisco"
RETURNING *

Example

Query
PREPARE SELECT * FROM `travel-sample`
WHERE type = "route"
AND airline = "FL";
Result
{
  "encoded_plan": "H4sIAAAAAAAA/6STUW/aPhTFv0p0/i+lMn91wBTwtAfUgVap2xBU28NAcEkuwatxMtvpyqrss08OhQ66l21SXmKfXJ/zO/ED2CR5yum80GQgAYEVky8tX+bG21w7yAsBZVK+7xfqI1uncgPZFjC0YUi0OnG70231mkncazc7y95Fc9ldps24s1rF3e6SO702BPKCLfncQj7gv6cXTPhrySZhCPxI1kqnlg3k52NRv/Tr3KrvQVVYdac0Z+zCqGvlfC2/IZuxh0TKKyq1l97SHeumo02hw3cjq+4g42pW7U/6KytXgcQkIRNC1Vh2Z879tuD90lylkOhS3KIXL3sxLXvdVYsOu4XNv3Dia5APIdCG7HZ+y1tIb0uuBG556wpKAt/THAH7fu8xKwRcQcbVZvmeEr8bJGDJZFwvr1W2hsQUNi89T1GbSXR5qFPn3472q1k1EyidMhkkMqdQiWMUQ/bJGn9s9nTMiCxpzRr/1MtQac8WAkluUrVji7Ozs8WRpcX/i9DTohG9jg5RGxGZNPqNlJTVyuzVw+spGo1n9q+M8or0aNcpBCy7Uvu5Z7vZN1IEoWO9Ck15so89z54RVeZpUjWrwnMimXjLtEH9GzuVmfquBmLnkDhHJeD5PtyD0Xgw6o8H0WRwPbi8ic6j4fjDu+gk49R8ejsYD6IA5VckU9N//yZ6zH9I/wrVzwAAAP//7/sWYTEEAAA=",
  "featureControls": 0,
  "indexApiVersion": 3,
  "name": "[127.0.0.1:8091]24734829-c793-4b90-b8bd-74ff788be493",
  "operator": {
    "#operator": "Sequence",
    "~children": [
      {
        "#operator": "Authorize",
        "privileges": {
          "List": [
            {
              "Priv": 7,
              "Target": "default:travel-sample"
            }
          ]
        },
        "~child": {
          "#operator": "Sequence",
          "~children": [
            {
              "#operator": "IndexScan3",
              "index": "def_type",
              "index_id": "8a72a1597ab98f2a",
              "index_projection": {
                "primary_key": true
              },
              "keyspace": "travel-sample",
              "namespace": "default",
              "spans": [
                {
                  "exact": true,
                  "range": [
                    {
                      "high": "\"route\"",
                      "inclusion": 3,
                      "low": "\"route\""
                    }
                  ]
                }
              ],
              "using": "gsi"
            },
            {
              "#operator": "Fetch",
              "keyspace": "travel-sample",
              "namespace": "default"
            },
            {
              "#operator": "Parallel",
              "~child": {
                "#operator": "Sequence",
                "~children": [
                  {
                    "#operator": "Filter",
                    "condition": "(((`travel-sample`.`type`) = \"route\") and ((`travel-sample`.`airline`) = \"FL\"))"
                  },
                  {
                    "#operator": "InitialProject",
                    "result_terms": [
                      {
                        "expr": "self",
                        "star": true
                      }
                    ]
                  },
                  {
                    "#operator": "FinalProject"
                  }
                ]
              }
            }
          ]
        }
      },
      {
        "#operator": "Stream"
      }
    ]
  },
  "signature": {
    "*": "*"
  },
  "text": "PREPARE SELECT * FROM `travel-sample`\nWHERE type = \"route\"\nAND airline = \"FL\";"
}
  • For information on executing the prepared statement, refer to EXECUTE.