Couchbase Server Quickstart - SQL JSON Upsert and Lookup via Web UI

    +
    Using SQL syntax and the Couchbase Query Workbench, create new database records in Couchbase and look them up.

    Prerequisite: Run Couchbase Server

    1. Couchbase Server 6.5 is already running.

    2. An empty bucket named "default" has been created.

    3. Both a primary index and an adaptive index have been created and built on the default bucket.

    If you still need to perform these tasks please use the following:

    Step 1: Open the query working environment

    In this exercise, we will be working directly with the Query Workbench in Couchbase Web UI. If you would like to perform this exercise using the SDK and/or a development framework in your IDE, then please refer to one of the other Quickstart exercises.

    Open your web browser and navigate to the Couchbase WebUI. If you are using a local docker image per our instructions above, the Web UI is at http://localhost:8091. Log in and click "Query" in the left-hand navigation.

    Step 2: Build and UPSERT a new JSON document using query

    Run a query that projects a document key and JSON document value. Paste the following query into the query editor and click "Execute":

    SELECT
      "USER::00001" AS _k,
      {
        "firstName" : "Perry",
        "lastName" : "Mason",
        "email" : "perry.mason@acme.com",
        "tagLine" : "Who can we get on the case?",
        "type" : "user"
        } AS _v
    ;

    This query uses the 'dummy scan' to generate the projection. It should return the following result:

    [
      {
        "_k": "USER::00001",
        "_v": {
          "email": "perry.mason@acme.com",
          "firstName": "Perry",
          "lastName": "Mason",
          "tagLine": "Who can we get on the case?",
          "type": "user"
        }
      }
    ]

    UPSERT the record into the default bucket by prefixing the query with an UPSERT statement:

    UPSERT INTO default (KEY _k, VALUE _v)
    
    SELECT
      "USER::00001" AS _k,
      {
        "firstName" : "Perry",
        "lastName" : "Mason",
        "email" : "perry.mason@acme.com",
        "tagLine" : "Who can we get on the case?",
        "type" : "user"
        } AS _v
    ;

    Paste this query into the editor and click, "Execute". The results[] array will be empty, but the editor should report docs:1 in the status line indicating (1) document mutation:

    success just now  elapsed: 12.3ms  execution: 11.6ms  docs: 1  size: 991 bytes

    Now retrieve the new key and document from the database with the following query:

    SELECT meta().id AS _k, _v FROM default _v;

    This query selects every document key and JSON document value from the bucket. So far for us there is only the one we have just upserted. Notice that the results are exactly same as our first query. The only difference here is that our first SELECT was generating the projection from a 'dummy scan', and this query is retrieving the new record from a 'primary scan' and 'fetch' from default bucket:

    [
      {
        "_k": "USER::00001",
        "_v": {
          "email": "perry.mason@acme.com",
          "firstName": "Perry",
          "lastName": "Mason",
          "tagLine": "Who can we get on the case?",
          "type": "user"
        }
      }
    ]
    You can use the 'Explain' button to review the different query plans.

    Step 3: UPSERT some more data and find a record

    Let’s use N1QL to generate two more records for us. In order to project multiple records, we express them within an array and pass it in the FROM clause. Paste and execute the following query to create and project some new data:

    SELECT x._k, x._v
    FROM [
      {
        "_k" : "USER::00002",
        "_v" : {
          "firstName" : "Major",
          "lastName" : "Tom",
          "email" : "major.tom@acme.com",
          "tagLine" : "Send me up a drink",
          "type" : "user"
          }
        },
      {
        "_k" : "USER::00003",
        "_v" : {
          "firstName" : "Jerry",
          "lastName" : "Wasaracecardriver",
          "email" : "jerry.wasaracecardriver@acme.com",
          "tagLine" : "el sob number one",
          "type" : "user"
          }
        }
      ] x
    ;

    The query performs an 'expression scan' of the array and projects the _k and _v elements in the SELECT.

    [
      {
        "_k": "USER::00002",
        "_v": {
          "email": "major.tom@acme.com",
          "firstName": "Major",
          "lastName": "Tom",
          "tagLine": "Send me up a drink",
          "type": "user"
        }
      },
      {
        "_k": "USER::00003",
        "_v": {
          "email": "jerry.wasaracecardriver@acme.com",
          "firstName": "Jerry",
          "lastName": "Wasaracecardriver",
          "tagLine": "el sob number one",
          "type": "user"
        }
      }
    ]

    UPSERT new records into the default bucket by prefixing the query with the UPSERT statement we used earlier:

    UPSERT INTO default (KEY _k, VALUE _v)
    
    SELECT x._k, x._v
    FROM [
      {
        "_k" : "USER::00002",
        "_v" : {
          "firstName" : "Major",
          "lastName" : "Tom",
          "email" : "major.tom@acme.com",
          "tagLine" : "Send me up a drink",
          "type" : "user"
          }
        },
      {
        "_k" : "USER::00003",
        "_v" : {
          "firstName" : "Jerry",
          "lastName" : "Wasaracecardriver",
          "email" : "jerry.wasaracecardriver@acme.com",
          "tagLine" : "el sob number one",
          "type" : "user"
          }
        }
      ] x
    ;

    Again, the results[] array will be empty, but the status bar will indicate 2 mutations:

    success just now  elapsed: 12.9ms  execution: 12.8ms  mutations: 2

    Now, let’s retrieve all 3 of our records using our previous query:

    SELECT meta().id AS _k, _v FROM default _v;

    Results:

    [
      {
        "_k": "USER::00001",
        "_v": {
          "email": "perry.mason@acme.com",
          "firstName": "Perry",
          "lastName": "Mason",
          "tagLine": "Who can we get on the case?",
          "type": "user"
        }
      },
      {
        "_k": "USER::00002",
        "_v": {
          "email": "major.tom@acme.com",
          "firstName": "Major",
          "lastName": "Tom",
          "tagLine": "Send me up a drink",
          "type": "user"
        }
      },
      {
        "_k": "USER::00003",
        "_v": {
          "email": "jerry.wasaracecardriver@acme.com",
          "firstName": "Jerry",
          "lastName": "Wasaracecardriver",
          "tagLine": "el sob number one",
          "type": "user"
        }
      }
    ]

    Finally, let’s lookup Jerry’s record by his first name:

    SELECT * FROM default WHERE firstName = "Jerry";

    Results:

    [
      {
        "default": {
          "email": "jerry.wasaracecardriver@acme.com",
          "firstName": "Jerry",
          "lastName": "Wasaracecardriver",
          "tagLine": "el sob number one",
          "type": "user"
        }
      }
    ]

    Because of the filter clause, this query scans the adaptive index for the attribute label 'firstName' and then the attribute value 'Jerry', receiving the key for the document. It then fetches the document and projects the contents in the results[] array.

    Exercise Complete

    Congratuations! You have substantially engaged with the world’s most powerful JSON document database using the most advanced SQL++ query technology.