In my process of learning Couchbase I was looking at a bucket full of documents that were created a while ago. There was a bunch of good, new documents in this same bucket. In an effort to keep moving forward I wanted to clean up the old junk and only keep around the new stuff. Most of the N1QL queries were pretty straight forward to find and delete the old documents.
After cleaning up a bunch of documents I noticed that in the Bucket Insights under Flavors there was:
Flavor 3 (0.1%)
Flavor – no fields found, perhaps binary data, not JSON?
The bucket has over 250,000 documents to 0.1% is a decent amount of documents. But how do I find them and clean them out? Having worked with relational databases for years this wouldn’t be an issue. Because with relational databases I know what the design of the table is and I can just check for empty fields. In Couchbase the JSON documents can be different. And this is the case in this bucket. There are 6 different flavors of the documents. There was no one consistent element that I could query against to see if it was empty . . . or was there.
The consistent element that was empty was the document its self. So in either an Ah-Ha or a Duh moment (depending on how you look at it) I decided to try the query:
SELECT * FROM vehicles WHERE vehicles IS NULL
Sure enough – this returned 85 empty documents.
Then I just deleted them with:
DELETE FROM `vehicles` WHERE `vehicles` IS NULL