INSERT

Use the INSERT statement to insert one or more new documents into an existing keyspace. Each INSERT statement requires a unique document key and a well-formed JSON document as values. In Couchbase, documents in a single bucket must have a unique key.

The INSERT statement can compute and return any expression based on the actual inserted documents.

Use the UPSERT statement if you want to overwrite a document with the same key, in case it already exists.

Example 1: The following statement inserts a single JSON document into the travel-sample bucket with key "k001". The returning clause specifies the function META().id to return the key of the inserted document (metadata), and the wildcard (*) to return the inserted document.

INSERT INTO `travel-sample` ( KEY, VALUE )
  VALUES
  (
    "k001",
    { "id": "01", "type": "airline"}
  )
RETURNING META().id as docid, *;

This results in:

{
  "requestID": "06c5acc1-69d3-4aad-9c11-b90a9bc895d8",
  "signature": {
    "*": "*",
    "id": "json"
  },
  "results": [
    {
      "docid": "k001",
      "travel-sample": {
        "id": "01",
        "type": "airline"
      }
    }
  ],
  "status": "success",
  "metrics": {
    "elapsedTime": "5.033416ms",
    "executionTime": "5.011203ms",
    "resultCount": 1,
    "resultSize": 151,
    "mutationCount": 1
  }
}

Prerequisites

The INSERT statement must include the following:

Name of the keyspace to insert the document.
Unique document key.
A well-formed JSON document specified as key-value pairs, or the projection of a SELECT statement which generates a well-formed single JSON to insert. See and for details.
Optionally, you can specify the values or an expression to be returned after the INSERT statement completes successfully.

Install the travel-sample Bucket

Install the sample bucket travel-sample before proceeding to run the examples in this topic. See Sample Buckets for information on how to install the sample buckets and java-sdk::sample-application.adoc#datamodel for details about the travel-sample data model.

Security Requirements

You should have read-write permission to the bucket, to be able to insert documents into a bucket. Any user who has the bucket credentials or any Couchbase administrator should be able to insert documents into a bucket. This includes the bucket administrator for the specified bucket, the cluster administrator, and the full administrator roles. See Roles for details about access privileges for various administrators.

You cannot insert documents into a SASL bucket if you have a read-only role for the SASL bucket.

RBAC Privileges

User executing the INSERT statement must have the Query Insert privilege on the target keyspace/bucket.

If the statement has any SELECT or RETURNING data-read clauses, then the Query Select privilege is also required on the keyspaces referred in the respective clauses. For more details about roles and privileges, see Authorization.

For example,

To execute the following statement, user must have the Query Insert privilege on `travel-sample`.

INSERT INTO `travel-sample` (KEY, VALUE)
VALUES ("key1", { "type" : "hotel", "name" : "new hotel" })

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

INSERT INTO `travel-sample` (KEY, VALUE)
VALUES ("key1", { "type" : "hotel", "name" : "new hotel" }) RETURNING *

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

INSERT INTO `travel-sample` (KEY foo, VALUE bar)
SELECT foo, bar FROM `beer-sample`

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

INSERT INTO `travel-sample` (KEY foo, VALUE bar)
SELECT "foo" || meta().id, bar FROM `travel-sample` WHERE type = "hotel"

Syntax

insert::= INSERT INTO keyspace-ref ( insert-values | insert-select ) [ returning-clause ]

Figure 1. Railroad Diagram: insert

keyspace-ref::= [ namespace : ] keyspace [ [ AS ] alias ]

Figure 2. Railroad Diagram: keyspace-ref

namespace::= identifier

Figure 3. Railroad Diagram: namespace

keyspace::= identifier

Figure 4. Railroad Diagram: keyspace

insert-values::= [ "(" [ PRIMARY ] KEY , VALUE ")" ] values-clause

Figure 5. Railroad Diagram: insert-values

values-clause::= VALUES "(" expr , expr ")" [, [VALUES] "(" expr , expr ")" ]*

Figure 6. Railroad Diagram: values-clause

insert-select::= "(" [ PRIMARY ] KEY expr [ , VALUE expr ] ")" select

Figure 7. Railroad Diagram: insert-select

returning-clause::= RETURNING result-expr [ , result-expr ]* ] | ( RAW | ELEMENT | VALUE ) expr

Figure 8. Railroad Diagram: returning-clause

result-expr::= ( [ path.]"*" | expr [ [ AS ] alias ] )

Figure 9. Railroad Diagram: result-expr

path::= identifier ( ("[" expr "]" )[.path] ) | ( [ ("[" expr "]" )].path )

Figure 10. Railroad Diagram: path

