couchbase - couchbase N1QL group-by 在子文档中

Question

给定以下数据模型：

{
 "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 
   }
 ]
}

如何通过 customerId 查询信用总和？IE：

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

标签： couchbasen1ql

Answer 1

每个文档聚合使用 SUBQUERY 表达式

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

对于整个查询

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