INSERT

reference

Use the INSERT statement to insert one or more new documents into an existing keyspace. Each INSERT statement requires a unique document key and well-formed JSON as values. In Couchbase, documents in a single keyspace 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.

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 Travel App Data Model for details about the travel-sample data model.

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.

Security Requirements

You should have read-write permission to the keyspace, to be able to insert documents into a keyspace. Any user who has the keyspace credentials or any Couchbase administrator should be able to insert documents into a keyspace. This includes the keyspace administrator for the specified keyspace, 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

To execute the INSERT statement, you must have the Query Insert privilege on the target keyspace.

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.

Please note that the examples below will alter the data in your sample buckets. To restore your sample data, remove and reinstall the travel-sample bucket. Refer to Sample Buckets for details.

For example,

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

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

To execute the following statement, you must have the Query Insert and Query Select privileges on `travel-sample`.inventory.hotel.

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

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

INSERT INTO `travel-sample`.inventory.landmark (KEY foo, VALUE bar)
SELECT META(doc).id AS foo, doc AS bar
FROM `beer-sample` AS doc WHERE type = "brewery";

To execute the following statement, you must have the Query Insert and Query Select privileges on `travel-sample`.inventory.hotel.

INSERT INTO `travel-sample`.inventory.hotel (KEY foo, VALUE bar)
SELECT "copy_" || meta(doc).id AS foo, doc AS bar
FROM `travel-sample`.inventory.hotel AS doc;

Syntax

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

Insert Target

insert-target ::= keyspace-ref [ [ AS ] alias ]

The insert target is the keyspace into which the documents are inserted. Ensure that the keyspace exists before trying to insert a document.

Keyspace Reference

keyspace-ref ::= keyspace-path | keyspace-partial

keyspace-path ::= [ namespace ':' ] bucket [ '.' scope '.' collection ]

( namespace ':' )? bucket ( '.' scope '.' collection )?

keyspace-partial ::= collection

Keyspace reference for the insert target. For more details, refer to Keyspace Reference.

AS Alias

Assigns another name to the keyspace reference. For details, refer to AS Clause.

alias: String to assign an alias.

Assigning an alias to the keyspace reference is optional. If you assign an alias to the keyspace reference, the AS keyword may be omitted.

Insert Values

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

( '(' 'PRIMARY'? 'KEY' ',' 'VALUE' ( ',' 'OPTIONS' )? ')' )? values-clause

Specifies one or more documents to be inserted using the VALUES clause. Each document requires a unique key and the values must be specified as well-formed JSON.

The bracketed KEY and VALUE keywords are purely a visual mnemonic to indicate that you are setting the key and value for the inserted document. There is no syntactic requirement to include these keywords when using the Insert Values syntax. Also note that there is no syntactic difference between PRIMARY KEY and KEY.

Similarly, the OPTIONS keyword is purely a visual mnemonic to indicate that you are setting metadata for the inserted document. There is no syntactic requirement to include the OPTIONS keyword when setting metadata for the inserted document.

VALUES Clause

values-clause ::= VALUES "(" key "," value [ "," options ] ")"
        [ "," [ VALUES ] "(" key "," value [ "," options ] ")" ]*

'VALUES' '(' key ',' value ( ',' options )? ')' ( ',' 'VALUES'? '(' key ',' value ( ',' options )? ')' )*

key

A string, or an expression resolving to a string, representing the ID of the document to be inserted. The KEY cannot be MISSING or NULL, and must be unique within the Couchbase keyspace. It can be a string or an expression that produces a string.

value

