Bulk update of all documents in a bucket

SQL++
1

Hello, I’d like to know if it is possible to make a massive update in all the documents that are stored in a bucket.
I want to add (append) an extra field to all the documents using a n1ql statement. Is this possible? What kind of statement do I need to do such thing?

Thanks.

I am using couchbase-server 4.5.

2

Yes, that’s certainly possible with N1QL. An UPDATE WHERE can do what you need.

At the moment that will have the query engine in your cluster fetch each document, make the change and store it. Since you say “massive update” and you’re on 4.5, if you need to be more efficient about the update and depending on the update, you could investigate using sub-document operations as those will move the mutation as close to the data as possible. They’re suitable for some mutations, but not all. You’ll have to see if it’ll handle your append. You can query for the meta().id, then sub-document handle the mutation.

While I’m at it, one other exceptionally efficient option is to use streaming with Apache Spark as I described in a blog.

That last suggestion may be more than you need for this update. Using the N1QL UPDATE with a WHERE clause is the simplest approach.

1 Like
3

Hi @fernando_l,

Btw, Couchbase 4.5.1 (just released) further enhances the UPDATE statement to navigate nested arrays with multiple levels of FOR expressions. More desc & examples at N1QL UPDATE documentation:
http://developer.couchbase.com/documentation/server/4.5/n1ql/n1ql-language-reference/update.html

Another related thread:

2 Likes