Couchbase N1QL query aggregation

Question

I'm trying to write a query that will aggregate the results of a query to provide total values for the results matched.

The documents in the bucket look like this:

{
    "clientId": "test-client",
    "event": {
        "history": [
            {
                "code": "FAILED",
                "serviceId": "s1"
            },
            {
                "code": "SUCCESS",
                "serviceId": "s2"
            }
        ],
        "size": 200
    }
},
{
    "clientId": "test-client",
    "event": {
        "history": [
            {
                "code": "FAILED",
                "serviceId": "s1"
            },
            {
                "code": "SUCCESS",
                "serviceId": "s2"
            }
        ],
        "size": 200
    }
},
{
    "clientId": "test-client",
    "event": {
        "history": [
            {
                "code": "SUCCESS",
                "serviceId": "s1"
            }
        ],
        "size": 200
    }
}

The output document I'm looking to produce looks like this:

{
    "clientId": "test-client",
    "totalSize": 600,
    "totalVolume": 3,
    "serviceSummary": [
        {
            "serviceId": "s1",
            "serviceTotalSize": 200,
            "serviceTotalVolume": 1
        },
        {
            "serviceId": "s2",
            "serviceTotalSize": 400,
            "serviceTotalVolume": 2
        }
    ]
}

So the query needs to

aggregate all results for the clientId calculating totalSize and totalVolume
look at the content of the history array, and find the serviceId with a code of "SUCCESS"
provide a total size and volume for the serivceId where the event was successful

So far I have a query like this:

select 
    d.clientId, 
    count(*) totalVolume, 
    sum(d.event.size) totalSize ,
    ARRAY_AGG(DISTINCT h.serviceId) serviceSummary
from demo d
unnest d.event.history h
where h.code = 'SUCCESS'
group by d.clientId;

which produces part of the result I want, but not the full serviceSummary

thanks for any help.

vsr vsr · Accepted Answer · 2019-04-18T13:26:04

SQL standards doesn't allow nested aggregates you need intervening subquery with multi level aggregates.

SELECT d1.clientId,
       SUM(d1.serviceTotalVolume) AS totalVolume,
       SUM(d1.serviceTotalSize) AS totalSize,
       ARRAY_AGG({d1.serviceId, d1.serviceTotalVolume, d1.serviceTotalSize}) AS serviceSummary
FROM ( SELECT
             d.clientId,
             h.serviceId,
             COUNT(1) AS serviceTotalVolume,
             SUM(d.event.size) AS serviceTotalSize
       FROM demo AS d
       UNNEST d.event.history AS h
       WHERE h.code = 'SUCCESS'
       GROUP BY d.clientId, h.serviceId) AS d1
GROUP BY d1.clientId;

Couchbase N1QL query aggregation

1 Answers