How do I count result from a query?

Go SDK
,
1

In MySQL I use num_rows to get count of result. How do I do the same in Couchbase NIQL? I have tried count($result) but it always give me 1 as the count.

2

If you are using N1QL you can use COUNT(*) to get the number of results in a query

3

Depends on how you are running the N1QL query.

If you’re using any of the SDK libraries you can look at the metrics object that is returned to get the result count

“metrics”: {
“resultCount”: 15
}

4

I am running the query like this:

$query = CouchbaseN1qlQuery::fromString(“SELECT count(*) FROM bucket WHERE type = ‘ac’ AND email = ‘xxxxx@xxxxxxxxxx.xx’”);

I am using the PHP SDK.

5

If you’re doing a SELECT COUNT then your $result should be one row, which is what you’re seeing in your count.

You will need to look in the actual $result to see the value that was returned
https://developer.couchbase.com/documentation/server/current/sdk/php/n1ql-queries-with-sdk.html

6

I have tried this but I have problems with getting the result to a variable.

$query = CouchbaseN1qlQuery::fromString(“SELECT count(*) FROM bucket WHERE type = ‘ac’ AND email = ‘xxxxx@xxxxxxxxxx.xx’”);
$result = $bucket->query($query);

I am not sure how to write this.

$number_of_rows = $result->rows as $row ??

7

Can you post a

var_dump($result)

8

This is what I get:

object(stdClass)#6 (5) { [“rows”]=> array(1) { [0]=> object(stdClass)#5 (1) { [“$1”]=> int(1) } } [“requestId”]=> string(36) “35c0f7da-898e-42ff-84be-c5d88efbeead” [“status”]=> string(7) “success” [“signature”]=> array(1) { [“$1”]=> string(6) “number” } [“metrics”]=> array(4) { [“elapsedTime”]=> string(11) “21.261354ms” [“executionTime”]=> string(11) “21.202906ms” [“resultCount”]=> int(1) [“resultSize”]=> int(8) } }

9

[“$1”]=> int(1) this is actual count.
Projection alias tries to derive from the expression identifier. If there is no alias it gives implicit aliases as “$1”, “$2”,…

In your case COUNT(*) expression and you have not given any alias it gave implicit alias as “$1”

SELECT count(*) AS cnt FROM bucket WHERE type = "ac" AND email = "xxxxx@xxxxxxxxxx.xx";

10

This seems to work but I wonder if the last line is correct?

$query = CouchbaseN1qlQuery::fromString(“SELECT count(*) AS cnt FROM bucket WHERE type = ‘ac’ AND email = ‘xxxxx@xxxxxxxxxx.xx”);
$result = $bucket->query($query);
$cnt = $result->rows[0]->cnt; ← is this the correct way???

11

I have a similar issue. For paging results, I need not only the count but the number documents covered by the query. I have seen this “metrics” object in examples and in documentation but it is never returned in the results. Is there a flag to trigger the metrics to return?

To be clear, if I am using LIMIT 25 and the total results would be 1,000 I need N1QL to return that but it returns neither the number of records returned nor the total documents covered in the query window, 1000 in my example.

Here is my bluebird-promisified search function:

/* retrieve a collection of meta-data representing links to idempotent end-points */
function search(options) {
  // need 'sortOn' & 'sortDir'

  options.offset = options.offset || 0;
  options.limit = (options.limit || process.env.APP_STORE_CB_PAGESIZE);
  options.metrics = true;

  var cbp = [].concat(options.offset, options.limit, options.metrics);

  var q = N1qlQuery.fromString(['SELECT', (options.select || '*'), 'FROM', (options.store_id || 'DEFAULT'), 'OFFSET', (options.offset || 0), 'LIMIT', options.limit].join(' '));
  return bucket.queryAsync(q, cbp)
    .then(function (results) {
      return results;
    });
}
12

The information will be part of metrics.

  "metrics": {
        "elapsedTime": "1.018619623s",
        "executionTime": "1.018592883s",
        "resultCount": 100,
        "resultSize": 30458,
        "sortCount": 31591
    }

resultCount tells result returned.
If query has ORDER BY and query does sort sortCount will tells qualified results by predicate.
If query has ORDER BY and uses Index order OR no ORDER BY sortCount will not present.
When Uses Index ORDER or LIMIT is present query stops when qualified rows satisfies so it doesn’t have that information. In that situations you can use aggregate query to count the results.

2 Likes
13

If you’re doing SELECT COUNT(*), I don’t know if Couchbase is optimized for this, but you could enforce optimization by doing SELECT COUNT(1). In old RDBMS, you don’t want to pull * if you’re just counting. Many have optimized this away…

14

CB 4.5.0 onwards COUNT(*) is optimized same as COUNT(1)

1 Like
15

I am not receiving a metric object in the response from Couchbase. As posted earlier, the query constructed below returns results exactly as desired EXCEPT no “metrics” object with which to properly configure the paging.

Do you see something in my implementation which may be lacking to receive it?

/* retrieve a collection of meta-data representing links to idempotent end-points */
function search(options) {
// need ‘sortOn’ & ‘sortDir’

options.offset = options.offset || 0;
options.limit = (options.limit || process.env.APP_STORE_CB_PAGESIZE);
options.metrics = true;

var cbp = [].concat(options.offset, options.limit, options.metrics);

var q = N1qlQuery.fromString([‘SELECT’, (options.select || ‘*’), ‘FROM’, (options.store_id || ‘DEFAULT’), ‘OFFSET’, (options.offset || 0), ‘LIMIT’, options.limit].join(’ '));
return bucket.queryAsync(q, cbp)
.then(function (results) {
return results;
});
}

16

Hi,

Is there any function to count the number of rows returned i.e. count the Resultset?

Thanks
Alponnu

17

The metrics.resultCount has that info.

“metrics”: {
“elapsedTime”: “7.937907ms”,
“executionTime”: “7.85005ms”,
“resultCount”: 3,
“resultSize”: 194593
}

18

In many examples, I have seen this “metrics” object. However, I have never been able to see it returned in the Couchbase Query workbench OR in the results returned in the Nodejs SDK. In fact, outside the examples, I have never been able to return this object in any way.

  1. How to return the metrics object via the Query workbench?
  2. How to return the metrics object to the Nodejs SDK?

If you can show me how to get that via the two means above, that would be greatly appreciated

19

The metrics object is not shown directly in the Query Workbench, but some of the information from metrics is shown in the UI next to the Execute button.

12%20AM
Screen Shot 2019-05-13 at 7.58.12 AM.png818×82 6.32 KB

20

Hey @jgcoding,

You can access the metrics in Node.js by checking inside the meta data returned by the query. This is accessible as a third argument in the callback that is returned.

bucket.query(..., function(err, res, meta) {
  console.log(meta);
});

Cheers, Brett