How to write one n1ql query to sum values from multiple documents in the same bucket

Question

I am a newbie to Couchbase DB server and I am trying to achieve with one query what I have done with three queries as this is not so efficient.

I have three different documents types (x,y,z) in the same bucket; all having a similar key: 'district' like so:

document x:

{
  "type": "x",
  "district": "Some district"
}

document y:

{
  "type": "y",
  "district": "Some district"
}

document z:

{
  "type": "z",
  "district": "Some district"
}

I have currently implemented something like the following pseudo-code in PHP:

$totalDistrictInX = "SELECT COUNT(x) FROM bucket WHERE type = 'x' AND district = 'Maboro';

$totalDistrictInY = "SELECT COUNT(x) FROM bucket WHERE type = 'y' AND district = 'Maboro';

$totalDistrictInZ = "SELECT COUNT(x) FROM bucket WHERE type = 'z' AND district = 'Maboro';

$totalCountOfMaboro = $totalDistrictInX + $totalDistrictInY + $totalDistrictInZ;

I cannot use a JOIN query because the Couchbase server currently in use is below 5.50 which only supports joining documents between document key to document field and not between document fields.

Is there a way to achieve this with one just n1ql query? Any help will be much appreciated, please.

vsr vsr · Accepted Answer · 2019-07-24T14:11:38

Use aggregate query without group by for total count, control what documents to count through predicate.

SELECT COUNT(1) AS cnt
FROM bucket
WHERE type IN ['x', 'y', 'z'] AND district = 'Maboro';

If you need count for each type use GROUP BY

SELECT type, COUNT(1) AS cnt
FROM bucket
WHERE type IN ['x', 'y', 'z'] AND district = 'Maboro'
GROUP BY type;

If you want total count and individual type, its counts as array

SELECT ARRAY_SUM(av[*].cnt) AS totalcnt, av AS details
LET av = (SELECT type, COUNT(1) AS cnt
          FROM bucket
          WHERE type IN ['x', 'y', 'z'] AND district = 'Maboro'
          GROUP BY type);

How to write one n1ql query to sum values from multiple documents in the same bucket

2 Answers