©2015 - 2022 Chad’s Technoworks. Disclaimer and Terms of Use

Information Technology

COUCHBASE CRUD OPERATIONS USING CBC - page 2

 Prev< 1 2 3 > Next

U = UPDATE A DOCUMENT

CBC Replace By Example

List the current document emp_1234

[couchbas@lxnode4 Documents]$ cbc cat emp_1234 -U http://lxnode5.vlabs.net/CRUD_Lab

emp_1234             CAS=0xf36b2c412b14, Flags=0x0. Size=156

{"comm":0,"dept":{"deptno":20,"dname":"RESEARCH","loc":"DALLAS"},"empno":1234,"ename":"COUNT","hiredate":"1989/10/15","job":"MUPPET","mgr":7902,"sal":900}

[couchbas@lxnode4 Documents]$

Note that the above result set is a single liner. This usually is the case for al documents created by N1QL whereas documents created by CBC commands sourced from a file that was formatted with carriage-return will have a result set aligned with carriage return.

Using CBC commands, change name from COUNT to KYLE for empno 1234.

file: update_empno1234.json

{

"empno": 1234,

"ename": "KYLE",

"job": "MUPPET",

"mgr": 7902,

"hiredate": "1989/10/15",

"sal": 900,

"comm": 0,

"dept": { "deptno": 20, "dname": "RESEARCH", "loc": "DALLAS" }

}

cbc create --mode replace -U http://lxnode5.vlabs.net/CRUD_Lab emp_1234 < update_empno1234.json

[couchbas@lxnode4 Documents]$ cbc create --mode replace -U http://lxnode5.vlabs.net/CRUD_Lab emp_1234 < update_empno1234.json

emp_1234            Stored. CAS=0x96ba077e2b14

[couchbas@lxnode4 Documents]$

List the document to check changes:

[couchbas@lxnode4 Documents]$ cbc cat emp_1234 -U http://lxnode5.vlabs.net/CRUD_Lab

emp_1234             CAS=0x96ba077e2b14, Flags=0x0. Size=178

{

"empno": 1234,

"ename": "KYLE",

"job": "MUPPET",

"mgr": 7902,

"hiredate": "1989/10/15",

"sal": 900,

"comm": 0,

"dept": { "deptno": 20, "dname": "RESEARCH", "loc": "DALLAS" }

}

[couchbas@lxnode4 Documents]$

CBC N1QL Update By Example

The N1QL UPDATE command provides more options to update documents by introducing FOR loop clause to iterate within a nested array.

For more details on the Update syntax, refer to Couchbase N1QL Update Statement article.

UPDATE To Change Value Of Attributes

cbc n1ql 'select * from CRUD_Lab'  -U http://lxnode5.vlabs.net

cbc n1ql \

'UPDATE CRUD_Lab USE KEYS "emp_1234" SET job = "ENGINEER", dept = {"deptno":30, "dname":"SALES", "loc":"CHICAGO"} RETURNING CRUD_Lab.*' \

 -U http://lxnode5.vlabs.net

[couchbas@lxnode4 Documents]$ cbc n1ql \

> 'UPDATE CRUD_Lab USE KEYS "emp_1234" SET job = "ENGINEER", dept = {"deptno":30, "dname":"SALES", "loc":"CHICAGO"} RETURNING CRUD_Lab.*' \

> -U http://lxnode5.vlabs.net

Encoded query: {"statement":"UPDATE CRUD_Lab USE KEYS \"emp_1234\" SET job = \"ENGINEER\", dept = {\"deptno\":30, \"dname\":\"SALES\", \"loc\":\"CHICAGO\"} RETURNING CRUD_Lab.*"}

{

            "comm": 0,

            "dept": {

                "deptno": 30,

                "dname": "SALES",

                "loc": "CHICAGO"

            },

            "empno": 1234,

            "ename": "KYLE",

            "hiredate": "1989/10/15",

            "job": "ENGINEER",

            "mgr": 7902,

            "sal": 900

        },

** N1QL Response finished