alias::= identifier

Figure 11. Railroad Diagram: alias

Figure 12. Railroad Diagram: expr

identifier::= unescaped-identifier | escaped-identifier

Figure 13. Railroad Diagram: identifier

unescaped-identifier::= [a-zA-Z_] ( [0-9a-zA-Z_$] )*

Figure 14. Railroad Diagram: unescaped-identifier

escaped-identifier::= `JSON-string`

Figure 15. Railroad Diagram: escaped-identifier

Semantics/Description

keyspace-ref

Specifies the keyspace into which the documents are inserted.

namespace: Namespace of the keyspace. Currently only default namespace is available.
keyspace: Specifies the keyspace name into which the document(s) are inserted. Ensure that the keyspace exists before trying to insert a document. For more information on keyspaces, see Keyspaces.
alias: Specifies an alias name for the keyspace, which can be used in rest of the query for brevity, or to differentiate multiple references to the same keyspace. For more information on aliases, see Aliases.

Here are some examples of keyspace, namespace, and an alias:

Keyspace - `travel-sample`
Namespace and keyspace - default:`travel-sample`
Keyspace with alias - default:`travel-sample` AS t

insert-values

Specifies one or more documents to be inserted using the keyspace (PRIMARY KEY, VALUE) clause. Each document requires a unique key and the values must be specified as a well-formed JSON document.

The document key should be unique within the Couchbase bucket. It can be a string or an expression that produces a string.

Example 2: You can specify a key using the following expression: "airline" || TOSTRING(1234) as shown here:

INSERT INTO `travel-sample` ( KEY, VALUE )
                    VALUES ( "airline" || TOSTRING(1234),
                    { "callsign": "" } )
                    RETURNING META().id;

The keyword PRIMARY is optional. There is no syntactic difference between PRIMARY KEY and KEY.

The KEY cannot be MISSING or NULL, and it cannot duplicate an existing key.

Example 3: If you don’t require the document key to be in a specific format, you can use the function UUID() to generate a unique key as shown here:

INSERT INTO `travel-sample` ( KEY, VALUE )
            VALUES ( UUID(),
                    { "callsign": "" } )
RETURNING META().id;

Since the document key is auto-generated, you can find the value of the key by specifying META().id in the returning clause.

values-clause

