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
  }
}

Answer 1

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;