What would be the best way for a delete n1ql query to return the count of documents that it deletes?

SQL++
1

I am trying to execute a delete n1ql query I see that there is a returning keyword i can use at the end which can return fields in the document or * returns the entire document. I would like to return the count of documents that got deleted.
Here is a query I am trying to use:

delete from " + repo.BucketName + " where CreateTime is not missing and CreateTime >= ‘" + TimeValue+ "’ returning *

Any help would be appreciated

2

If you run your DELETE query in cbq shell, you will see mutationCount in the output.

3

Thank you for the prompt response!! I am using golang with couchbase. I do not see mutation count in the response at both places. I am using Couchbase 4.5 version.
Both in go lang response and in querybench mark in couchbase UI I get this empty response("[]") but documents are deleted:

My golang code with query:

var query *gocb.N1qlQuery
qtime:= testBeginTime.Format(“2006-01-02T15:04:05.999999999Z07:00”)

query = gocb.NewN1qlQuery("delete from " + BucketName + " where CreatedTime is not missing and CreatedTime >= '" + qtime + "'")
result, err := repo.bucket.ExecuteN1qlQuery(query, nil)
fmt.Println("After Delete Query")
fmt.Println(result)

Output:
After Delete Query
&{-1 [] }

I dont see a mutationCount as a response. Can you tell me how I can access it? Thank You for your patience and helping me out.

4

Adding @ingenthr to help with golang.

5

I’ll have a look into it @prashant.krishnan and either @brett19 or I will reply soon.

6

Sorry for the delay @prashant.krishnan.

My guess is there is something in your test was incorrect as returning does return results. Counting the returned results is the best way to do this for now owing to GOCB-131.

Here’s a complete program that shows returning properly returning the deleted docs. Note that it uses RequestPlus consistency to ensure the statement is executed with respect to all currently received data.

By the way, I had trouble from the query console too. It just returned an empty array even though I’m certain the docs had been there for some time. This might be something for @eben to look into.

package main

import (
	"fmt"
	"github.com/couchbase/gocb"
	"encoding/json"
)

// bucket reference - reuse as bucket reference in the application
var bucket *gocb.Bucket

func main() {
	// Connect to Cluster
	cluster, err := gocb.Connect("couchbase://127.0.0.1")
	if err != nil {
		fmt.Println("ERRROR CONNECTING TO CLUSTER:", err)
	}
	// Open Bucket
	bucket, err = cluster.OpenBucket("default", "")
	if err != nil {
		fmt.Println("ERRROR OPENING BUCKET:", err)
	}

	// Create some things to delete…
	myDoc := []byte(`{"name":"one", "type":"test"}`)
	cas, err := bucket.Upsert("one", &myDoc, 0)
	if err == nil {
		fmt.Printf("Added document returning cas %d\n", cas)
	}

	myDoc = []byte(`{"name":"two", "type":"test"}`)
	cas, err = bucket.Upsert("two", &myDoc, 0)
	if err == nil {
		fmt.Printf("Added document returning cas %d\n", cas)
	}

	myQuery := gocb.NewN1qlQuery("DELETE FROM default WHERE type = \"test\" RETURNING default.*")
	myQuery.Consistency(gocb.RequestPlus)

	// Execute Query
	rows, err := bucket.ExecuteN1qlQuery(myQuery, nil)
	if err != nil {
		fmt.Println("ERROR EXECUTING N1QL QUERY:", err)
	}

	// Iterate through rows and print output
	var row map[string]interface{}
	fmt.Printf("Results:\n")
	for rows.Next(&row) {
		jsonstring, err := json.Marshal(row)
		if err!=nil {
			fmt.Println("ERROR MARSHALING JSON:", err)
		}
		fmt.Printf("%s\n", jsonstring)
	}

	// Exiting
	fmt.Println("Example Successful - Exiting")
}
7

Support for “mutation count” in the query workbench query results has been added in the next release, but it is not present in 4.5 or 4.6. The only way to see it in those versions is to run the query via ‘cbq’ or via one of SDKs as Matt described.