Delete all documents with null content from a Couchbase using N1QL

Question

I executed a query with a little error on my couchbase server, this is executed query:

INSERT INTO `Writer_DB` (KEY _k, VALUE _v)
SELECT META().id _k, _v
from `Sam_DB` v

I used the v instead of _v so the operation was executed, but the result was that I have the Id's but I don't have the document's content, so when I search a document by its Id the content displayed is null.

I run again the query but it retrieved an error due the duplicate Id's so it would be nice to delete all documents with null content or "fill" the document content by Id. The thing is that I'm a newbie in couchbase and NoSql.

There's no quick way to do this. If there is a single key you know should exist in every document, it would be easy to use a mango find query to fetch all documents where that key is empty, then delete those. Otherwise, you probably need to fetch every document, then in your app see if it has any keys other than _id. — Flimzy
As for "filling" the document, just replace it as usual, using a PUT. — Flimzy

vsr vsr · Accepted Answer · 2018-05-15T16:30:35

DELETE FROM `Writer_DB` AS d WHERE d IS NULL;

The above query requires primary index, to speed the query up the following index can be used:

create index ix1 on default(self) WHERE self IS NULL;
DELETE FROM `Writer_DB` AS d WHERE d IS NULL;

To update the documents

UPDATE `Writer_DB` SET id = META().id WHERE d IS NULL;

OR

DELETE FROM `Write_DB` USE KEYS (SELECT RAW META().id from `Sam_DB`);

Delete all documents with null content from a Couchbase using N1QL

1 Answers