A newer version of this documentation is available.

View Latest

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.

    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 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 insert-target ( insert-values | insert-select ) [ returning-clause ]
    'INSERT' 'INTO' insert-target ( insert-values | insert-select ) returning-clause?

    Insert Target

    insert-target ::= keyspace-ref [ [ AS ] alias ]
    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 ::= [ namespace ':' ] keyspace
    ( namespace ':' )? keyspace

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

    namespace

    (Optional) An identifier that refers to the namespace of the insert target. Currently, only the default namespace is available. If the namespace name is omitted, the default namespace in the current session is used.

    keyspace

    (Required) An identifier that refers to the bucket name or keyspace of the insert target.

    For example, default:`travel-sample` indicates the travel-sample keyspace in the default namespace.

    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 ")" ] values-clause
    ( '(' 'PRIMARY'? 'KEY' ',' 'VALUE' ')' )? 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 a well-formed JSON document.

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

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

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

    Example 1. Specify a key using an expression
    Query
    INSERT INTO `travel-sample` ( KEY, VALUE )
                        VALUES ( "airline" || TOSTRING(1234),
                        { "callsign": "" } )
                        RETURNING META().id;

    The KEY cannot be MISSING or NULL, and it cannot duplicate an existing 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:

    Example 2. Generate a unique key
    Query
    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

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

    A string, or an expression resolving to a string, representing the ID of the document to be inserted.

    value

    A well-formed JSON object, or an expression resolving to a well-formed JSON object, 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 or empty or MISSING values.

    Example 3. 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 4. 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 5. 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 6. Insert a NULL JSON document
    Query
    INSERT INTO `travel-sample` (KEY, VALUE)
        VALUES ( "1021",
                  { } )
                  RETURNING *;

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

    Insert Select

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

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

    key

    A string, or an expression resolving to a string, representing the ID of the document to be inserted.

    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.

    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.

    SELECT Statement

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

    Example 7. Insert with SELECT

    Query the travel-sample bucket for documents of type "airport" and airportname "Heathrow", and then insert the projection (1 document) into the travel-sample bucket using a unique key generated using UUID().

    Query
    INSERT INTO `travel-sample` (KEY UUID(), VALUE _airport)
        SELECT _airport FROM `travel-sample` _airport
          WHERE type = "airport" AND airportname = "Heathrow"
    RETURNING *;
    Results
    [
      {
        "travel-sample": {
          "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"
        }
      }
    ]

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

    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 8. 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 9. 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` (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"
      }
    ]

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

    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
    • 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://127.0.0.1:8093/admin/settings  -d '{ "debug":true, "pipeline-batch": 32 }' -u Administrator:password
    • 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 10. Overview

    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.

    Query
    INSERT INTO `travel-sample` ( KEY, VALUE )
      VALUES
      (
        "k001",
        { "id": "01", "type": "airline"}
      )
    RETURNING META().id as docid, *;
    Results
    {
      "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
      }
    }

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

    Example 11. Inserting a Single Document

    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
        }
    }

    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 12. Performing Bulk Inserts

    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 ( "airline_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
        }
    }

    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. Inserting Values using SELECT

    Query the travel-sample bucket for documents 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 _airport)
        SELECT _airport FROM `travel-sample` _airport
          WHERE type = "airport" AND airportname = "Heathrow";
    Results
    {
      "results": [],
      "metrics": {
        "elapsedTime": "10.616228ms",
        "executionTime": "10.576012ms",
        "resultCount": 0,
        "resultSize": 0,
        "mutationCount": 1
      }
    }
    Example 14. 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` (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"
        }
      }
    ]
    Example 15. Using Insert to Copy Bucket Data to Another Bucket

    Use the INSERT statement to create a copy of bucket_1 under the new name bucket_2.

    Query
    INSERT INTO bucket_2(key _k, value _v)
        SELECT META().id _k, _v
          FROM bucket_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 16. Inserting Values Using Subqueries

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

    Query
    INSERT INTO `travel-sample` (KEY UUID()) (3)
        SELECT x.name, x.city, "landmark_hotels" AS type (2)
          FROM `travel-sample` x
          WHERE x.type = "hotel" and x.city WITHIN
            ( SELECT DISTINCT t.city (1)
                FROM `travel-sample` t
                WHERE t.type = "landmark" )
          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
    [
      {
        "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"
        }
      }
    ]
    Example 17. 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` (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": "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
        }
    }
    Example 18. 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.

    1. Prepare the INSERT statement.

      Query
      PREPARE ins_india FROM
            INSERT INTO `travel-sample` (KEY, VALUE)
              VALUES ( $key,
                      { "type" : "airport",
                        "tz" : "India Standard Time",
                        "country" : "India",
                        "faa" : $faa_code} )
      RETURNING *;
    2. 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
        [
            {
                "default": {
                    "country": "India",
                    "faa": "DEL",
                    "type": "airport",
                    "tz": "India Standard Time"
                }
            }
        ]
    3. Execute the prepared statement using REST API. To execute using the cbq shell or the Query Workbench, go to Step 2.

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

        Query
        $ curl -v http://localhost:8093/query/service -d 'prepared="ins_india"&$key="airport_10002"&$faa_code="BLR"'
        Results
        [
            {
                "travel-sample": {
                    "country": "India",
                    "faa": "BLR",
                    "type": "airport",
                    "tz": "India Standard Time"
                }
            }
        ]

    The REST parameters should not have any spaces around & when passing multiple parameters. For example, the following REST API call 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"'
    "errors": [
        {
            "code": 5010,
            "msg": "Error evaluating VALUES. - cause: No value for named parameter $faa_code."
        }
    ]

    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 19. Simple INSERT Statement Using KEY VALUE Pairs to Insert Two Documents
    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
        }
    }

    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 20. INSERT Statement Using the Projection of a Select Statement to Generate Values
    Query
    EXPLAIN INSERT INTO `travel-sample` (key UUID(), value _airport)
        SELECT _airport FROM `travel-sample` _airport
          WHERE type = "airport" AND airportname = "Heathrow";
    Results
    [
      {
        "plan": {
          "#operator": "Sequence",
          "~children": [
            {
              "#operator": "Sequence",
              "~children": [
                {
                  "#operator": "IntersectScan", (3)
                  "scans": [
                    {
                      "#operator": "IndexScan3", (2)
                      "as": "_airport",
                      "index": "def_airportname",
                      "index_id": "af738c1d1aa1f107",
                      "index_projection": {
                        "primary_key": true
                      },
                      "keyspace": "travel-sample",
                      "namespace": "default",
                      "spans": [
                        {
                          "exact": true,
                          "range": [
                            {
                              "high": "\"Heathrow\"",
                              "inclusion": 3,
                              "low": "\"Heathrow\""
                            }
                          ]
                        }
                      ],
                      "using": "gsi"
                    },
                    {
                      "#operator": "IndexScan3", (1)
                      "as": "_airport",
                      "index": "def_type",
                      "index_id": "4877ed06167c9af8",
                      "index_projection": {
                        "primary_key": true
                      },
                      "keyspace": "travel-sample",
                      "namespace": "default",
                      "spans": [
                        {
                          "exact": true,
                          "range": [
                            {
                              "high": "\"airport\"",
                              "inclusion": 3,
                              "low": "\"airport\""
                            }
                          ]
                        }
                      ],
                      "using": "gsi"
                    }
                  ]
                },
                {
                  "#operator": "Fetch", (4)
                  "as": "_airport",
                  "keyspace": "travel-sample",
                  "namespace": "default"
                },
                {
                  "#operator": "Parallel",
                  "~child": {
                    "#operator": "Sequence",
                    "~children": [
                      {
                        "#operator": "Filter",
                        "condition": "(((`_airport`.`type`) = \"airport\") and ((`_airport`.`airportname`) = \"Heathrow\"))"
                      },
                      {
                        "#operator": "InitialProject",
                        "result_terms": [
                          {
                            "expr": "`_airport`"
                          }
                        ]
                      },
                      {
                        "#operator": "FinalProject"
                      }
                    ]
                  }
                }
              ]
            },
            {
              "#operator": "Parallel",
              "~child": {
                "#operator": "Sequence",
                "~children": [
                  {
                    "#operator": "SendInsert", (5)
                    "alias": "travel-sample",
                    "key": "uuid()",
                    "keyspace": "travel-sample",
                    "namespace": "default",
                    "value": "`_airport`"
                  },
                  {
                    "#operator": "Discard"
                  }
                ]
              }
            }
          ]
        },
        "text": "INSERT INTO `travel-sample` (KEY UUID(), VALUE _airport)\n    SELECT _airport FROM `travel-sample` _airport\n      WHERE type = \"airport\" AND airportname = \"Heathrow\";"
      }
    ]

    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:

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