Specify the values as a well-formed JSON. (See http://json.org/example.html for examples of a well-formed JSON. )

You can insert NULL or empty or MISSING values.

Example 4: Insert an empty value.

Query

INSERT INTO `travel-sample` (KEY, VALUE)
    VALUES ( "airline::432",
              { "callsign": "",
                "country" : "USA",
                "type" : "airline"} )
RETURNING META().id as docid;

Results

{
    "requestID": "9100f45b-0489-4b91-8b8a-110d525683e0",
    "signature": {
        "id": "json"
    },
    "results": [
        {
            "docid": "airline::432"
        }
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "1.384451ms",
        "executionTime": "1.36097ms",
        "resultCount": 1,
        "resultSize": 44,
        "mutationCount": 1
    }
    }

Example 5: Insert a NULL value.

Query

INSERT INTO `travel-sample` (KEY, VALUE)
    VALUES ( "airline::1432",
            { "callsign": NULL,
              "country" : "USA",
              "type" : "airline"} )
RETURNING *;

Results

[
  {
    "travel-sample": {
      "callsign": null,
      "country": "USA",
      "type": "airline"
    }
  }
]

Example 6: Insert a MISSING value.

Query

INSERT INTO `travel-sample` (KEY, VALUE)
    VALUES ( "airline::142",
            { "callsign": MISSING,
              "country" : "USA",
              "type" : "airline"} )
RETURNING *;

Results

[
  {
    "travel-sample": {
      "country": "USA",
      "type": "airline"
    }
  }
  ]

Example 7: Insert a NULL JSON document.

INSERT INTO `travel-sample` (KEY, VALUE)
    VALUES ( "1021",
              { } )
              RETURNING *;

For more examples illustrating the variations of the values-clause, see Examples.

insert-select

Use the projection of a SELECT statement which generates a well-formed JSON to insert.

If the project of a SELECT statement generates multiple JSON documents, then your INSERT statement must handle the generation of unique keys for each of the documents.

Example 8: Query the travel-sample bucket for documents of type "airport" and airportname "Heathrow", and then insert the projection (2 documents) into the travel-sample bucket using unique keys generated using UUID().

Query

INSERT INTO `travel-sample` (KEY UUID(), VALUE _country)
    SELECT _country FROM `travel-sample` _country
      WHERE type = "airport" AND airportname = "Heathrow"
RETURNING *;

Results

{
  "results": [],
  "metrics": {
    "elapsedTime": "24.777636ms",
    "executionTime": "24.739281ms",
    "resultCount": 0,
    "resultSize": 0,
    "mutationCount": 2
  }
}

See Example 15 to use the INSERT statement to copy one bucket’s data to another bucket.

select: SELECT statements let you retrieve data from specified keyspaces. For details, see SELECT Syntax.

returning-clause

Specifies the fields that must be returned as part of the results object. Use * to return all the fields in all the documents that were inserted.

result-expr

Specifies an expression on the inserted documents, that will returned as output.

Example 9: Return the document ID and country.

Query

INSERT INTO `travel-sample` (KEY, VALUE)
    VALUES ( "airline_24444",
            { "callsign": "USA-AIR",
              "country" : "USA",
              "type" : "airline"})
RETURNING META().id as docid, country;

Results

[
  {
    "country": "USA",
    "docid": "airline_24444"
  }
]

Example 10: Use the UUID() function to generate the key and show the usage of the RETURNING clause to retrieve the generated document key and the last element of the callsign array with an expression.

Query

INSERT INTO `travel-sample` (KEY, VALUE)
    VALUES ( UUID(),
            { "callsign": [ "USA-AIR", "America-AIR" ],
              "country" : "USA",
              "type" : "airline"} )
RETURNING META().id as docid, callsign[ARRAY_LENGTH(callsign)-1];

Results

[
  {
    "$1": "America-AIR",
    "docid": "2bdfd7d1-a5ca-475b-827c-3b18af8f4f62"
  }
]

The INSERT statement returns the requestID, the signature, results including the keyspace and JSON document inserted, status of the query, and metrics.

requestID: Request ID of the statement generated by the server.
signature: Signature of the fields specified in the returning clause.
results: If the query specified the returning clause, then results contains one or more fields as specified in the returning clause. If not, returns an empty results array.
errors: Returns the error codes and messages if the statement fails with errors. Returned only when the statement fails with errors. Errors can also include timeouts.
status: Status of the statement - "successful" or "errors".
metrics: Provides metrics for the statement such as elapsedTime, executionTime, resultCount, resultSize, and mutationCount. For more information, see Metrics.

path

Paths support nested data. They use the dot notation syntax to identify the logical location of an attribute within a document. Paths provide a method to find data in document structures without having to retrieve the entire document or handle it within an application. For more information, see Paths.

expr

N1QL expressions can be an identifier, a literal value, operators, function calls, or queries.

identifier

An identifier uniquely identifies an object in a query and can be used to refer to a value in the current context of a query. Identifiers are case sensitive and can be used for bucket names, field names within documents, and array indexes. For more information, see Identifiers.

Examples

Inserting a Single Document

The simplest use case of an INSERT statement is to insert a single document into the keyspace.

Example 11: Insert a new document with key "1025" and type "airline" into the travel-sample bucket.

Query

INSERT INTO `travel-sample` (KEY,VALUE)
  VALUES ( "1025",
            {     "callsign": "MY-AIR",
                  "country": "United States",
                  "iata": "Z1",
                  "icao": "AQZ",
                  "id": "1011",
                  "name": "80-My Air",
                  "type": "airline"
            } )
RETURNING *;

Results

{
    "requestID": "d735943c-4031-49a6-9320-c1c3daeb09a1",
    "signature": {
        "*": "*"
    },
    "results": [
        {
            "travel-sample": {
                "callsign": "MY-AIR",
                "country": "United States",
                "iata": "Z1",
                "icao": "AQZ",
                "id": "1011",
                "name": "80-My Air",
                "type": "airline"
            }
        }
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "3.473989ms",
        "executionTime": "3.194353ms",
        "resultCount": 1,
        "resultSize": 300,
        "mutationCount": 1
    }
}

Performing Bulk Inserts

Use the INSERT statement to batch insert multiple documents using the following syntax:

INSERT INTO keyspace (KEY, VALUE)
  VALUES ( "1", { "value": "one" } ),
  VALUES ( "2", { "value": "two" } ),
  …,
  VALUES ( "n", { "value": "n" } );

Example 12: Insert two documents with key "airline_4444" and "airline_4445" of type "airline" into the travel-sample bucket:

Query

INSERT INTO `travel-sample` (KEY,VALUE)
VALUES ( "airline_4444",
    { "callsign": "MY-AIR",
      "country": "United States",
      "iata": "Z1",
      "icao": "AQZ",
      "name": "80-My Air",
      "id": "4444",
      "type": "airline"} ),
VALUES ( "4445", { "callsign": "AIR-X",
      "country": "United States",
      "iata": "X1",
      "icao": "ARX",
      "name": "10-AirX",
      "id": "4445",
      "type": "airline"} )
RETURNING *;

Results

{
    "requestID": "1068fcc9-f133-475c-90e9-6b32eb5b5f10",
    "signature": {
        "*": "*"
    },
    "results": [
        {
            "travel-sample": {
                "callsign": "MY-AIR",
                "country": "United States",
                "iata": "Z1",
                "icao": "AQZ",
                "id": "4444",
                "name": "80-My Air",
                "type": "airline"
            }
        },
        {
            "travel-sample": {
                "callsign": "MY-AIR",
                "country": "United States",
                "iata": "Z1",
                "icao": "AQZ",
                "id": "4445",
                "name": "80-My Air",
                "type": "airline"
            }
        }
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "3.125132ms",
        "executionTime": "3.086968ms",
        "resultCount": 2,
        "resultSize": 600,
        "mutationCount": 2
    }
}

Inserting Values using SELECT

Instead of providing actual values, you can specify the data to be inserted using the SELECT statement which selects the data from an existing bucket.

Example 13: Query the travel-sample bucket for the field _country where the airportname is "Heathrow" and type is "airport". Then insert the result of the select statement (a well-formed JSON document) into the travel-sample bucket with a key generated using the UUID() function.

Query

INSERT INTO `travel-sample` (key UUID(), value _country)
    SELECT _country FROM `travel-sample` _country
      WHERE type = "airport" AND airportname = "Heathrow";

Results

{
  "results": [],
  "metrics": {
    "elapsedTime": "10.616228ms",
    "executionTime": "10.576012ms",
    "resultCount": 0,
    "resultSize": 0,
    "mutationCount": 1
  }
}

Inserting Values Using a Combination Key, Generated Using the Project and Functions/Operators

Example 14: Generate a document key as a combination of the projection and some function, such as <countryname>::<system-clock>. The SELECT statement retrieves the country name "k1" and concatenates it with a delimiter "::" and the system clock function using the string concat operator "||".

Query

INSERT INTO `travel-sample` (KEY k1||"::"||clock_str(), value t)
    SELECT DISTINCT t.country AS k1,t
      FROM `travel-sample` t
      WHERE type = "airport" LIMIT 5
RETURNING META().id as docid, *;

The result shows the META().id generated as a result of this concatenation (highlighted below).

Results

[
  {
    "docid": "United States::2016-08-17T13:43:59.888-07:00",
    "travel-sample": {
      "airportname": "Bend Municipal Airport",
      "city": "Bend",
      "country": "United States",
      "faa": null,
      "geo": {
        "alt": 3460,
        "lat": 44.0945556,
        "lon": -121.2002222
      },
      "icao": "KBDN",
      "id": 8133,
      "type": "airport",
      "tz": "America/Los_Angeles"
    }
  },
  {
    "docid": "France::2016-08-17T13:43:59.888-07:00",
    "travel-sample": {
      "airportname": "Poulmic",
      "city": "Lanvedoc",
      "country": "France",
      "faa": null,
      "geo": {
        "alt": 287,
        "lat": 48.281703,
        "lon": -4.445017
      },
      "icao": "LFRL",
      "id": 1413,
      "type": "airport",
      "tz": "Europe/Paris"
    }
  }
]

Using Insert to Copy Bucket Data to Another Bucket

Example 15: Use the INSERT statement to create a copy of bucket_1 under the new name bucket_2:

INSERT INTO bucket_2(key _k, value _v)
    SELECT META().id _k, _v
      FROM bucket_1 _v;

Inserting Values Using Subqueries

Sub-queries can be used with INSERT in the insert-select form of the statement. The SELECT part can be any sophisticated query in itself.

Example 16: Insert a new type in documents from all hotels in the cities that have landmarks.

The inner most SELECT finds all cities that have landmarks.
The outer SELECT finds the hotels that are in the cities selected by the inner query in Step 1. It also adds a new type attribute with the value "landmark_hotels" to the projected result. For brevity, we SELECT only 4 documents.
Finally, the INSERT statement inserts the result of Step 2 with UUID() generated keys.

Query

INSERT INTO `travel-sample` (KEY UUID())
    SELECT x.name, x.city, "landmark_hotels" AS type
      FROM `travel-sample` x
      WHERE x.type = "hotel" and x.city WITHIN
        ( SELECT DISTINCT t.city
            FROM `travel-sample` t
            WHERE t.type = "landmark" )
      LIMIT 4
RETURNING *;

+ .Results

[
  {
    "travel-sample": {
      "city": "Aberdeenshire",
      "name": "Castle Hotel",
      "type": "landmark_hotels"
    }
  },
  {
    "travel-sample": {
      "city": "Argyll and Bute",
      "name": "Loch Fyne Hotel",
      "type": "landmark_hotels"
    }
  },
  {
    "travel-sample": {
      "city": "Argyll and Bute",
      "name": "Inveraray Youth Hostel",
      "type": "landmark_hotels"
    }
  },
  {
    "travel-sample": {
      "city": "Argyll and Bute",
      "name": "Argyll Hotel",
      "type": "landmark_hotels"
    }
  }
]

Inserting Values Using Functions

Example 17: Use multiple functions during the INSERT:

UUID() function to generate unique key for the document being inserted.
The string concatenation operator || to join "airport_" and the UUID.
UPPER string function to insert only uppercase values of the FAA code.

Query

cbq> \set -$faa_code "blr" ;
cbq> INSERT INTO `travel-sample` (KEY, VALUE)
      VALUES ("airport_" || UUID(),
             { "type" : "airport",
               "tz" : "India Standard Time",
               "country" : "India",
               "faa" : UPPER($faa_code)} )
RETURNING *;

+ .Results

{
    "requestID": "ab03d366-b079-4c7e-b9e9-935b9797b59a",
    "signature": {
        "*": "*"
    },
    "results": [
        {
            "travel-sample": {
                "country": "India",
                "faa": "BLR",
                "type": "airport",
                "tz": "India Standard Time"
            }
        }
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "3.299189ms",
        "executionTime": "3.260071ms",
        "resultCount": 1,
        "resultSize": 201,
        "mutationCount": 1
    }
}

Inserting Values Using Prepared Statements

Example 18: Prepare an INSERT statement and show how to execute it by passing parameters with cbq and REST.

The Query Workbench currently cannot pass the parameters, and hence can’t run parametrized queries.

The INSERT statement has some of the attribute values preset while it takes the document key and airport faa_code as parameters.

Prepare the INSERT statement.

Query

cbq> PREPARE ins_india FROM
      INSERT INTO `travel-sample` (KEY, VALUE)
        VALUES ( $key,
                { "type" : "airport",
                  "tz" : "India Standard Time",
                  "country" : "India",
                  "faa" : $faa_code} )
RETURNING *;

Results

{
    "requestID": "29e9af37-61df-4c3b-a12f-1c145ff66ad3",
    "signature": "json",
    "results": [
        {
            "encoded_plan": "H4sIAAAJbogA/5ySQW/bPgzFv4rAfw9t4f9hO/rWQzYYG7IiTgsMSZASNtNqk2WNooOkgffZR8VI2nTZpSdJ5E9PfNDbAfmqraleBocecoAMPDakO+vj0vraopbaQIzSMuQ7+O/lACX96lSAFPldPVlXM6nI7BS66eSpZfucqD30LpV7dB2VlQ6ZwTrtoxZns4uftM3Mbg5V23nh7RxyM4cizT2HTLcrxFS70HWZnKaabAMNIFoOLcuAyvOr26YU9DVybaa2UbpfLKDPTme6RUbnyOlIDW4OJxsbyD8cvLzLbEm+LnwkFsXQWUxmhXFN7v+ITXDpujqPASs603K2sQK575wb/vMA1rTCzslfTgpvxaK75fYHVelRpqjcUoibuB+PNiGBkdxK21FQT8Id9Yu3Wp+sf1HSdiIgsF1bR4/p23aHMfLTufOP/VutUpiwgb1EtI8epWNKCtfavFYbILRRp6pPAZnMMbVmxW1jinE5mkx1mX4zDyevPZjLL6Pvmbm/+Xo3uhqW0lwe8zRkxJwNiflnShJxTKI5F0XzKov9lTGT0fRuMi7Gn43a+RMAAP//+P56E48DAAA=",
            "name": "ins_india",
            "operator": {
                "#operator": "Sequence",
                "~children": [
                    {
                        "#operator": "Authorize",
                        "child": {
                            "#operator": "Sequence",
                            "~children": [
                                {
                                    "#operator": "ValueScan",
                                    "values": "[[$key, {\"country\": \"India\", \"faa\": $faa_code, \"type\": \"airport\", \"tz\": \"India Standard Time\"}]]"
                                },
                                {
                                    "#operator": "Parallel",
                                    "maxParallelism": 1,
                                    "~child": {
                                        "#operator": "Sequence",
                                        "~children": [
                                            {
                                                "#operator": "SendInsert",
                                                "alias": "travel-sample",
                                                "keyspace": "travel-sample",
                                                "limit": null,
                                                "namespace": "default"
                                            },
                                            {
                                                "#operator": "InitialProject",
                                                "result_terms": [
                                                    {
                                                        "expr": "self",
                                                        "star": true
                                                    }
                                                ]
                                            },
                                            {
                                                "#operator": "FinalProject"
                                            }
                                        ]
                                    }
                                }
                            ]
                        },
                        "privileges": {
                            "default:travel-sample": 2
                        }
                    },
                    {
                        "#operator": "Stream"
                    }
                ]
            },
            "signature": {
                "*": "*"
            },
            "text": "prepare ins_india from INSERT INTO `travel-sample` (KEY, VALUE) VALUES ($key, {\"type\" : \"airport\", \"tz\" : \"India Standard Time\", \"country\" : \"India\", \"faa\" : $faa_code})  RETURNING *"
        }
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "2.726566ms",
        "executionTime": "2.682386ms",
        "resultCount": 1,
        "resultSize": 3461
    }
}

Execute the prepared statement using cbq. To execute using the REST API, skip to Step 3.

Open a cbq prompt and set the environment variables $key and $faa_code. These values will be passed as parameters when executing the prepared statement ins_india.
```
cbq> \set -$key "airport_10001" ;
cbq> \set -$faa_code "DEL" ;
```

Execute the prepared statement ins_india.

cbq> execute ins_india;
{
    "requestID": "b6c08546-95a4-4619-9b54-d93a6ac42aa6",
    "signature": null,
    "results": [
        {
            "default": {
                "country": "India",
                "faa": "DEL",
                "type": "airport",
                "tz": "India Standard Time"
            }
        }
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "2.128772ms",
        "executionTime": "2.08428ms",
        "resultCount": 1,
        "resultSize": 195,
        "mutationCount": 1
    }
}

Execute the prepared statement using REST API. To execute using the cbq shell, go to Step 2.

Insert another airport by passing $key and $faa_code as REST parameters.

$ curl -v http://localhost:8093/query/service -d 'prepared="ins_india"&$key="airport_10002"&$faa_code="BLR"'

> POST /query/service HTTP/1.1
> User-Agent: curl/7.37.1
> Host: localhost:8093
> Accept: */*
> Content-Length: 57
> Content-Type: application/x-www-form-urlencoded
>
* upload completely sent off: 57 out of 57 bytes
< HTTP/1.1 200 OK
< Content-Length: 542
< Content-Type: application/json; version=1.5.0
< Date: Wed, 10 Aug 2016 23:12:55 GMT
<
{
    "requestID": "adb2f0ec-8d55-490e-a4ce-2b09737c7b23",
    "signature": {
        "*": "*"
    },
    "results": [
        {
            "travel-sample": {
                "country": "India",
                "faa": "BLR",
                "type": "airport",
                "tz": "India Standard Time"
            }
        }
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "4.390041ms",
        "executionTime": "4.350285ms",
        "resultCount": 1,
        "resultSize": 201,
        "mutationCount": 1
    }
    }

The REST parameters should not have any spaces around ‘&’ when passing multiple parameters. For example, the following REST API throws an error because of spaces before the $faa_code parameter.

$ curl -v http://localhost:8093/query/service -d 'prepared="ins_india"&$key="airport_10002" & $faa_code="BLR"'
* Connected to localhost (::1) port 8093 (#0)
> POST /query/service HTTP/1.1
> User-Agent: curl/7.37.1
> Host: localhost:8093
> Accept: */*
> Content-Length: 59
> Content-Type: application/x-www-form-urlencoded
>
* upload completely sent off: 59 out of 59 bytes
< HTTP/1.1 200 OK
< Content-Length: 490
< Content-Type: application/json; version=1.5.0
< Date: Wed, 10 Aug 2016 23:12:41 GMT
<
{
    "requestID": "13fa81a2-84a6-4db4-b51f-474be4b86006",
    "signature": {
        "*": "*"
    },
    "results": [
    ],
    "errors": [
        {
            "code": 5010,
            "msg": "Error evaluating VALUES. - cause: No value for named parameter $faa_code."
        }
    ],
    "status": "errors",
    "metrics": {
        "elapsedTime": "328.022µs",
        "executionTime": "288.106µs",
        "resultCount": 0,
        "resultSize": 0,
        "errorCount": 1
    }
}
* Connection #0 to host localhost left intact

Restrictions

When inserting documents into a specified keyspace, keep in mind the following restrictions which would help avoid errors during execution.

The keyspace must exist. The INSERT statement returns an error if the keyspace does not exist.
Do not insert a document with a duplicate key. If you are inserting multiple documents, the statement aborts at the first error encountered.
Timeouts can affect the completion of an INSERT statement, especially when performing bulk inserts. Ensure that the timeout is set to a reasonable value that allows the bulk insert operation to complete.

To set the indexer timeout, use the following command:
```
curl <host>:9102/settings -u <username>:<password> -d  '{"indexer.settings.scan_timeout": <some integer>}'
```
For example,
```
$ curl localhost:9102/settings -u Administrator:password -d  '{"indexer.settings.scan_timeout": 1200}'
```
Use the following command to retrieve the indexer settings:
```
curl -X GET http://localhost:9102/settings -u Administrator:password
```
See Request Timeout for more information on timeouts.
When inserting multiple documents, no cleanup or rollback is done for the already inserted documents if the INSERT operations hits an error. This means, when you are inserting 10 documents, if the INSERT operation fails when inserting the 6th document, the operator quits and exits. It does not rollback the first five documents that were inserted. Nor does it ignore the failure and continue to insert the remaining documents.

Explain Plan

To understand how the INSERT statement is executed by N1QL, let us take a look at two examples. For detailed explanation about the EXPLAIN plan, see the EXPLAIN statement.

Simple INSERT Statement Using KEY VALUE Pairs to Insert Two Documents

Example 19: The query engine first scans the input values shown by the operator "ValueScan" to obtain the input values, and then it inserts the documents into the specified keyspace (shown by the operator SendInsert).

Query

EXPLAIN INSERT INTO `travel-sample` (KEY,VALUE)
VALUES ( "1025",
          { "callsign": "SKY-AIR",
            "country": "United States",
            "id": "1025",
            "type": "airline"
          } ),
VALUES ( "1026",
          { "callsign": "F1-AIR",
            "country": "United States",
            "id": "1014"
          } )
RETURNING *;

Results

{
    "requestID": "30d33a23-9635-439a-8676-7f95812aabcc",
    "signature": "json",
    "results": [
        {
            "plan": {
                "#operator": "Sequence",
                "~children": [
                    {
                        "#operator": "ValueScan",
                        "values": "[[\"1025\", {\"callsign\": \"SKY-AIR\", \"country\": \"United States\", \"id\": \"1025\", \"type\": \"airline\"}], [\"1026\", {\"callsign\": \"F1-AIR\", \"country\": \"United States\", \"id\": \"1014\"}]]"
                    },
                    {
                        "#operator": "Parallel",
                        "maxParallelism": 2,
                        "~child": {
                            "#operator": "Sequence",
                            "~children": [
                                {
                                    "#operator": "SendInsert",
                                    "alias": "travel-sample",
                                    "keyspace": "travel-sample",
                                    "limit": null,
                                    "namespace": "default"
                                },
                                {
                                    "#operator": "InitialProject",
                                    "result_terms": [
                                        {
                                            "expr": "self",
                                            "star": true
                                        }
                                    ]
                                },
                                {
                                    "#operator": "FinalProject"
                                }
                            ]
                        }
                    }
                ]
            },
            "text": "INSERT INTO `travel-sample` (KEY,VALUE) VALUES ( \"1025\", { \"callsign\": \"SKY-AIR\", \"country\": \"United States\", \"id\": \"1025\", \"type\": \"airline\"} ), VALUES ( \"1026\", { \"callsign\": \"F1-AIR\", \"country\": \"United States\", \"id\": \"1014\"} ) RETURNING *"
        }
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "3.26355ms",
        "executionTime": "3.237978ms",
        "resultCount": 1,
        "resultSize": 2027
    }
}

INSERT Statement Using the Projection of a Select Statement to Generate Values

Example 20: The Query Engine first executes the SELECT statement and then uses the projection to insert into the travel-sample bucket, performing the operations in the order listed:

An IndexScan to find the documents that satisfy type="airport".
A subsequent IndexScan for airportname="Heathrow".
An IntersectScan to obtain the documents that satisfy both conditions of Step 1 and Step 2.
A Fetch for the value on the field _country.
An Insert of the value along with the auto-generated key into the travel-sample bucket.

Query

EXPLAIN INSERT INTO `travel-sample` (key UUID(), value _country)
    SELECT _country FROM `travel-sample` _country
      WHERE type = "airport" AND airportname = "Heathrow";

Results

[
  {
    "plan": {
      "#operator": "Sequence",
      "~children": [
        {
          "#operator": "Sequence",
          "~children": [
            {
              "#operator": "IntersectScan",                     / Step 3
              "scans": [
                {
                  "#operator": "IndexScan",                     / Step 1
                  "index": "def_type",
                  "index_id": "aea49ebaf37e4015",
                  "keyspace": "travel-sample",
                  "namespace": "default",
                  "spans": [
                    {
                      "Range": {
                        "High": [
                          "\"airport\""
                        ],
                        "Inclusion": 3,
                        "Low": [
                          "\"airport\""
                        ]
                      }
                    }
                  ],
                  "using": "gsi"
                },
                {
                  "#operator": "IndexScan",                    / Step 2
                  "index": "def_airportname",
                  "index_id": "1e6e52dd512a354f",
                  "keyspace": "travel-sample",
                  "namespace": "default",
                  "spans": [
                    {
                      "Range": {
                        "High": [
                          "\"Heathrow\""
                        ],
                        "Inclusion": 3,
                        "Low": [
                          "\"Heathrow\""
                        ]
                      }
                    }
                  ],
                  "using": "gsi"
                }
              ]
            },
            {
              "#operator": "Parallel",
              "~child": {
                "#operator": "Sequence",
                "~children": [
                  {
                    "#operator": "Fetch",                    / Step 4
                    "as": "_country",
                    "keyspace": "travel-sample",
                    "namespace": "default"
                  },
                  {
                    "#operator": "Filter",
                    "condition": "(((`_country`.`type`) = \"airport\") and ((`_country`.`airportname`) = \"Heathrow\"))"
                  },
                  {
                    "#operator": "InitialProject",
                    "result_terms": [
                      {
                        "expr": "`_country`"
                      }
                    ]
                  },
                  {
                    "#operator": "FinalProject"
                  }
                ]
              }
            }
          ]
        },
        {
          "#operator": "Parallel",
          "~child": {
            "#operator": "Sequence",
            "~children": [
              {
                "#operator": "SendInsert",                   / Step 5
                "alias": "travel-sample",
                "key": "uuid()",
                "keyspace": "travel-sample",
                "limit": null,
                "namespace": "default",
                "value": "`_country`"
              },
              {
                "#operator": "Discard"
              }
            ]
          }
        }
      ]
    },
    "text": "INSERT INTO `travel-sample` (key UUID(), value _country) SELECT _country from `travel-sample` _country WHERE type = \"airport\" and airportname = \"Heathrow\";"
  }
]

Best Practices

When inserting multiple documents, configure pipeline-batch and max-parallelism query parameters for better performance. See the following section on Performance for details on how to set these parameters.

Performance

When a single INSERT statement is executed, N1QL prepares the statement, scans the values and then inserts the document. When inserting a large number of documents, you can improve the performance of the INSERT statement by using one of the following techniques:

Batching the documents to perform bulk inserts, which decreases the latency and increases the throughput. The INSERT statement sends documents to the data node in batches, with a default batch size of 16. You can configure this value by passing the pipeline-batch parameter as an argument to the cbq engine, or using the REST API parameter for query service. Note that the maximum batch size is (2^32 -1) and specifying a value higher than the maximum batch size may increase the memory consumption. The following example command sets the pipeline-batch size to 32 instead of the default 16:
```
curl -v -X POST http://127.0.0.1:8093/admin/settings  -d '{ "debug":true, "pipeline-batch": 32 }' -u Administrator:password
```
Use max-parallelism when inserting multiple documents.
When performing bulk inserts, use prepared statements or multiple values.
When new documents are inserted, the indexes are updated. When a large number of documents are inserted, this may affect the performance of the cluster.

Metrics

The INSERT statement returns the following metrics along with the results and status:

elapsedTime: Total elapsed time for the statement.
executionTime: Time taken by Couchbase Server to execute the statement. This value is independent of network latency, platform code execution time, and so on.
resultCount: Total number of results returned by the statement. In case of INSERT without a RETURNING clause, the value is 0.
resultSize: Total number of results that satisfy the query.
mutationCount: Specifies the number of documents that were inserted by the INSERT statement.

Monitoring

You can use the query monitoring API to gather diagnostic information. For example, if you are performing a bulk insert using a SELECT statement, you can use the query monitoring API to get the number of documents being inserted. Check system:active_requests catalog for more information on monitoring active queries. The REST API for bucket statistics is available at . CLI tools like cbstats provide detailed stats about the cluster. The same information can also be obtained using cbq shell or REST API. For more information, see Query Monitoring.

You can also take a look at the bucket metrics from the Web Console. To do so, go to the Data Buckets tab and click the bucket that you want to monitor. In the General Bucket Analytics screen, scroll to the Query section to gather information such as requests/sec, selects/sec and so on.