couchbase N1QL group-by in sub-document

Question

given the below data model:

{
 "events": [
   {
      "customerId": "a", 
      "type": "credit" ,
      "value": 10 
   },
   {
      "customerId": "a", 
      "type": "credit" ,
      "value": 10 
   },
   {
      "customerId": "b", 
      "type": "credit" ,
      "value": 5 
   },
   {
      "customerId": "b", 
      "type": "credit" ,
      "value": 5 
   }
 ]
}

how can i query the sum of credits by customerId ? i.e:

{
  {
   "customerId": "a",
   "total": "20
  },
  {
   "customerId": "b",
   "total": "10
  }
}

I'm assuming you have more than one document of that type. Do you want the aggregation at per-document, or do you want to aggregate across all documents? — Matthew Groves
@MatthewGroves yes there are multiple documents of the type, aggregation at document level should do. The answer from vsr works. — Sreerag

vsr vsr · Accepted Answer · 2020-12-16T15:41:17

Use SUBQUERY expression per document aggregation

SELECT d.*, 
    (SELECT e.customerId, SUM(e.`value`) AS total
     FROM d.events AS e
     WHERE ......
     GROUP BY e.customerId) AS events
FROM default AS d
WHERE ...........;

For Whole Query

SELECT e.customerId, SUM(e.`value`) AS total
FROM default AS d
UNNEST d.events AS e
WHERE ......
GROUP BY e.customerId;

couchbase N1QL group-by in sub-document

1 Answers