Batch data manipulation can be super handy for all sorts of operations. Good news is, DML (data manipulation language) in N1QL provides full support for, not just singleton operations, but for powerful bulk/batch data transformation. Here are a few powerful examples;
– UPDATE can help you add and initialize a new attribute in documents that do not have the last_updated attribute. .
|
1 |
UPDATE bucket SET last_updated ="1/1/1973" WHERE last_updated IS MISSING; |
Or break a name attribute into first and last name.
|
1 |
UPDATE default SET first_name=SPLIT(name)[0], last_name=SPLIT(name)[1] UNSET name; |
– INSERT statement can batch in a few ways. Here is a sample with VALUES.
|
1 |
INSERT INTO bucket(key,value) VALUES("0",{"name":"Kaan"}), VALUES("1",{"name":"Lara"}), ... ; |
I also frequently copy my bucket data to other buckets for expimentation using INSERT. INSERT with SELECT moves large sets of data around with a single statement. Here is how to create a copy of bucket_1 under a new name bucket_2.
|
1 |
INSERT INTO bucket_2 (key _k, value _v) SELECT META().id _k, _v FROM bucket_1 _v; |
I also use UPSERT to merge the changes from bucket_1 to bucket_2 as bucket_1 changes.
|
1 2 |
UPSERT INTO bucket_2 (key _k, value _v) SELECT meta().id _k, _v FROM bucket_1 _v; |
Batch updates and bulk operations can be challenging to code without flexibilities of SQL. N1QL lets you do all that and more with JSON!
You can find more details on DML statements here:
INSERT: http://developer.couchbase.com/documentation/server/4.1/n1ql/n1ql-language-reference/insert.html
UPDATE: http://developer.couchbase.com/documentation/server/4.1/n1ql/n1ql-language-reference/update.html
UPSERT: http://developer.couchbase.com/documentation/server/4.1/n1ql/n1ql-language-reference/upsert.html
MERGE: http://developer.couchbase.com/documentation/server/4.1/n1ql/n1ql-language-reference/merge.html
DELETE: http://developer.couchbase.com/documentation/server/4.1/n1ql/n1ql-language-reference/delete.html
Happy testing.
-cihan
