Using JOIN, Aggregate function SUM() and GROUP BY in couchbase

Question

I have 5 document in DB. I have a bucket name as 'workflow_update' and the document structure is as below :-

{
    "attributes": {
       "activity": "Brand Management",
       "numberOfLine": "5"
    },
    "appId": "98b7dcbd-89b1-4d41-b951-28eec467218e",
    "type": "task"
},
{
    "attributes": {
       "activity": "Brand Management",
       "numberOfLine": "1"
    },
    "appId": "98b7dcbd-89b1-4d41-b951-28eec467218e",
    "type": "task"
},
{
    "attributes": {
       "activity": "Upsert",
       "numberOfLine": "2"
    },
    "appId": "98b7dcbd-89b1-4d41-b951-28eec467218e",
    "type": "task"
},
{
    "attributes": {
       "activity": "Upsert",
       "numberOfLine": "10"
    },
    "appId": "98b7dcbd-89b1-4d41-b951-28eec467218e",
    "type": "task"
}

Expected Result :-

[
    {
       "activity": "Brand Management",
       "numberOfLine": "6",
       "count": "2"
    },
    {
       "activity": "Upsert",
       "numberOfLine": "12",
       "count": "2"
    }
]

Query :-

select 
p.attributes.activity AS label, 
SUM(p.attributes.numberOfLine) AS numberOfLines, 
SUM(p.counts) AS `value`
from (SELECT wf.appId, wf.attributes.activity,
      wf.attributes.numberOfLine,
      count(1) AS `counts`
  FROM `workflow_update` AS wf
  WHERE wf.appId='98b7dcbd-89b1-4d41-b951-28eec467218e' 
  AND wf.attributes.activity != '' 
  AND wf.type='task'
  group by wf.appId, wf.attributes.activity, wf.attributes.numberOfLine) AS p 
 LEFT JOIN `workflow_update` AS wf_up ON p.appId = wf_up.appId and wf_up.type='task'
 Group By p

Result :-

{
   "code": 4330,
   "msg": "No index available for ANSI join term wf_up"
}

So for any activity I want the activity name, total numberOfLine and count of the total activity. Any leads for the expected result will be very helpful.

Answer 1

What is the purpose of the LEFT JOIN in your query? It seems like we can achieve what you need with something like:

SELECT wf.attributes.activity AS label,
      SUM(TONUMBER(wf.attributes.numberOfLine)) AS numberOfLines,
      COUNT(1) AS `value`
FROM `workflow_update` AS wf
WHERE wf.appId='98b7dcbd-89b1-4d41-b951-28eec467218e'
  AND wf.attributes.activity != ''
  AND wf.type='task'
GROUP BY wf.attributes.activity;

Note that numberOfLine is a string in your example so you'll need to convert it to number before performing SUM operation.

The error you are getting from your original query is due to lack of an appropriate secondary index for the join term wf_up which you can fix by creating something like:

CREATE INDEX idx1 ON `workflow_update` (appId) WHERE type = "task";