希望在应用group by子句后在couchbase中获得整个文档

Question

我有多个这样的json文档

{
"event": "eb1518fb-81d1-49aa-9854-405267a7828f",
"identityId": "5eb44b44-d5ce-4473-815c-591a4ff1bea3",
"bookingCode": "ABCDE",
"status": "SUCCESS",
"clientId": "DESKTOP",
"created": "2021-04-15T04:16:35+00:00",
"locationType": "STORE"
},
{
"event": "eb1518fb-81d1-49aa-9854-405267a7828f",
"identityId": "5eb44b44-d5ce-4473-815c-591a4ff1bea3",
"bookingCode": "ABCDE",
"status": "FAILURE",
"clientId": "MOBILE",
"created": "2021-04-15T04:16:35+00:00",
"locationType": "STORE"
},
{
"event": "eb1518fb-81d1-49aa-9854-405267a7828f",
"identityId": "q1244b44-d5ce-4473-815c-591a4ff1bea3",
"bookingCode": "BCD",
"status": "SUCCESS",
"clientId": "MOBILE,
"created": "2021-04-15T04:16:35+00:00",
"locationType": "STORE"
},
...

我想通过身份id和预订代码应用组来获得唯一的记录，并需要在过滤后获得整个JSON文档，像这样

select *, count(*) from eventbooking where event = "eb1518fb-81d1-49aa-9854-405267a7828f"
group by bookingCode, identityId

这给了我一个错误Expression self must depend only on group keys or aggregates是否有任何方法，我可以得到整个json文件与组过滤后?

我希望得到类似这样的输出。

{
"event": "eb1518fb-81d1-49aa-9854-405267a7828f",
"identityId": "5eb44b44-d5ce-4473-815c-591a4ff1bea3",
"bookingCode": "ABCDE",
"status": "FAILURE",
"clientId": "MOBILE",
"created": "2021-04-15T04:16:35+00:00",
"locationType": "STORE"
},
{
"event": "eb1518fb-81d1-49aa-9854-405267a7828f",
"identityId": "q1244b44-d5ce-4473-815c-591a4ff1bea3",
"bookingCode": "BCD",
"status": "SUCCESS",
"clientId": "MOBILE,
"created": "2021-04-15T04:16:35+00:00",
"locationType": "STORE"
}

Answer 1

查询使用GROUP BY或Aggregates，那么投影只能包含

<

• 组键/gh>骨料
• 组键或聚合的表达式
<
• 常量/gh>

假设每个组有较少的文档，您可以将它们聚合为ARRAY。如果需要不同的格式，通过数组下标获取或在父查询中使用UNNEST。如果它有很多使用覆盖查询和ARRAY_AGG文档键，那么父查询获取这些文档(流式传输文档)。

SELECT COUNT(1) AS cnt,
ARRAY_AGG(e) AS docs
FROM eventbooking AS e
WHERE e.event = "eb1518fb-81d1-49aa-9854-405267a7828f"
GROUP BY e.bookingCode, e.identityId;

如果您只需要组中最新创建的文档。

SELECT COUNT(1) AS cnt,
MAX([e.created,e])[1].* 
FROM eventbooking AS e
WHERE e.event = "eb1518fb-81d1-49aa-9854-405267a7828f"
GROUP BY e.bookingCode, e.identityId;

Answer 2

查询永远不会返回文档。它总是返回一个Map在SELECT子句中的投影(在本例中为"*&"，即所有值)。

如果你需要文档(为了修改它)，你可以查询Meta。匹配WHERE子句的文档的Id，然后遍历它们，获取、修改和保存文档。