{

    "requestID": "b14ae27c-8e7a-4cff-a1ad-d87c01114317",

    "signature": {

        "*": "*"

    },

    "results": [

        ],

    "status": "success",

    "metrics": {

        "elapsedTime": "4.54211ms",

        "executionTime": "4.458828ms",

        "resultCount": 1,

        "resultSize": 341,

        "mutationCount": 1

    }

}

[couchbas@lxnode4 Documents]$

UPDATE To Add A New Attribute To A Document

The following will add languages attribute of type array in all documents.

cbc n1ql 'UPDATE CRUD_Lab SET languages = ["english","spanish"] RETURNING CRUD_Lab.*' -U http://lxnode5.vlabs.net

[couchbas@lxnode4 Documents]$ cbc n1ql 'UPDATE CRUD_Lab SET languages = ["english","spanish"] RETURNING CRUD_Lab.*' -U http://lxnode5.vlabs.net

Encoded query: {"statement":"UPDATE CRUD_Lab SET languages = [\"english\",\"spanish\"] RETURNING CRUD_Lab.*"}

{

            "comm": 0,

            "dept": {

                "deptno": 30,

                "dname": "SALES",

                "loc": "CHICAGO"

            },

            "empno": 1234,

            "ename": "KYLE",

            "hiredate": "1989/10/15",

            "job": "ENGINEER",

            "languages": [

                "english",

                "spanish"

            ],

            "mgr": 7902,

            "sal": 900

        },

{

            "comm": 0,

            "dept": {

                "deptno": 20,

                "dname": "RESEARCH",

                "loc": "DALLAS"

            },

            "empno": 7369,

            "ename": "SMITH",

            "hiredate": "1980/12/17",

            "job": "CLERK",

            "languages": [

                "english",

                "spanish"

            ],

            "mgr": 7902,

            "sal": 800

        },

** N1QL Response finished

{

    "requestID": "de0126a0-3298-43f5-9ff9-72f60c089820",

    "signature": {

        "*": "*"

    },

    "results": [

        ],

    "status": "success",

    "metrics": {

        "elapsedTime": "18.72835ms",

        "executionTime": "18.648156ms",

        "resultCount": 2,

        "resultSize": 872,

        "mutationCount": 2

    }

}

[couchbas@lxnode4 Documents]$

Querying Arrays By Example

Now that we had created a document with key-value having arrays, let’s do a sample array query.

cbc n1ql 'SELECT ename, dept.dname FROM CRUD_Lab WHERE "spanish" IN languages' -U http://lxnode5.vlabs.net

[couchbas@lxnode4 Documents]$ cbc n1ql 'SELECT ename, dept.dname FROM CRUD_Lab WHERE "spanish" IN languages' -U http://lxnode5.vlabs.net

Encoded query: {"statement":"SELECT ename, dept.dname FROM CRUD_Lab WHERE \"spanish\" IN languages"}

{

            "dname": "SALES",

            "ename": "KYLE"

        },

{

            "dname": "RESEARCH",

            "ename": "SMITH"

        },

** N1QL Response finished

{

    "requestID": "24dbeff7-7974-458e-9c1b-16a33cf9e65e",

    "signature": {

        "dname": "json",

        "ename": "json"

    },

    "results": [

        ],

    "status": "success",

    "metrics": {

        "elapsedTime": "16.608579ms",

        "executionTime": "16.521232ms",

        "resultCount": 2,

        "resultSize": 142

    }

}

[couchbas@lxnode4 Documents]$

UPDATE To Remove An Attribute From The Document

This is the equivalent of dropping a column of a table.

cbc n1ql 'UPDATE CRUD_Lab UNSET languages RETURNING CRUD_Lab.*' -U http://lxnode5.vlabs.net

[couchbas@lxnode4 Documents]$ cbc n1ql 'UPDATE CRUD_Lab UNSET languages RETURNING CRUD_Lab.*' -U http://lxnode5.vlabs.net

Encoded query: {"statement":"UPDATE CRUD_Lab UNSET languages RETURNING CRUD_Lab.*"}

{

            "comm": 0,

            "dept": {

                "deptno": 30,

                "dname": "SALES",

                "loc": "CHICAGO"

            },

            "empno": 1234,

            "ename": "KYLE",

            "hiredate": "1989/10/15",

            "job": "ENGINEER",

            "mgr": 7902,

            "sal": 900

        },