A JSON object or value, or an expression resolving to a JSON object or value, representing the body of the document to be inserted. (See http://json.org/example.html for examples of well-formed JSON.) You can insert NULL, empty, or MISSING values.

options

[Optional] An object representing the metadata to be set for the inserted document. Only the expiration attribute has any effect; any other attributes are ignored.

expiration: An integer, or an expression resolving to an integer, representing the document expiration in seconds.

If the document expiration is not specified, it defaults to 0, meaning the document expiration is the same as the bucket expiration.

Example 1. Specify a key using an expression

You can specify a key using an expression, as shown here.

Query

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

Example 2. Generate a unique key

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.

Query

INSERT INTO `travel-sample`.inventory.airline ( 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.

Example 3. Insert an empty value

Query

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

Results

{
    "requestID": "018a7bc3-c3d2-42fb-a383-0d98d1609337",
    "signature": {
        "docid": "json"
    },
    "results": [
    {
        "docid": "airline::432"
    }
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "4.8844ms",
        "executionTime": "4.7142ms",
        "resultCount": 1,
        "resultSize": 39,
        "serviceLoad": 4,
        "mutationCount": 1
    }
}

Example 4. Insert a NULL value

Query

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

Results

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

Example 5. Insert a MISSING value

Query

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

Results

[
  {
    "airline": {
      "country": "USA",
      "type": "airline"
    }
  }
]

Example 6. Insert a NULL JSON document

Query

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

Example 7. Insert a document with expiration

Insert a document into the airline keyspace using an expiration of 5 days.

Query

INSERT INTO `travel-sample`.inventory.airline (KEY, VALUE, OPTIONS)
    VALUES ( "airline::ttl",
             { "callsign": "Temporary",
               "country" : "USA",
               "type" : "airline" },
             { "expiration": 5*24*60*60 } );

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

Insert Select

insert-select ::= "(" [ PRIMARY ] KEY key [ "," VALUE value ] [ "," OPTIONS options ] ")" select

'(' 'PRIMARY'? 'KEY' key ( ',' 'VALUE' value )? ( ',' 'OPTIONS' options )? ')' select

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

key

A string, or an expression resolving to a string, representing the ID of the document to be inserted. 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.

value

[Optional] An object, or an expression resolving to an object, representing the body of the document to be inserted. This may be an alias assigned by the SELECT statement. If the VALUE is omitted, the entire JSON document generated by the SELECT statement is inserted.

options

[Optional] An object representing the metadata to be set for the inserted document. Only the expiration attribute has any effect; any other attributes are ignored.

expiration: An integer, or an expression resolving to an integer, representing the document expiration in seconds.

If the document expiration is not specified, it defaults to 0, meaning the document expiration is the same as the bucket expiration.

SELECT Statement

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

Example 8. Insert with SELECT

Query the airport keyspace for documents with airportname "Heathrow", and then insert the projection (1 document) into the airport keyspace using a unique key generated using UUID().

Query

INSERT INTO `travel-sample`.inventory.airport (KEY UUID(), VALUE _airport)
    SELECT _airport FROM `travel-sample`.inventory.airport _airport
    WHERE airportname = "Heathrow"
RETURNING *;

Results

[
  {
    "airport": {
      "airportname": "Heathrow",
      "city": "London",
      "country": "United Kingdom",
      "faa": "LHR",
      "geo": {
        "alt": 83,
        "lat": 51.4775,
        "lon": -0.461389
      },
      "icao": "EGLL",
      "id": 507,
      "type": "airport",
      "tz": "Europe/London"
    }
  }
]

Example 9. Insert with SELECT and set expiration

Query the airport keyspace for documents with airportname "Heathrow", and then insert the projection into the airport keyspace using a unique key and an expiration of 2 hours.

Query

INSERT INTO `travel-sample`.inventory.airport (KEY UUID(), VALUE doc, OPTIONS {"expiration": 2*60*60})
    SELECT a AS doc FROM `travel-sample`.inventory.airport a
      WHERE airportname = "Heathrow";

Example 10. Insert with SELECT and preserve expiration

If you want to copy the expiration of an existing document to the inserted document, you can use a META().expiration expression in the SELECT statement, as shown here.

Query

INSERT INTO `travel-sample`.inventory.airport (KEY UUID(), VALUE doc, OPTIONS {"expiration": ttl})
    SELECT META(a).expiration AS ttl, a AS doc FROM `travel-sample`.inventory.airport a
      WHERE airportname = "Heathrow";

See Example 18 to use the INSERT statement to copy one keyspace’s data to another keyspace.

RETURNING Clause

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

'RETURNING' ( result-expr ( ',' result-expr )* | ( 'RAW' | 'ELEMENT' | 'VALUE' ) expr )

Specifies the fields that must be returned as part of the results object.

Result Expression

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

( path '.' )? '*' | expr ( 'AS'? alias )?

Specifies an expression on the inserted documents, that will be returned as output. Use * to return all the fields in all the documents that were inserted.

Example 11. Return the document ID and country

Query

INSERT INTO `travel-sample`.inventory.airline (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 12. Return the document ID and an expression

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`.inventory.airline (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": "6af57793-65d2-4cc3-beea-5d713c7f3c29"
  }
]

Result

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.

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. For more information, see Query Monitoring.

You can also take a look at the keyspace 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.

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 http://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
```
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.

Performance and Best Practices

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 using the pipeline_batch request-level parameter, or the pipeline-batch service-level setting. 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://localhost:8093/admin/settings -u Administrator:password \
-d '{ "debug":true, "pipeline-batch": 32 }'
```
Use the max_parallelism request-level parameter, or the max-parallelism service-level setting 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.

Examples

Example 13. Overview

The following statement inserts a single JSON document into the airline keyspace 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.

Query

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

Results

{
  "requestID": "df5846b1-1044-4b1f-ae8a-979be25282d1",
  "signature": {
      "*": "*",
      "docid": "json"
  },
  "results": [
  {
      "airline": {
          "id": "01",
          "type": "airline"
      },
      "docid": "k001"
  }
  ],
  "status": "success",
  "metrics": {
      "elapsedTime": "6.916ms",
      "executionTime": "6.6224ms",
      "resultCount": 1,
      "resultSize": 117,
      "serviceLoad": 4,
      "mutationCount": 1
  }
}

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

Example 14. Inserting a Single Document

Insert a new document with key "1025" into the airline keyspace.

Query

INSERT INTO `travel-sample`.inventory.airline (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": "c3bd0276-5d7d-425f-98f9-b333b9ae4302",
  "signature": {
      "*": "*"
  },
  "results": [
  {
      "airline": {
          "callsign": "MY-AIR",
          "country": "United States",
          "iata": "Z1",
          "icao": "AQZ",
          "id": "1011",
          "name": "80-My Air",
          "type": "airline"
      }
  }
  ],
  "status": "success",
  "metrics": {
      "elapsedTime": "5.9133ms",
      "executionTime": "5.6264ms",
      "resultCount": 1,
      "resultSize": 254,
      "serviceLoad": 4,
      "mutationCount": 1
  }
}

You can batch insert multiple documents using multiple VALUES clauses. The VALUES keyword itself is optional in the second and later iterations of the clause.

Example 15. Performing Bulk Inserts

Insert two documents with key "airline_4444" and "airline_4445" into the airline keyspace:

Query

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

Results

{
  "requestID": "2fabc03a-ea9b-49fd-a044-6ef667381311",
  "signature": {
      "*": "*"
  },
  "results": [
  {
      "airline": {
          "callsign": "MY-AIR",
          "country": "United States",
          "iata": "Z1",
          "icao": "AQZ",
          "id": "4444",
          "name": "80-My Air",
          "type": "airline"
      }
  },
  {
      "airline": {
          "callsign": "AIR-X",
          "country": "United States",
          "iata": "X1",
          "icao": "ARX",
          "id": "4445",
          "name": "10-AirX",
          "type": "airline"
      }
  }
  ],
  "status": "success",
  "metrics": {
      "elapsedTime": "5.7617ms",
      "executionTime": "5.4635ms",
      "resultCount": 2,
      "resultSize": 505,
      "serviceLoad": 4,
      "mutationCount": 2
  }
}

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

Example 16. Inserting Values using SELECT

Query the airport keyspace for documents where the airportname is "Heathrow". Then insert the result of the select statement (a well-formed JSON document) into the airport keyspace with a key generated using the UUID() function.

Query

INSERT INTO `travel-sample`.inventory.airport (KEY UUID(), VALUE airport)
    SELECT airport FROM `travel-sample`.inventory.airport
      WHERE airportname = "Heathrow";

Results

{
    "requestID": "91dd03d8-411c-463d-b58f-474836b0a643",
    "signature": null,
    "results": [
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "51.5194ms",
        "executionTime": "44.56ms",
        "resultCount": 0,
        "resultSize": 0,
        "serviceLoad": 4,
        "mutationCount": 1
    }
}

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

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`.inventory.airport (KEY k1||"::"||clock_str(), value t)
    SELECT DISTINCT t.country AS k1,t
      FROM `travel-sample`.inventory.airport t
      LIMIT 5
RETURNING META().id as docid, *;

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

Results

[
  {
    "airport": {
      "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"
    },
    "docid": "France::2021-02-09T13:53:28.445Z"
  }
]

Example 18. Using Insert to Copy Keyspace Data to Another Keyspace

Use the INSERT statement to create a copy of keyspace_1 under the new name keyspace_2.

Query

INSERT INTO keyspace_2(key _k, value _v)
    SELECT META().id _k, _v
      FROM keyspace_1 _v;

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 19. Inserting Values Using Subqueries

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

Query

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

1	The inner most `SELECT` finds all cities that have landmarks.
2	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.
3	Finally, the `INSERT` statement inserts the result of Step 2 with `UUID()` generated keys.

Results

[
  {
    "hotel": {
      "city": "Aberdeenshire",
      "name": "Castle Hotel",
      "type": "landmark_hotels"
    }
  },
  {
    "hotel": {
      "city": "Aberdeenshire",
      "name": "Two Bears Cottage",
      "type": "landmark_hotels"
    }
  },
  {
    "hotel": {
      "city": "Agoura Hills",
      "name": "Malibu Creek Campground",
      "type": "landmark_hotels"
    }
  },
  {
    "hotel": {
      "city": "Altrincham",
      "name": "Cresta Court Hotel",
      "type": "landmark_hotels"
    }
  }
]

Example 20. Inserting Values Using Functions

Set the parameter $faa_code using the cbq prompt, or the Run-Time Preferences in the Query Workbench.

Parameters

cbq> \set -$faa_code "blr" ;

Query

INSERT INTO `travel-sample`.inventory.airport (KEY, VALUE)
      VALUES ("airport_" || UUID(), (1)(2)
             { "type" : "airport",
               "tz" : "India Standard Time",
               "country" : "India",
               "faa" : UPPER($faa_code)} ) (3)
RETURNING *;

The query uses multiple functions during the INSERT:

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

Results

{
    "requestID": "4fea5296-c9f4-4fd3-be78-95e5a04531eb",
    "signature": {
        "*": "*"
    },
    "results": [
    {
        "airport": {
            "country": "India",
            "faa": "BLR",
            "type": "airport",
            "tz": "India Standard Time"
        }
    }
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "7.7853ms",
        "executionTime": "7.6472ms",
        "resultCount": 1,
        "resultSize": 167,
        "serviceLoad": 4,
        "mutationCount": 1
    }
}

Example 21. Inserting Values Using Prepared Statements

Prepare an INSERT statement and execute it by passing parameters. 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

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

Execute the prepared statement using the cbq shell or the Query Workbench. To execute using the REST API, skip to Step 3.
1. Set the parameters $key and $faa_code using the cbq prompt, or the Run-Time Preferences in the Query Workbench. These values will be passed as parameters when executing the prepared statement ins_india.
  Parameters
  cbq> \set -$key "airport_10001" ; cbq> \set -$faa_code "DEL" ;
2. Execute the prepared statement ins_india.
  Query
  EXECUTE ins_india;
  Results
  [ { "airport": { "country": "India", "faa": "DEL", "type": "airport", "tz": "India Standard Time" } } ]

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

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

Query

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

Results

{
   "requestID":"55ff7e8a-7410-470f-ab83-c464f9d0092d",
   "signature":{
      "*":"*"
   },
   "results":[
      {
         "airport":{
            "country":"India",
            "faa":"BLR",
            "type":"airport",
            "tz":"India Standard Time"
         }
      }
   ],
   "status":"success",
   "metrics":{
      "elapsedTime":"22.6797ms",
      "executionTime":"17.0216ms",
      "resultCount":1,
      "resultSize":87,
      "serviceLoad":4,
      "mutationCount":1
   }
}

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.

Example 22. Simple INSERT Statement Using KEY VALUE Pairs to Insert Two Documents

Query

EXPLAIN INSERT INTO `travel-sample`.inventory.airline (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": "5d1797cb-a7df-409d-b924-130ba0cc597a",
    "signature": "json",
    "results": [
    {
        "plan": {
            "#operator": "Sequence",
            "~children": [
                {
                    "#operator": "ValueScan",
                    "cardinality": 2,
                    "cost": 0.032,
                    "values": "[[\"1025\", {\"callsign\": \"SKY-AIR\", \"country\": \"United States\", \"id\": \"1025\", \"type\": \"airline\"}], [\"1026\", {\"callsign\": \"F1-AIR\", \"country\": \"United States\", \"id\": \"1014\"}]]"
                },
                {
                    "#operator": "Parallel",
                    "maxParallelism": 1,
                    "~child": {
                        "#operator": "Sequence",
                        "~children": [
                            {
                                "#operator": "SendInsert",
                                "alias": "airline",
                                "bucket": "travel-sample",
                                "keyspace": "airline",
                                "namespace": "default",
                                "scope": "inventory"
                            },
                            {
                                "#operator": "InitialProject",
                                "result_terms": [
                                    {
                                        "expr": "self",
                                        "star": true
                                    }
                                ]
                            }
                        ]
                    }
                }
            ]
        },
        "text": "INSERT INTO `travel-sample`.inventory.airline (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": "6.5577ms",
        "executionTime": "6.2773ms",
        "resultCount": 1,
        "resultSize": 1898,
        "serviceLoad": 4
    }
}

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).

Example 23. INSERT Statement Using the Projection of a Select Statement to Generate Values

Query

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

Results

[
  {
    "plan": {
      "#operator": "Sequence",
      "~children": [
        {
          "#operator": "Sequence",
          "~children": [
            {
              "#operator": "IndexScan3", (1)
              "bucket": "travel-sample",
              "index": "def_inventory_airport_airportname",
              "index_id": "14b05d2b21bd6eee",
              "index_projection": {
                "primary_key": true
              },
              "keyspace": "airport",
              "namespace": "default",
              "scope": "inventory",
              "spans": [
                {
                  "exact": true,
                  "range": [
                    {
                      "high": "\"Heathrow\"",
                      "inclusion": 3,
                      "low": "\"Heathrow\""
                    }
                  ]
                }
              ],
              "using": "gsi"
            },
            {
              "#operator": "Fetch", (2)
              "bucket": "travel-sample",
              "keyspace": "airport",
              "namespace": "default",
              "scope": "inventory"
            },
            {
              "#operator": "Parallel",
              "~child": {
                "#operator": "Sequence",
                "~children": [
                  {
                    "#operator": "Filter", (3)
                    "condition": "((`airport`.`airportname`) = \"Heathrow\")"
                  },
                  {
                    "#operator": "InitialProject",
                    "result_terms": [
                      {
                        "expr": "`airport`"
                      }
                    ]
                  }
                ]
              }
            }
          ]
        },
        {
          "#operator": "Parallel",
          "~child": {
            "#operator": "Sequence",
            "~children": [
              {
                "#operator": "SendInsert", (4)
                "alias": "airport",
                "bucket": "travel-sample",
                "key": "uuid()",
                "keyspace": "airport",
                "namespace": "default",
                "scope": "inventory",
                "value": "`airport`"
              },
              {
                "#operator": "Discard"
              }
            ]
          }
        }
      ]
    },
    "text": "INSERT INTO `travel-sample`.inventory.airport (key UUID(), value airport)\n    SELECT airport FROM `travel-sample`.inventory.airport\n      WHERE airportname = \"Heathrow\";"
  }
]

The Query Engine first executes the SELECT statement and then uses the projection to insert into the travel-sample.inventory.airport keyspace, performing the operations in the order listed:

1	An `IndexScan` to search for documents using the `def_inventory_airport_airportname` index.
2	A `Fetch` for the document in the `airport` keyspace.
3	A `Filter` for documents with `airportname="Heathrow"`.
4	An `Insert` of the value along with the auto-generated key into the `airport` keyspace.