Trying to UPDATE a doc, need help, New to N1QL

SQL++
1

I am new, I am trying to make this

{
“_id”: “XYZ::JJJ::john.smith::123456::20220615”,
“_rev”: “1-123456789”,
“channels”: [
“john.smith”
],
}

look like this

{
“_id”: “XYZ::JJJ::john.smith::123456::20220615”,
“_rev”: “1-123456789”,
“channels”: [
“john.smith”
],
“data” : {}
}

2

Assume you have a bucket or collection called “default”. If you use collection, set query_context as appropriate.

CREATE PRIMARY INDEX ON default;
INSERT INTO default VALUES ("k001", { "_id": "XYZ::JJJ::john.smith::123456::20220615", "_rev": "1-123456789", "channels": [ "john.smith" ] });
UPDATE default SET data = {} WHERE _id = "XYZ::JJJ::john.smith::123456::20220615";
SELECT * FROM default WHERE _id = "XYZ::JJJ::john.smith::123456::20220615";
4

Thanks, I think I was overthinking it, we have a bucket called EDC, I need to update a bulk number of records, so I was thinking of this statement

UPDATE EDC USE KEYS [‘XYZ::JJJ::john.smith::123456::20220615’, ‘DDD::JJJ::jane.smith::123456::20220615’]

SET data = {}

also, is there a way to check how many mutations this will have, I am used to sql server and I use ROLLBACK statement, to check how many records will be affected before I commit the changes, just to be sure I am not updating like a million records, lol.

5

Your statement should work, except if you want to do bulk update you need to include all the document keys in the USE KEYS clause. In addition, what’s included in the USE KEYS clause needs to be the document key. If you have a predicate that provides you the documents you needed for the update then you can also use that in a WHERE clause instead of USE KEYS.

If you use cbq-shell to run queries (cbq: The Command Line Shell for N1QL | Couchbase Docs), it returns the query result as well as “metrics” object that contains “mutationCount”, which I believe is what you are looking for:

cbq> update default set data = {} where _id = "XYZ::JJJ::john.smith::123456::20220615";
{
    "requestID": "7bacf3a2-fb6c-4eaa-8737-1b86ecae8809",
    "signature": null,
    "results": [
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "3.170831ms",
        "executionTime": "3.093687ms",
        "resultCount": 0,
        "resultSize": 0,
        "serviceLoad": 2,
        "mutationCount": 1
    }
}

I’m less familiar with SDK and other potential ways to get this same information.
If you use Query WorkBench, the mutation count is displayed (together with some other information) to the right of the “Execute” button.

1 Like