{

            "comm": 0,

            "dept": {

                "deptno": 20,

                "dname": "RESEARCH",

                "loc": "DALLAS"

            },

            "empno": 7369,

            "ename": "SMITH",

            "hiredate": "1980/12/17",

            "job": "CLERK",

            "mgr": 7902,

            "sal": 800

        },

** N1QL Response finished

{

    "requestID": "53c08478-8dd8-4875-bd56-5d5b57754e82",

    "signature": {

        "*": "*"

    },

    "results": [

        ],

    "status": "success",

    "metrics": {

        "elapsedTime": "21.75925ms",

        "executionTime": "21.581451ms",

        "resultCount": 2,

        "resultSize": 682,

        "mutationCount": 2

    }

}

[couchbas@lxnode4 Documents]$

UPSERT To Add A Document If It Doesn't Exist

CBC Upsert By Example

file: allen.json

{

"empno": 7499,

"ename": "ALLEN",

"job": "SALESMAN",

"mgr": 7698,

"hiredate": "1981/02/20",

"sal": 1600,

"comm": 300,

"dept": { "deptno": 30, "dname": "SALES", "loc": "CHICAGO" }

}

cbc create --mode upsert -U http://lxnode5.vlabs.net/CRUD_Lab emp_7499 < allen.json

[couchbas@lxnode4 Documents]$ cbc create --mode upsert -U http://lxnode5.vlabs.net/CRUD_Lab emp_7499 < allen.json

emp_7499            Stored. CAS=0x36211c52c14

[couchbas@lxnode4 Documents]$

N1QL Upsert By Example

JSON doc for employee Blake:

{

"empno": 7698,

"ename": "BLAKE",

"job": "MANAGER",

"mgr": 7839,

"hiredate": "1981/05/01",

"sal": 2850,

"comm": 0,

"dept": { "deptno": 30, "dname": "SALES", "loc": "CHICAGO" }

}

cbc n1ql -U http://lxnode5.vlabs.net \

'UPSERT INTO CRUD_Lab (KEY, VALUE) VALUES ("emp_7698", {"empno": 7698,"ename": "BLAKE","job": "MANAGER","mgr": 7839,"hiredate": "1981/05/01","sal": 2850,"comm": 0,"dept": { "deptno": 30, "dname": "SALES", "loc": "CHICAGO" }}) RETURNING *'

[couchbas@lxnode4 ~]$ cbc n1ql -U http://lxnode5.vlabs.net \

> 'UPSERT INTO CRUD_Lab (KEY, VALUE) VALUES ("emp_7698", {"empno": 7698,"ename": "BLAKE","job": "MANAGER","mgr": 7839,"hiredate": "1981/05/01","sal": 2850,"comm": 0,"dept": { "deptno": 30, "dname": "SALES", "loc": "CHICAGO" }}) RETURNING *'

Encoded query: {"statement":"UPSERT INTO CRUD_Lab (KEY, VALUE) VALUES (\"emp_7698\", {\"empno\": 7698,\"ename\": \"BLAKE\",\"job\": \"MANAGER\",\"mgr\": 7839,\"hiredate\": \"1981/05/01\",\"sal\": 2850,\"comm\": 0,\"dept\": { \"deptno\": 30, \"dname\": \"SALES\", \"loc\": \"CHICAGO\" }}) RETURNING *"}

{

            "CRUD_Lab": {

                "comm": 0,

                "dept": {

                    "deptno": 30,

                    "dname": "SALES",

                    "loc": "CHICAGO"

                },

                "empno": 7698,

                "ename": "BLAKE",

                "hiredate": "1981/05/01",

                "job": "MANAGER",

                "mgr": 7839,

                "sal": 2850

            }

        },

** N1QL Response finished

{

    "requestID": "b0e3e543-dac6-43bd-8e23-2e154adb6053",

    "signature": {

        "*": "*"

    },

    "results": [

        ],

    "status": "success",

    "metrics": {

        "elapsedTime": "7.698147ms",

        "executionTime": "7.628542ms",

        "resultCount": 1,

        "resultSize": 430,

        "mutationCount": 1

    }

}

[couchbas@lxnode4 ~]$