How do I count result from a query?

n1ql
query

#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.


#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)


#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;
});
}