I have the following query:
SELECT
e.data.platform.id as carId,
e.data.servicingDetail.aceDetail.deploymentId as deploymentId,
e.data.servicingDetail.aceDetail.ruleExecution.ruleName as ruleName,
e.data.servicingDetail.aceDetail.ruleExecution.rulePackageName as rulePackageName
FROM `ace_events` as e
WHERE e.type = "MatchFired"
AND e.data.servicingDetail.aceDetail.deploymentId="com.engineering:RulesUseCases:1.0.0"
AND e.data.platform.id="600000527"
AND e.time between "2020-08-03T00:00:00.698Z" AND "2020-09-03T05:00:00.698Z"
which produces a result like so:
[
{
"carId": "600000527",
"deploymentId": "com.engineering:RulesUseCases:1.0.0",
"ruleName": "walmart-DecisionTable_11",
"rulePackageName": "com.engineering.rulesusecases"
},
{
"carId": "600000527",
"deploymentId": "com.engineering:RulesUseCases:1.0.0",
"ruleName": "walmart-DecisionTable_11",
"rulePackageName": "com.engineering.rulesusecases"
},
{
"carId": "600000527",
"deploymentId": "com.engineering:RulesUseCases:1.0.0",
"ruleName": "walmart-DecisionTable_11",
"rulePackageName": "com.engineering.rulesusecases"
},
{
"carId": "600000527",
"deploymentId": "com.engineering:RulesUseCases:1.0.0",
"ruleName": "walmart-DecisionTable_11",
"rulePackageName": "com.engineering.rulesusecases"
}
]
I would like to reduce the duplicates but count the repeated members of the groups. My desired outcome would be something like this:
[
{
"carId": "600000527",
"deploymentId": "com.engineering:RulesUseCases:1.0.0",
"execCount": 3,
"ruleName": "walmart-DecisionTable_13",
"rulePackageName": "com.engineering.rulesusecases"
},
{
"carId": "600000527",
"deploymentId": "com.engineering:RulesUseCases:1.0.0",
"execCount": 5,
"ruleName": "walmart-DecisionTable_14",
"rulePackageName": "com.engineering.rulesusecases"
},
{
"carId": "600000527",
"deploymentId": "com.engineering:RulesUseCases:1.0.0",
"execCount": 2,
"ruleName": "walmart-DecisionTable_11",
"rulePackageName": "com.engineering.rulesusecases"
}
]
basically count any entry in the result that has the same values for all carId, deploymentId, ruleName and rulePackageName.
How can I achieve this with one N1QL query?